rileyhilliard/writing-sql

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.