writing-sql

name

writing-sql

description

Trains Claude to write SQL at Staff+ DBA level by targeting the non-obvious patterns Claude's defaults miss. Covers multi-column statistics for correlated predicates, operator class selection for index usage, aggregate FILTER patterns, keyset pagination, and the query anti-patterns that silently destroy production performance. Use when writing complex SQL, reviewing a query for correctness or performance, adding indexes, or optimizing a slow query.

Writing SQL

Core principle: Every query you write has an execution plan. Write SQL with that plan in mind, not just the result.

This skill covers only what Claude's defaults miss. Topics already handled by managing-databases/postgres-querying.md (LATERAL, CTE materialization, DISTINCT ON, EXPLAIN red flags, ROWS vs RANGE) are not repeated here.

Topic Navigation

Task	Load
Planner picking bad plans on multi-column WHERE	extended-statistics.md
LIKE search not using index, text pattern matching	index-internals.md
Composite index column ordering, INCLUDE clause	index-internals.md
Type mismatch between param and column silently bypassing index	index-internals.md
Conditional aggregation, pivot queries, subtotals	aggregation-patterns.md
GROUPING SETS, ROLLUP, CUBE	aggregation-patterns.md
NULL edge cases in aggregates and window functions	aggregation-patterns.md
Paginating large result sets	query-anti-patterns.md
NOT IN returning zero rows unexpectedly	query-anti-patterns.md
DISTINCT added to fix duplicate rows	query-anti-patterns.md
Correlated subqueries in SELECT list	query-anti-patterns.md
Functions in WHERE bypassing indexes	query-anti-patterns.md

Iron Rules

These apply to every query, no exceptions:

• Never paginate with OFFSET on large tables. Use keyset pagination.
• Never use NOT IN when the subquery column is nullable. Use NOT EXISTS.
• Always match parameter types to column types exactly. Implicit casts bypass indexes.
• Replace CASE WHEN inside aggregates with FILTER. It's cleaner and semantically correct.
• When a multi-column WHERE produces bad estimates, add extended statistics before adding hints or rewrites.