鱼皮AI导航
鱼皮AI导航
  • 主页
  • 社区
  • AI工具
  • AI知识库
  • AI提示词
  • MCP
  • Skills
  • AI学习资源
  • AI资讯
  • GitHub开源
登录/注册

postgres_syntax

深入解析 PostgreSQL 官方语法定义,精准掌握各类 DDL 语句(如 CREATE TABLE、TRIGGER、INDEX 等)的结构组成、可选元素、关键字顺序及嵌套规则,支撑 pgschema 在多版本 PostgreSQL(14–18)环境下生成严格合规的 SQL DDL。

数据库
访问 GitHub
文档Skills.MD
name
PostgreSQL Syntax Reference
description
Consult PostgreSQL's parser and grammar (gram.y) to understand SQL syntax, DDL statement structure, and parsing rules when implementing pgschema features

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:

  • 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
  • Debugging how PostgreSQL interprets specific DDL constructs

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 Typegram.y Sectionparse_utilcmd.c Function
CREATE TABLECreateStmttransformCreateStmt()
ALTER TABLEAlterTableStmttransformAlterTableStmt()
CREATE INDEXIndexStmttransformIndexStmt()
CREATE TRIGGERCreateTrigStmttransformCreateTrigStmt()
CREATE FUNCTIONCreateFunctionStmttransformCreateFunctionStmt()
CREATE PROCEDURECreateFunctionStmt(procedures are functions)
CREATE VIEWViewStmttransformViewStmt()
CREATE MATERIALIZED VIEWCreateMatViewStmt-
CREATE SEQUENCECreateSeqStmttransformCreateSeqStmt()
CREATE TYPECreateEnumStmt, CreateDomainStmt, CompositeTypeStmt-
CREATE POLICYCreatePolicyStmttransformCreatePolicyStmt()
COMMENT ONCommentStmt-

2. Locate the Grammar Rule in gram.y

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

Example - Finding CREATE TRIGGER syntax:

▼
bash
复制代码
# 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:

▼
yacc
复制代码
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:

▼
yacc
复制代码
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:

▼
c
复制代码
// 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 DDL generation (internal/diff/*.go):

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

Note: pgschema uses an inspector-only approach - both desired and current states come from database inspection rather than SQL parsing. Understanding gram.y helps ensure generated DDL is syntactically correct.

Key Grammar Concepts

Optional Elements

Grammar rules prefixed with opt_ are optional:

▼
yacc
复制代码
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:

▼
yacc
复制代码
columnList: columnElem { $$ = list_make1($1); } | columnList ',' columnElem { $$ = lappend($1, $3); }

Alternatives

Use | to show different syntax options:

▼
yacc
复制代码
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:

▼
yacc
复制代码
%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:

▼
yacc
复制代码
CreateSomethingStmt: CREATE opt_or_replace SOMETHING name definition_elements

ALTER Statement Pattern

▼
yacc
复制代码
AlterSomethingStmt: ALTER SOMETHING name alter_action | ALTER SOMETHING IF_P EXISTS name alter_action

DROP Statement Pattern

▼
yacc
复制代码
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

▼
yacc
复制代码
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

▼
yacc
复制代码
TriggerWhen: WHEN '(' a_expr ')' { $$ = $3; } | /* EMPTY */ { $$ = NULL; }

Index Elements

▼
yacc
复制代码
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

▼
yacc
复制代码
ConstraintAttributeSpec: ON DELETE key_action | ON UPDATE key_action | DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE

Keywords and Reserved Words

Check keywords.c for keyword classification:

Reserved keywords: Cannot be used as identifiers without quoting

  • SELECT, FROM, WHERE, CREATE, TABLE, etc.

Type function name keywords: Can be used as function or type names

  • CHAR, CHARACTER, VARCHAR, etc.

Unreserved keywords: Can be used as identifiers

  • ABORT, ABSOLUTE, ACCESS, ACTION, etc.

Impact on pgschema: When generating DDL, quote identifiers that match reserved keywords.

Examples

Example 1: Understanding CREATE TABLE LIKE

In gram.y:

▼
yacc
复制代码
TableLikeClause: LIKE qualified_name TableLikeOptionList

TableLikeOptionList:

▼
yacc
复制代码
TableLikeOptionList: TableLikeOptionList INCLUDING TableLikeOption | TableLikeOptionList EXCLUDING TableLikeOption | /* EMPTY */

TableLikeOption:

▼
yacc
复制代码
TableLikeOption: COMMENTS | CONSTRAINTS | DEFAULTS | IDENTITY_P | GENERATED | INDEXES | STATISTICS | STORAGE | ALL

This tells us:

  • LIKE table_name is the basic syntax
  • Can include/exclude specific features: INCLUDING ALL, EXCLUDING INDEXES, etc.
  • Multiple options can be combined

pgschema usage: When generating DDL for tables with LIKE clauses, follow the gram.y syntax exactly to ensure valid output.

Example 2: Understanding Constraint Triggers

In gram.y:

▼
yacc
复制代码
ConstraintAttributeSpec: DEFERRABLE { $$ = CAS_DEFERRABLE; } | NOT DEFERRABLE { $$ = CAS_NOT_DEFERRABLE; } | INITIALLY DEFERRED { $$ = CAS_INITIALLY_DEFERRED; } | INITIALLY IMMEDIATE { $$ = CAS_INITIALLY_IMMEDIATE; }

For constraint triggers:

▼
yacc
复制代码
CreateTrigStmt: CREATE opt_or_replace CONSTRAINT TRIGGER name ...

This tells us:

  • Constraint triggers use CREATE CONSTRAINT TRIGGER
  • Can be DEFERRABLE or NOT DEFERRABLE
  • Can be INITIALLY DEFERRED or INITIALLY IMMEDIATE

pgschema DDL generation (internal/diff/trigger.go):

▼
go
复制代码
func generateCreateTrigger(trigger *ir.Trigger) string { var sql strings.Builder sql.WriteString("CREATE ") if trigger.IsConstraint { sql.WriteString("CONSTRAINT ") } sql.WriteString("TRIGGER ") sql.WriteString(quoteIdentifier(trigger.Name)) // ... if trigger.Deferrable { sql.WriteString(" DEFERRABLE") } if trigger.InitiallyDeferred { sql.WriteString(" INITIALLY DEFERRED") } return sql.String() }

Example 3: Understanding Expression Indexes

In gram.y:

▼
yacc
复制代码
index_elem: ColId opt_collate opt_class opt_asc_desc opt_nulls_order { $$ = makeIndexElem($1, NULL, NULL, $2, $3, $4, $5, NULL); } | func_expr_windowless opt_collate opt_class opt_asc_desc opt_nulls_order { $$ = makeIndexElem(NULL, $1, NULL, $2, $3, $4, $5, NULL); } | '(' a_expr ')' opt_collate opt_class opt_asc_desc opt_nulls_order { $$ = makeIndexElem(NULL, NULL, $2, $4, $5, $6, $7, NULL); }

This tells us:

  • Index elements can be:
    1. Column names: CREATE INDEX idx ON table (column)
    2. Function calls: CREATE INDEX idx ON table (lower(column))
    3. Arbitrary expressions: CREATE INDEX idx ON table ((column + 1))
  • Note the extra parentheses for arbitrary expressions: (( ... ))

pgschema parsing consideration:

▼
go
复制代码
// When parsing index definitions, handle all three forms: // 1. Simple column reference // 2. Function expression // 3. Arbitrary expression (needs extra parens in DDL)

Example 4: Understanding GENERATED Columns

In gram.y:

▼
yacc
复制代码
GeneratedConstraintElem: GENERATED generated_when AS '(' a_expr ')' STORED { Constraint *n = makeNode(Constraint); n->contype = CONSTR_GENERATED; n->generated_when = $2; n->raw_expr = $5; n->cooked_expr = NULL; n->location = @1; $$ = (Node *)n; } generated_when: ALWAYS { $$ = ATTRIBUTE_IDENTITY_ALWAYS; } | BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; }

This tells us:

  • Generated columns: GENERATED ALWAYS AS (expression) STORED
  • Identity columns: GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY
  • The expression must be in parentheses
  • Must include STORED keyword for computed columns

Debugging Tips

1. Test Grammar Interactively

Clone postgres and build the parser:

▼
bash
复制代码
git clone https://github.com/postgres/postgres.git cd postgres ./configure make -C src/backend/parser

2. Compare with PostgreSQL Behavior

Test actual PostgreSQL behavior:

▼
bash
复制代码
psql -c "CREATE TRIGGER ..." # If PostgreSQL accepts it, the syntax is valid # Use \d+ to see how PostgreSQL formats it

3. Check gram.y Comments

gram.y contains helpful comments explaining syntax choices and historical notes.

4. Search for Examples in Tests

PostgreSQL's test suite has extensive SQL examples:

▼
bash
复制代码
# In postgres repo find src/test/regress/sql -name "*.sql" -exec grep -l "CREATE TRIGGER" {} \;

Version Differences

PostgreSQL syntax evolves across versions:

  • PostgreSQL 14: Added COMPRESSION clause for tables
  • PostgreSQL 15: Added MERGE statement, UNIQUE NULLS NOT DISTINCT
  • PostgreSQL 16: Added SQL/JSON functions
  • PostgreSQL 17: Added MERGE enhancements, incremental view maintenance

For pgschema (supports 14-18):

  • Check gram.y history to see when features were added
  • Add version detection in parser if needed
  • Test across all supported versions

Verification Checklist

After consulting gram.y and implementing in pgschema:

  • Grammar rule fully understood from gram.y
  • All syntax alternatives identified
  • Optional elements properly handled
  • Keywords and quoting rules followed
  • DDL generation produces valid PostgreSQL syntax
  • Test case added in testdata/diff/
  • Tested against PostgreSQL via integration test
  • Works across PostgreSQL versions 14-18

Quick Reference

Finding syntax in gram.y:

▼
bash
复制代码
# Search for statement type grep -n "CreateTrigStmt:" src/backend/parser/gram.y # Find keyword definitions grep -n "^TRIGGER" src/backend/parser/gram.y # Understand an option grep -A 10 "TriggerWhen:" src/backend/parser/gram.y

Understanding precedence:

▼
bash
复制代码
# Look at top of gram.y head -100 src/backend/parser/gram.y | grep -A 50 "%left\|%right\|%nonassoc"

Find utility command handling:

▼
bash
复制代码
grep -n "transformCreateTrigStmt" src/backend/parser/parse_utilcmd.c
数据库

快捷安装

复制到终端去安装 Skill

npxskillsaddpgschema/pgschema--skill"postgres_syntax"
站长:程序员鱼皮 老鱼简历 商务合作 增值电信业务经营许可证
2026 鱼皮AI导航 | 沪ICP备19026706号-6
站长:程序员鱼皮 老鱼简历 商务合作 增值电信业务经营许可证
2026 鱼皮AI导航 | 沪ICP备19026706号-6
🔥热门工具
朱雀AI检测朱雀AI检测
朱雀AI检测助手朱雀AI检测助手
LovartLovart
老鱼简历老鱼简历
WritePass官网WritePass官网
面多多面多多
CursorCursor
星月写作星月写作
朱雀AI检测助手朱雀AI检测助手
零感AI零感AI
Skills 推荐
relational-database-mcp-cloudbase
面向 CloudBase 关系型数据库的自动化管理,支持安全执行只读查询、写入与结构变更操作,并可检视及调整表级安全规则,确保在无应用上下文的代理环境中精准控制数据访问与修改权限。
database-index-advisor
通过分析数据库查询负载和现有索引配置,自动识别缺失的高效索引以加速慢查询,同时发现长期未被使用的冗余索引以便清理,从而在提升读取性能的同时节省存储空间并优化写入效率。
detecting-sql-injection-vulnerabilities
自动识别和分析应用程序代码及数据库查询中的 SQL 注入风险,覆盖输入点检测、查询构造审查、参数化验证与安全模式推荐,生成结构化安全报告并提供修复建议。
database
规范 NestJS 应用的数据访问层设计,涵盖 ORM 选型、连接池与分片等可扩展架构,强制使用仓库模式隔离数据库逻辑,通过异步配置加载敏感信息,禁用生产环境自动同步,依赖迁移脚本实现零停机演进,并统一要求分页、索引声明与事务保护。
prisma-schema-helper
提供 Prisma 数据模型定义的自动化辅助能力,涵盖模式校验、关系配置、字段类型推导及迁移脚本生成,支持 Node.js 等后端技术栈的数据库层快速搭建与维护。