Query Doctor

Comparing a number to text silently matches nothing

A numeric column compared to a non-numeric string doesn't always error — on some engines it just quietly returns no rows, no warning, no hint that the comparison itself was the problem.

"Column count doesn't match value count"

The number of values in your INSERT doesn't match the number of columns — either the column list, or a row in a multi-row VALUES list, is short or long by one.

"Multiple primary keys for table are not allowed"

You marked two separate columns PRIMARY KEY on the same table. A table can only have one primary key — if you meant both columns together should be unique, that's a composite key, different syntax.

"ORDER BY position ... is not in select list"

You sorted (or grouped) by a column number that doesn't exist in the SELECT list — usually because a column got added or removed and a positional reference further down wasn't updated.

"near "TRUNCATE": syntax error"

TRUNCATE TABLE isn't a SQLite statement at all — it doesn't exist, not even in a limited form. Here's what to use instead, and why it isn't quite the same thing.

"UNION types ... cannot be matched"

The Nth column of one UNION branch is a different type than the Nth column of another. Postgres and SQL Server both refuse to guess how to reconcile them — SQLite just combines them anyway.

CAST / CONVERT failure — invalid value for type

You tried to convert a value into a type it doesn't actually fit — text that isn't a number, a number that overflows, a string that isn't a date. Most engines reject it outright; SQLite quietly does its best guess instead.

"Duplicate column name" (ALTER TABLE ADD COLUMN)

You tried to add a column with a name the table already has. Usually a re-run migration — sometimes a sign the column was never actually missing in the first place.

Integer division returns 0 (or truncates)

7 / 2 comes back 3, not 3.5, on every major engine — integer divided by integer stays an integer. Not a bug, but the single most common silent-wrong-result mistake in SQL.

"ON CONFLICT clause does not match any... constraint"

You named a conflict target in an upsert that isn't actually backed by a unique or primary key constraint. Postgres and SQLite both require one — here's why, and the two ways to fix it.

"relation already exists" / "Table already exists"

A CREATE TABLE ran against a name the database already has. Usually a re-run script, sometimes a genuine naming collision — IF NOT EXISTS fixes the first, not the second.

"String or binary data would be truncated"

A value longer than the column's declared length — most engines reject it or truncate it with a warning. SQLite does neither; it just stores the whole thing.

Ambiguous column name

Two joined tables both have a column with this name, and you referenced it without saying which table. Every engine refuses to guess — here's how to fix it and avoid it next time.

CHECK constraint failed

A row you tried to write violates a CHECK constraint's rule — the constraint is doing exactly what it was declared to do. The fix is deciding whether the data or the rule is wrong.

"column does not exist" / "no such column"

The column name in your query doesn't match the table's actual columns — a typo, a stale schema assumption, or a column you meant to reach through a join you didn't write.

Division by zero

Some engines throw a hard error, others silently return NULL — knowing which one you're on decides whether this bug crashes your query or just quietly corrupts a report.

Duplicate key / unique constraint violation

You inserted or updated a row into a value that already exists in a primary key or unique column. The fix depends on whether the duplicate is a bug or an expected case you need to handle.

Foreign key constraint fails

You tried to insert or update a row that points at a parent row which doesn't exist, or delete a parent row something still references. Plus the one thing every SQLite user eventually gets caught by — it isn't enforced unless you turn it on.

column must appear in the GROUP BY clause

You selected a column that isn't grouped and isn't wrapped in an aggregate. Postgres, MySQL (in strict mode), and SQL Server all refuse to guess which row's value you meant.

"no such table" / relation does not exist

The table name in your query doesn't match what the database actually has — usually a typo, the wrong schema, or a case-sensitivity trap. Here's how to find out which.

NOT NULL constraint failed

You tried to insert or update a row without a value in a column that requires one. Straightforward once you know whether the value was left out or genuinely doesn't exist yet.

Subquery returns more than one row

You used a subquery somewhere that expects exactly one value, and it returned several. Most engines stop you — SQLite, notably, won't.

"syntax error at or near" / "Incorrect syntax near"

A generic parser error that can mean a dozen different things. Here's how to read the pointer and the handful of causes that account for almost every case.

UNION: queries must have the same number of columns

Every SELECT combined with UNION, INTERSECT, or EXCEPT has to return the same number of columns, in a compatible order. Here's the fix and the type-compatibility trap that comes right after it.