PostgreSQL Syntax Reference

PostgreSQL Syntax Reference

Use this skill when you need to understand PostgreSQL's SQL syntax, DDL statement structure, or how PostgreSQL parses specific SQL constructs. This is essential for correctly parsing SQL files and generating valid DDL in pgschema.

When to Use This Skill

Invoke this skill when:

• Implementing new SQL statement parsing in ir/parser.go
• Debugging SQL parsing issues with pg_query_go
• Understanding complex SQL syntax (CREATE TABLE, CREATE TRIGGER, etc.)
• Generating DDL statements in internal/diff/*.go
• Validating SQL statement structure
• Understanding precedence and grammar rules
• Learning about PostgreSQL-specific syntax extensions

Source Code Locations

Main parser directory: https://github.com/postgres/postgres/blob/master/src/backend/parser/

Key files to reference:

Grammar and Lexer

• gram.y - Main grammar file - Yacc/Bison grammar defining PostgreSQL SQL syntax
• scan.l - Lexical scanner (Flex/Lex) - tokenization rules
• keywords.c - Reserved and non-reserved keywords

Parser Implementation

• parse_clause.c - Parsing of clauses (WHERE, GROUP BY, ORDER BY, etc.)
• parse_expr.c - Expression parsing (operators, function calls, etc.)
• parse_type.c - Type name parsing and resolution
• parse_relation.c - Table and relation parsing
• parse_target.c - Target list parsing (SELECT list, etc.)
• parse_func.c - Function call parsing
• parse_utilcmd.c - Utility commands (DDL statements like CREATE, ALTER, DROP)

Analysis and Transformation

• analyze.c - Post-parse analysis
• parse_node.c - Parse node creation utilities

Step-by-Step Workflow

1. Identify the SQL Statement Type

Determine what kind of SQL you're working with:

| Statement Type | gram.y Section | parse_utilcmd.c Function | |----------------|----------------|-------------------------| | CREATE TABLE | CreateStmt | transformCreateStmt() | | ALTER TABLE | AlterTableStmt | transformAlterTableStmt() | | CREATE INDEX | IndexStmt | transformIndexStmt() | | CREATE TRIGGER | CreateTrigStmt | transformCreateTrigStmt() | | CREATE FUNCTION | CreateFunctionStmt | transformCreateFunctionStmt() | | CREATE PROCEDURE | CreateFunctionStmt | (procedures are functions) | | CREATE VIEW | ViewStmt | transformViewStmt() | | CREATE MATERIALIZED VIEW | CreateMatViewStmt | - | | CREATE SEQUENCE | CreateSeqStmt | transformCreateSeqStmt() | | CREATE TYPE | CreateEnumStmt, CreateDomainStmt, CompositeTypeStmt | - | | CREATE POLICY | CreatePolicyStmt | transformCreatePolicyStmt() | | COMMENT ON | CommentStmt | - |

2. Locate the Grammar Rule in gram.y

Search gram.y for the statement's production rule:

Example - Finding CREATE TRIGGER syntax:

# In the postgres repository
grep -n "CreateTrigStmt:" src/backend/parser/gram.y

What to look for:

• The production rule name (e.g., CreateTrigStmt:)
• Alternative syntaxes (multiple | branches)
• Optional elements (opt_* rules)
• List constructs (*_list rules)
• Terminal tokens (keywords, literals)

3. Understand the Grammar Structure

gram.y uses Yacc/Bison syntax:

CreateTrigStmt:
    CREATE opt_or_replace TRIGGER name TriggerActionTime TriggerEvents ON
    qualified_name TriggerReferencing TriggerForSpec TriggerWhen
    EXECUTE FUNCTION_or_PROCEDURE func_name '(' TriggerFuncArgs ')'
    {
        CreateTrigStmt *n = makeNode(CreateTrigStmt);
        n->trigname = $4;
        n->relation = $8;
        n->funcname = $13;
        /* ... */
        $$ = (Node *)n;
    }

Key elements:

• Terminals (uppercase): Keywords like CREATE, TRIGGER, ON
• Non-terminals (lowercase): Other grammar rules like name, qualified_name
• Actions ({ ... }): C code that builds the parse tree
• Alternatives (|): Different ways to write the same statement
• Optional elements: Rules prefixed with opt_

4. Trace Through Related Rules

Follow the grammar rules to understand the complete syntax:

Example - Understanding trigger events:

TriggerEvents:
    TriggerOneEvent
    | TriggerEvents OR TriggerOneEvent

TriggerOneEvent:
    INSERT
    | DELETE
    | UPDATE
    | UPDATE OF columnList
    | TRUNCATE

This shows:

• Triggers can have multiple events combined with OR
• UPDATE can optionally specify columns with OF columnList

5. Cross-Reference with parse_utilcmd.c

After understanding the grammar, check how PostgreSQL transforms the parsed statement:

Example - How CREATE TRIGGER is processed:

// In parse_utilcmd.c
static void
transformCreateTrigStmt(CreateTrigStmt *stmt, const char *queryString)
{
    // Validation and transformation logic
    // - Check trigger name conflicts
    // - Validate trigger function exists
    // - Process WHEN condition
    // - Handle constraint triggers
}

6. Apply to pgschema

Use this understanding in pgschema:

For parsing (ir/parser.go):

• pgschema uses pg_query_go which wraps libpg_query (based on PostgreSQL's parser)
• Parse tree structure matches gram.y production rules
• Access parsed nodes to extract information

For DDL generation (internal/diff/*.go):

• Follow gram.y syntax exactly
• Use proper keyword ordering
• Include all required elements
• Quote identifiers correctly

Key Grammar Concepts

Optional Elements

Grammar rules prefixed with opt_ are optional:

opt_or_replace:
    OR REPLACE     { $$ = true; }
    | /* EMPTY */  { $$ = false; }

This means CREATE OR REPLACE TRIGGER ... and CREATE TRIGGER ... are both valid.

Lists

Lists are typically defined recursively:

columnList:
    columnElem                     { $$ = list_make1($1); }
    | columnList ',' columnElem    { $$ = lappend($1, $3); }

Alternatives

Use | to show different syntax options:

TriggerActionTime:
    BEFORE     { $$ = TRIGGER_TYPE_BEFORE; }
    | AFTER    { $$ = TRIGGER_TYPE_AFTER; }
    | INSTEAD OF { $$ = TRIGGER_TYPE_INSTEAD; }

Precedence

Operator precedence is defined at the top of gram.y:

%left OR
%left AND
%right NOT
%nonassoc IS ISNULL NOTNULL
%nonassoc '<' '>' '=' LESS_EQUALS GREATER_EQUALS NOT_EQUALS

Common Grammar Patterns

CREATE Statement Pattern

Most CREATE statements follow this pattern:

CreateSomethingStmt:
    CREATE opt_or_replace SOMETHING name definition_elements

ALTER Statement Pattern

AlterSomethingStmt:
    ALTER SOMETHING name alter_action
    | ALTER SOMETHING IF_P EXISTS name alter_action

DROP Statement Pattern

DropSomethingStmt:
    DROP SOMETHING name opt_drop_behavior
    | DROP SOMETHING IF_P EXISTS name opt_drop_behavior

Important SQL Constructs for pgschema

Table Columns with Constraints

columnDef:
    ColId Typename opt_column_storage ColQualList
    | ColId Typename opt_column_storage GeneratedConstraintElem
    | ColId Typename opt_column_storage GENERATED generated_when AS IDENTITY_P OptParenthesizedSeqOptList

This covers:

• Regular columns: column_name type
• Generated columns: column_name type GENERATED ALWAYS AS (expr) STORED
• Identity columns: column_name type GENERATED ALWAYS AS IDENTITY

Trigger WHEN Clause

TriggerWhen:
    WHEN '(' a_expr ')'    { $$ = $3; }
    | /* EMPTY */          { $$ = NULL; }

Index Elements

index_elem:
    ColId opt_collate opt_class opt_asc_desc opt_nulls_order
    | func_expr_windowless opt_collate opt_class opt_asc_desc opt_nulls_order
    | '(' a_expr ')' opt_collate opt_class opt_asc_desc opt_nulls_order

This shows indexes can be on:

• Simple columns
• Function expressions (functional indexes)
• Arbitrary expressions (expression indexes)

Foreign Key Options

ConstraintAttributeSpec:
    ON DELETE key_action
    | ON UPDATE key_action
    | DEFERRABLE
    | NOT DEFERRABLE
    | INITIALLY DEFERRED
    | INITIALL