PostgreSQL code authoring
Inspect the target PostgreSQL version, schema, migration tool, existing SQL, and access pattern before drafting. Preserve repository conventions; do not invent table, column, index, or constraint names.
Construction contract
- Parameterize runtime values; never concatenate user or variable data into SQL text.
- Write the smallest result shape needed: explicit columns, deterministic ordering where order matters, and pagination suited to the access pattern. Use keyset pagination for deep traversal when the existing contract permits it.
- Model integrity in DDL with the appropriate primary key,
NOT NULL, UNIQUE, CHECK, and foreign-key constraints. Match column types, defaults, and nullability to the actual domain contract.
- For a migration, prefer compatible staged changes. State lock/transaction constraints, data backfill plan, rollback path, and verification for destructive, large-table, type, or constraint changes.
- Propose indexes from an observed query predicate/join/order pattern and verify performance with
EXPLAIN or EXPLAIN (ANALYZE, BUFFERS) where safe. Never claim a query is faster without evidence.
- Author only; do not execute data or schema changes without separate authorization.
Read query construction for query work and migration construction for DDL or migrations. The installed postgres-query-review, postgres-schema-design, and postgres-risk-check dependencies provide the shared review rationale; use them as construction constraints, not as a request to write a review.
Deliverable
Return the SQL or migration, stated assumptions, verification commands/tests, and rollback/operational notes when applicable.
1---2name: postgres-code-authoring3description: Write or modify PostgreSQL queries, schemas, and migrations with parameter safety, data integrity, and operational verification. Use when a feature, bug fix, or refactor in a repository detected as PostgreSQL requires SQL, DDL, indexes, or migrations, even when the user does not name the database; do not use for review-only requests.4---56# PostgreSQL code authoring78Inspect the target PostgreSQL version, schema, migration tool, existing SQL, and access pattern before drafting. Preserve repository conventions; do not invent table, column, index, or constraint names.910## Construction contract1112- Parameterize runtime values; never concatenate user or variable data into SQL text.13- Write the smallest result shape needed: explicit columns, deterministic ordering where order matters, and pagination suited to the access pattern. Use keyset pagination for deep traversal when the existing contract permits it.14- Model integrity in DDL with the appropriate primary key, `NOT NULL`, `UNIQUE`, `CHECK`, and foreign-key constraints. Match column types, defaults, and nullability to the actual domain contract.15- For a migration, prefer compatible staged changes. State lock/transaction constraints, data backfill plan, rollback path, and verification for destructive, large-table, type, or constraint changes.16- Propose indexes from an observed query predicate/join/order pattern and verify performance with `EXPLAIN` or `EXPLAIN (ANALYZE, BUFFERS)` where safe. Never claim a query is faster without evidence.17- Author only; do not execute data or schema changes without separate authorization.1819Read [query construction](reference/kb/query-construction.md) for query work and [migration construction](reference/kb/migration-construction.md) for DDL or migrations. The installed `postgres-query-review`, `postgres-schema-design`, and `postgres-risk-check` dependencies provide the shared review rationale; use them as construction constraints, not as a request to write a review.2021## Deliverable2223Return the SQL or migration, stated assumptions, verification commands/tests, and rollback/operational notes when applicable.
ch4570