postgres_syntax

🗄️ 数据库

访问 GitHub

深入解析 PostgreSQL 官方语法定义，精准掌握各类 DDL 语句（如 CREATE TABLE、TRIGGER、INDEX 等）的结构组成、可选元素、关键字顺序及嵌套规则，支撑 pgschem…

简介

核心能力速览

✓所属分类：🗄️ 数据库

✓通过 Agent Skills 协议，将「能力」封装为可复用、可安装的 AI 组件

✓支持按需加载领域知识与工具，让通用模型转变为特定任务的专家

适用场景

适用于「🗄️ 数据库」相关场景，可作为可复用的 AI 能力组件，接入支持 Agent Skills 的 AI 客户端（如 Claude、Cursor、Cline 等），按需调用以扩展模型能力。

Skills.MD

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

快捷安装

在终端执行以下命令，即可将本 Skill 安装到本地 AI 客户端：

npx skills add pgschema/pgschema