pgplex 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 syntaxinternal/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 optionsopt_*: 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:
- Column:
CREATE INDEX idx ON t (col) - Function:
CREATE INDEX idx ON t (lower(col)) - 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:
COMPRESSIONclause for tables - PG 15:
UNIQUE NULLS NOT DISTINCT - PG 16: SQL/JSON functions
- PG 17:
MERGEenhancements
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