Skip to main content
skill CX Codex

postgres_syntax — Claude Code skill: SQL syntax, install

postgres_syntax is a skill that checks SQL and DDL against PostgreSQL grammar. It helps generate schemas, validate statements, and resolve syntax questions.

Apache-2.0 1 installs ★ 1,089 updated 2026-10-28

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add pgplex/pgschema --skill postgres_syntax -a claude-code
Open on GitHub

What postgres_syntax does

This skill uses PostgreSQL’s grammar to explain how SQL and DDL statements are formed. It covers tables, indexes, triggers, functions, views, sequences, types, and policies. It also helps with generated and identity columns, expression indexes, constraint triggers, and operator precedence.

Its preferred local references are internal/gram.y and internal/scan.l. You can search grammar rules with grep, such as CreateTrigStmt or TriggerWhen. When producing DDL, the skill directs the agent to keep keyword order correct, quote identifiers through ir/quote.go, and test the resulting statements with integration tests against a real PostgreSQL instance.

PostgreSQL 14–18 have syntax differences called out here, including COMPRESSION, UNIQUE NULLS NOT DISTINCT, SQL/JSON functions, and changes to MERGE. A real PostgreSQL instance is needed to run the generated DDL checks.

What it helps with

  • validate generated PostgreSQL DDL
  • locate the grammar rule for a SQL statement
  • explain the structure of CREATE TRIGGER
  • distinguish column, function, and expression index forms
  • account for PostgreSQL 14–18 syntax differences

Example prompts for the agent

  • Check this DDL against PostgreSQL grammar and point out any syntax problems.
  • Use internal/gram.y to explain how CREATE TRIGGER handles a WHEN condition.
  • Review whether the index on lower(email) uses the correct expression syntax.

How to install postgres_syntax

1Skills CLI (Vercel)

npx skills add pgplex/pgschema --skill postgres_syntax -a claude-code

2Manually: put the files in place

~/.claude/skills/postgres_syntax/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/postgres_syntax/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about postgres_syntax

How do I install postgres_syntax in Claude Code?

Run npx skills add pgplex/pgschema --skill postgres_syntax -a claude-code, then check ~/.claude/skills/postgres_syntax/SKILL.md.

What does postgres_syntax do?

It consults PostgreSQL grammar to explain SQL and DDL structure. The skill covers statements such as tables, indexes, triggers, functions, and policies.

Which files does postgres_syntax use?

The preferred local references are internal/gram.y for SQL grammar and internal/scan.l for tokenization. Generated DDL should also be checked with integration tests against a real PostgreSQL instance.

Source text

SKILL.md · Apache-2.0
---
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**:
```bash
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:
```yacc
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
```yacc
TriggerWhen:
    WHEN '(' a_expr ')'
    | /* EMPTY */
```

### Constraint Triggers
```yacc
CREATE opt_or_replace CONSTRAINT TRIGGER name ...
    -- Can be DEFERRABLE / NOT DEFERRABLE
    -- Can be INITIALLY DEFERRED / INITIALLY IMMEDIATE
```

### Table LIKE Clause
```yacc
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

© pgplex · Apache-2.0 · pgplex/pgschema

Similar skills

All skills for Data and databases →

From the same set2

Project sponsors

Companies that keep this analytics open