Common SQL Errors and How to Fix Them: An Error-Message Index

Grouping Errors

"column X must appear in the GROUP BY clause or be used in an aggregate function"

Cause: SELECT lists a column that is neither grouped nor aggregated. Fix: decide its role — a dimension goes in GROUP BY, a measure gets wrapped in an aggregate. The senior move: GROUP BY 1 or grouping by the primary key, which PostgreSQL treats as functionally dependent. Related reading: conditional aggregation solves many "I need two aggregates from one table" cases cleanly.

"aggregate functions are not allowed in WHERE"

Cause: filtering on an aggregate before grouping happens. Fix: use HAVING (after grouping) or a subquery/CTE that computes the aggregate first, then filter outside. HAVING is clearer for group-level filters — and engines can often push it down anyway.

NULL and Type Errors

"invalid input syntax for type integer: 'abc'" (PostgreSQL)

Cause: a string that is not a number reached a numeric comparison — usually a parameter binding or a JOIN between a text column and an integer. Fix: find the offending row with a guard query: WHERE col !~ '^[0-9]+$', then fix the data or the cast. MySQL's equivalent is a silent 0 comparison, which is worse — see the NULL cheat sheet for the coercion differences.

"division by zero" (PostgreSQL) / NULL result (MySQL)

Fix that keeps rows: COALESCE(numerator / NULLIF(denominator, 0), 0) — NULLIF converts the zero into NULL (making the division return NULL), and COALESCE reports it as 0. Same pattern works in every dialect.

"column reference is ambiguous"

Cause: two joined tables share a column name and the query does not qualify it. Fix: prefix with the table alias (o.created_at). Systemic fix: alias every table and qualify every column in multi-table queries — the habit prevents the bug class entirely.

Constraint Errors

"duplicate key value violates unique constraint"

Cause: inserting a value that already exists. Fixes, in order of preference: (1) if the intent is "insert or update", use the dialect upsert — ON CONFLICT ... DO UPDATE / ON DUPLICATE KEY UPDATE; (2) if the intent is "ignore if present", ON CONFLICT DO NOTHING / INSERT IGNORE; (3) if the row should not be there at all, your dedup logic upstream is the bug. Finding which rows collide: duplicate-finding guide.

"violates foreign key constraint"

Cause: a child row references a parent that does not exist — or you are deleting a parent that still has children. Fixes: insert parents before children in batch jobs; choose ON DELETE behavior deliberately (RESTRICT to block, CASCADE to clean up, SET NULL to detach). Audit query: SELECT o.* FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE c.id IS NULL;

"null value in column violates not-null constraint"

Cause: an INSERT/UPDATE omitted a required column, or a JOIN produced NULLs you did not expect. Fix: supply the value or a default — and when the source is a LEFT JOIN, check that the row SHOULD exist before defaulting it silently.

Connection and Concurrency Errors

"too many connections"

Cause: connection pool exhaustion — often leaked connections from code paths that do not close on error, or a burst of serverless invocations each holding one. Fixes: use a pooler (pgbouncer, ProxySQL), cap pool size below the server limit, and ensure every code path closes (use context managers / defer close). Diagnosis: SELECT count(*), state FROM pg_stat_activity GROUP BY state;

"deadlock detected"

Cause: two transactions lock the same rows in opposite order (A locks row 1 then 2; B locks 2 then 1). Fixes: (1) make lock order consistent — always update tables/rows in the same order; (2) keep transactions short — do not hold one open across network calls; (3) retry on deadlock (the engine kills one transaction; it is designed to be retried). The deadlock report names both queries — read it, it tells you the exact lock order conflict.

"canceling statement due to statement timeout"

Cause: the query exceeded the configured limit — usually a missing index or an accidental cross join. Fix: run the plan and work the optimization checklist; if a legitimate long query (nightly rollup) needs more time, raise the timeout for that session only.

Dialect-Specific Errors

"unknown column in field list" (MySQL)

Cause: a column name typo, or a column that exists in the schema but not yet applied to this environment. Fix: verify with SHOW COLUMNS FROM table; and check migrations ran. Note: backticks around a misspelled name produce this error rather than a syntax error, which is friendlier.

"FUNCTION DATE_TRUNC does not exist" (MySQL)

Cause: PostgreSQL idioms applied to MySQL. MySQL has no DATE_TRUNC. Fix: DATE_FORMAT(d, '%Y-%m-01') for month truncation; full mapping in the date functions cheat sheet.

"only_full_group_by" error (MySQL 5.7+)

Cause: MySQL adopted PostgreSQL's stricter grouping rule; legacy queries that relied on "pick any row" now fail. Fix: do the same as the PostgreSQL fix — group or aggregate every listed column. Resist disabling ONLY_FULL_GROUP_BY; the old behavior returned nondeterministic values.

"operator does not exist: character varying = integer" (PostgreSQL)

Cause: comparing a text column with a number — PostgreSQL refuses implicit casts that would lose information. Fix: cast explicitly (col::int = 42) after confirming the column only holds numeric values, or fix the schema so the type matches what it stores.

Try It Yourself

Paste an error message plus your query into the SQL generator — describe what you meant and it returns the corrected query with the fix explained, covering the whole family rather than the single instance.