SQL Ecosystem

Cross-engine SQL guidance focused on where PostgreSQL, MySQL, and SQLite diverge, and where correct-looking SQL fails silently. Skip anything you'd get from a general SQL reference; this file exists for the parts that surprise a competent developer moving between engines.

Engine divergence traps

• SQLite type affinity is advisory, not enforced. A column declared INTEGER still accepts and stores a string: the declared type only selects an affinity, not a constraint. Code that assumes SQLite rejects wrong-typed inserts the way PostgreSQL/MySQL do will silently store garbage.
• Upsert syntax differs and is not interchangeable: PostgreSQL uses INSERT ... ON CONFLICT (col) DO UPDATE SET x = EXCLUDED.x; MySQL uses INSERT ... ON DUPLICATE KEY UPDATE x = VALUES(x). Porting one to the other by search-replace fails to parse rather than misbehaving, but the EXCLUDED/VALUES() reference syntax is the part people forget to translate.
• FULL OUTER JOIN does not exist in MySQL. Rewrite as LEFT JOIN ... UNION SELECT ... RIGHT JOIN ...; there is no direct substitute keyword.
• Foreign-key columns are auto-indexed by MySQL but not by PostgreSQL. A REFERENCES clause in PostgreSQL creates no index: joins and cascading deletes on that column do full scans until you add CREATE INDEX explicitly. This is the single most common missing-index bug when a schema is ported from MySQL to PostgreSQL.
• EXPLAIN ANALYZE is MySQL 8.0.18+ only; earlier MySQL has plain EXPLAIN. SQLite's equivalent is EXPLAIN QUERY PLAN, not EXPLAIN (bare EXPLAIN in SQLite dumps VDBE bytecode, not a query plan: a frequent tool-invocation mistake).

Current engine versions

• PostgreSQL: 18, 17 (LTS), 16, 15, 14 supported; 13 and earlier reached EOL November 2025. PG 18 adds async I/O (concurrent readahead/seqscan) and, notably, flips the default for generated columns from STORED to VIRTUAL when neither keyword is specified: a schema written for PG ≤17 that omitted the keyword changes behavior on upgrade (computed-on-write becomes computed-on-read). PG 17 adds SQL/JSON (JSON_TABLE, JSON_QUERY, JSON_VALUE, JSON_EXISTS). PG 15 adds MERGE (full MATCHED/NOT MATCHED upsert). pg_stat_io (PG 16+) gives per-backend-type I/O stats; incremental sort (PG 13+) exploits existing index order to cut ORDER BY cost: look for "Incremental Sort" in the plan rather than assuming a full sort ran.
• MySQL: 8.4 LTS and 9.x Innovation releases.
• SQLite: 3.48+, type-affinity system (see trap above), single-file database.

Query patterns that hide bugs

• CTE materialization is a silent performance cliff, not just a readability choice. PostgreSQL (12+) inlines a CTE referenced once but materializes it if referenced more than once: the same query can regress hard after an innocuous second reference is added. Force the behavior explicitly instead of relying on the reference-count heuristic:

  WITH expensive_calc AS MATERIALIZED (
    SELECT user_id, SUM(total) AS lifetime_value FROM orders GROUP BY user_id
  )
  SELECT * FROM expensive_calc WHERE lifetime_value > 1000;
  

• LAST_VALUE() silently returns the wrong row without an explicit frame. The default window frame ends at the current row, so LAST_VALUE(total) OVER (PARTITION BY user_id ORDER BY order_date) returns the current row's value, not the partition's actual last value. It needs ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to mean what the name implies.
• ROWS, RANGE, and GROUPS framing are not interchangeable when ORDER BY has duplicate values: ROWS counts physical rows, RANGE counts by value distance (e.g. a 7-day window by timestamp), GROUPS (PG 11+) counts peer groups of tied rows. Picking the wrong one silently double- or under-counts tied rows in a moving aggregate.
• LATERAL lets a subquery in FROM reference preceding FROM items (PostgreSQL, MySQL 8.0.14+): the standard way to do top-N-per-group without a window-function-plus-filter:

  SELECT u.name, t.total FROM users u
  LEFT JOIN LATERAL (
    SELECT total FROM orders WHERE user_id = u.id ORDER BY total DESC LIMIT 3
  ) t ON true;
  

• Composite indexes obey a leftmost-prefix rule: an index on (user_id, status) serves WHERE user_id = ? and WHERE user_id = ? AND status = ?, but not WHERE status = ? alone. Column order in the index, not in the query, decides usability.
• OR across different columns defeats single-index use. WHERE email = ? OR name = ? forces a scan even with indexes on both columns individually; rewrite as a UNION of two single-column-filtered queries to let each half use its own index.

Schema design traps

• Polymorphic association (commentable_type + commentable_id) cannot carry a foreign-key constraint. Referential integrity is enforced only at the application layer: an orphaned or mistyped commentable_id is not caught by the database, ever.
• Monetary values stored as FLOAT/DOUBLE accumulate rounding error (0.1 + 0.2 != 0.3 in IEEE 754). Use DECIMAL/NUMERIC, or store integer minor units (cents).
• GENERATED ... STORED vs VIRTUAL: PostgreSQL supported only STORED through v17 (18+ adds VIRTUAL, see version note above); MySQL and SQLite support both. VIRTUAL recomputes on read and cannot be indexed the same way STORED can: check which one a query actually needs before assuming "generated column" means "persisted and indexable."

Transactions and isolation

• Isolation-level defaults differ by engine and by spec-compliance: PostgreSQL defaults to READ COMMITTED; MySQL defaults to REPEATABLE READ. Standard SQL REPEATABLE READ still permits phantom reads, but PostgreSQL's REPEATABLE READ prevents phantoms anyway (it's closer to snapshot isolation): code tested against Postgres at that isolation level can see new phantom-read failures purely from being pointed at MySQL, with no code change.
• FOR UPDATE SKIP LOCKED is the standard queue-worker pattern (skip rows another worker already locked rather than blocking): SELECT * FROM jobs WHERE status='pending' FOR UPDATE SKIP LOCKED LIMIT 1. FOR UPDATE NOWAIT fails immediately instead of blocking: useful to distinguish real contention from a hang.
• Advisory locks (PostgreSQL) have two different lifetimes: pg_advisory_lock/ pg_advisory_unlock are session-scoped and outlive the transaction unless explicitly released; pg_advisory_xact_lock auto-releases at commit. Using the session variant inside a connection pool leaks locks across borrowed connections if the unlock call is ever skipped on an error path.
• Deadlock avoidance is ordering, not detection: acquire locks on multiple rows in a consistent order (e.g., always lower ID first) across every code path that touches those rows together; a lock_timeout (PG) / innodb_lock_wait_timeout (MySQL) bounds the blast radius when ordering discipline still gets violated somewhere.

Migrations

• ALTER TABLE ... ADD COLUMN ... DEFAULT x is instant in PostgreSQL 11+ (no table rewrite, metadata-only) but a full table rewrite on older PostgreSQL and on MySQL before 8.0's instant DDL support: the same statement is a no-op-cost change on one version and a locks-the-table-for-the-duration change on another.
• CREATE INDEX CONCURRENTLY (PostgreSQL) can fail and leave an invalid index behind rather than rolling back cleanly: it cannot run inside a transaction, so a failure mid-build does not undo. Always check pg_index.indisvalid after a concurrent build and DROP INDEX + retry if it's false; don't assume "the command returned" means "the index is usable."
• Renaming a column with zero downtime is expand-contract, not RENAME COLUMN: add the new column, backfill, deploy code that writes both, deploy code that reads only the new column, then drop the old one. A bare RENAME COLUMN breaks every in-flight deployment still reading the old name.
• Dropping a column safely requires the drain step: stop writing from the application first, let old code paths fully roll off, only then DROP COLUMN: dropping while old code still references the column errors mid-request rather than failing at deploy time.

SQL injection surfaces beyond parameterized values

Parameterized queries close the value-injection path but leave two others open:

• Wildcard injection in LIKE: if user input is interpolated as the pattern, a value of % matches every row even through a parameterized query, because %/_ are pattern metacharacters the parameterization doesn't escape. Escape them in application code before binding, or use an explicit ESCAPE clause: ... LIKE '%' || $1 || '%' ESCAPE ''.
• Identifier injection: parameters bind values, not table/column names: f"SELECT {column_name} FROM {table_name}" is unparameterizable by definition. The only safe pattern is whitelisting identifiers against a known set before interpolating them, plus quoting (quote_ident() in PostgreSQL) as defense in depth.

Tooling

• pg_format --spaces 2 --keyword-case 2 input.sql: PostgreSQL-aware formatter (pgFormatter).
• sqlfluff lint --dialect postgres query.sql / sqlfluff fix --dialect postgres query.sql: multi-dialect linter/autofixer (postgres, mysql, sqlite, bigquery, ...).
• pgTAP: TAP-protocol unit tests written in SQL (SELECT has_table(...), SELECT has_column(...)), runs inside PostgreSQL itself rather than an external test runner.

Style convention (Emacs sql-indent)

Right-align keywords so clause bodies start at a consistent column, and put commas at line start:

SELECT c.customer_id
       , c.customer_name
       , COUNT(o.order_id) AS order_count
  FROM customers AS c
  LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
 WHERE c.status = 'active'
 GROUP BY c.customer_id
        , c.customer_name
HAVING COUNT(o.order_id) > 5
 ORDER BY order_count DESC
 LIMIT 100;

Uppercase keywords, snake_case identifiers, explicit AS on aliases, one column per line.

Related

• context7-usage: fetch current PostgreSQL/MySQL/SQLite docs (library IDs: /websites/postgresql, /websites/dev_mysql_doc_refman_9_4_en, /sqlite/sqlite) instead of relying on training-data recall of version-specific behavior.
• serena-usage: navigate schema definitions and find existing query patterns across a codebase before adding a new one.
• investigation-patterns: for tracing a query-performance regression back to a plan change rather than guessing from the query text.