OpenParable
Skip to content
← Back to skills

Postgres Syntax

Development
postgres_syntax

Postgres Syntax is an Agent Skill for development published in the pgplex/pgschema repository on GitHub: a SKILL.md instruction file an AI agent can load to do this task.

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.

View on GitHub ↗
QUICK START

How to use this skill

Bring this guide into your coding agent with a prompt tailored to the tool you use.

  1. Open your project in Codex.
  2. Copy and paste the prompt below.
  3. Review files and risks before approving any action.
Prompt to paste
Inspect the Agent Skill at https://github.com/pgplex/pgschema/blob/HEAD/.claude/skills/postgres_syntax/SKILL.md and all supporting files. Report provenance, permissions, scripts, dependencies, and security risks. Propose an exact installation into .agents/skills/postgres-syntax, but wait for my approval before writing files or running scripts.

Copying this prompt does not install or run the skill. Review third-party files before use.

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

StatementGrammar RuleKey Sub-rules
CREATE TABLECreateStmtcolumnDef, TableConstraint, TableLikeClause
ALTER TABLEAlterTableStmtalter_table_cmd
CREATE INDEXIndexStmtindex_elem (column, function, expression)
CREATE TRIGGERCreateTrigStmtTriggerActionTime, TriggerEvents, TriggerWhen
CREATE FUNCTIONCreateFunctionStmtfunc_args, createfunc_opt_list
CREATE VIEWViewStmtSelectStmt
CREATE SEQUENCECreateSeqStmtOptSeqOptList
CREATE TYPECreateEnumStmt, CompositeTypeStmt, CreateDomainStmt
CREATE POLICYCreatePolicyStmtrow_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