postgres-syntax - SKILL.md Agent Skill

name: postgres_syntax description: Consult PostgreSQL's parser and grammar (gram.y) to understand SQL syntax, DDL statement structure, and parsing rules when implementing pgschema features. Use this skill when generating DDL in internal/diff/*.go, validating SQL syntax, understanding keyword precedence, or learning how PostgreSQL handles specific constructs like triggers, indexes, generated columns, or constraint triggers.

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:

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: 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