postgresql-syntax-reference

PostgreSQL Syntax Reference

Reference PostgreSQL's grammar to understand SQL syntax and generate correct DDL.

Source Files

Local copies (preferred):

• internal/gram.y - Yacc/Bison grammar defining all PostgreSQL SQL syntax
• internal/scan.l - Flex lexer for tokenization

Searching the grammar:

grep -n "CreateTrigStmt:" internal/gram.y     # Find statement rule
grep -A 10 "TriggerWhen:" internal/gram.y     # Understand an option

Statement Types → Grammar Rules

Statement	Grammar Rule	Key Sub-rules
CREATE TABLE	CreateStmt	columnDef, TableConstraint, TableLikeClause
ALTER TABLE	AlterTableStmt	alter_table_cmd
CREATE INDEX	IndexStmt	index_elem (column, function, expression)
CREATE TRIGGER	CreateTrigStmt	TriggerActionTime, TriggerEvents, TriggerWhen
CREATE FUNCTION	CreateFunctionStmt	func_args, createfunc_opt_list
CREATE VIEW	ViewStmt	SelectStmt
CREATE SEQUENCE	CreateSeqStmt	OptSeqOptList
CREATE TYPE	CreateEnumStmt, CompositeTypeStmt, CreateDomainStmt
CREATE POLICY	CreatePolicyStmt	row_security_cmd

Grammar Syntax Guide

gram.y uses Yacc/Bison notation:

• UPPERCASE: Terminal tokens (keywords like CREATE, TRIGGER)
• lowercase: Non-terminal rules (references to other grammar rules)
• |: Alternative syntax options
• opt_*: Optional elements (can be empty)
• *_list: Recursive list constructs

Example:

CreateTrigStmt:
    CREATE opt_or_replace TRIGGER name TriggerActionTime TriggerEvents ON
    qualified_name TriggerReferencing TriggerForSpec TriggerWhen
    EXECUTE FUNCTION_or_PROCEDURE func_name '(' TriggerFuncArgs ')'

Key Constructs for pgschema

Column Definitions

• Regular: column_name type [constraints]
• Generated: column_name type GENERATED ALWAYS AS (expr) STORED
• Identity: column_name type GENERATED {ALWAYS|BY DEFAULT} AS IDENTITY

Index Elements

Three forms — note extra parens for arbitrary expressions:

1. Column: CREATE INDEX idx ON t (col)
2. Function: CREATE INDEX idx ON t (lower(col))
3. Expression: CREATE INDEX idx ON t ((col + 1))

Trigger WHEN Clause

TriggerWhen:
    WHEN '(' a_expr ')'
    | /* EMPTY */

Constraint Triggers

CREATE opt_or_replace CONSTRAINT TRIGGER name ...
    -- Can be DEFERRABLE / NOT DEFERRABLE
    -- Can be INITIALLY DEFERRED / INITIALLY IMMEDIATE

Table LIKE Clause

LIKE qualified_name [INCLUDING|EXCLUDING] {COMMENTS|CONSTRAINTS|DEFAULTS|IDENTITY|GENERATED|INDEXES|STATISTICS|STORAGE|ALL}

Operator Precedence (from gram.y top)

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

Keywords

• Reserved: Cannot be identifiers without quoting (SELECT, TABLE, CREATE)
• Unreserved: Can be used as identifiers (ABORT, ACCESS, ACTION)

When generating DDL, quote identifiers that match reserved keywords.

Version Differences (14-18)

• PG 14: COMPRESSION clause for tables
• PG 15: UNIQUE NULLS NOT DISTINCT
• PG 16: SQL/JSON functions
• PG 17: MERGE enhancements

Check gram.y git history to see when features were added. Add version detection in pgschema if needed.

Applying to pgschema

When generating DDL in internal/diff/*.go:

• Follow gram.y syntax exactly for keyword ordering
• Include all required elements
• Quote identifiers correctly via ir/quote.go
• Test generated DDL against real PostgreSQL via integration tests