SQL / RELATIONAL FOUNDATIONS
Reading an error message when SQL complains
Diagnose a failing SQL statement by reading its error message: what broke, where the parser stopped, which class of failure it is, and what to change first.
What you will learn
- Read ERROR, LINE, caret, DETAIL, and HINT as five separate pieces of information
- Treat the caret as where the parser gave up, so the mistake is at or before it
- Classify failures by SQLSTATE class: 42 syntax or naming, 23 constraint, 22 data
- Fix the first error only, re-run, and check whether the caret moved
Understanding Reading an error message when SQL complains
A PostgreSQL error arrives as up to five labelled parts, and each answers a different question. ERROR gives the severity and a one-line reason, LINE echoes the statement text as the server received it, the caret under that line marks the offset the server could tie the failure to, DETAIL names the concrete values involved, and HINT is a guess at the fix. Nothing after the first line is decoration: the DETAIL under a constraint violation is what tells you which key collided, and skipping it is why people re-read their query ten times without learning anything new.
The offset matters more than beginners expect, because it marks where the parser stopped rather than where you typed something wrong. In SELECT id name dept FROM staff the text is legal SQL up to name, since SQL lets an alias follow an expression without AS, so the parser reads id AS name and only breaks at dept, two tokens past the missing comma. Read the caret as an upper bound: your mistake is under it or before it, usually in the one or two tokens immediately preceding.
Whether a position appears at all splits errors into two families. Syntax errors and name or type resolution errors such as column "nmae" does not exist and operator does not exist: text = integer are raised before a single row is touched, so the server can point into the text; unique violations, division by zero, and failed casts happen while rows are being processed, so there is no offset to report. The five-character SQLSTATE carries that classification in a stable form, 42601 syntax_error, 42703 undefined_column, 23505 unique_violation, 22012 division_by_zero, and unlike the English wording it does not change between versions or locales.
CREATE TABLE staff (id integer, name text, dept text);
INSERT INTO staff VALUES (1, 'Ada', 'eng'), (2, 'Grace', 'eng'), (3, 'Ken', 'ops');
SELECT id, name, FROM staff;
SELECT id, name FROM staff WHERE dept = 'eng';A SQL error tells you where the server stopped and which class of rule was broken, so the reported position is an upper bound on your mistake, not its address.
Worked examples
A name the server cannot resolve
Shows an error raised after the statement parsed cleanly, with a HINT that names the fix.
CREATE TABLE staff (id integer, name text, dept text);
INSERT INTO staff VALUES (1, 'Ada', 'eng');
SELECT nmae FROM staff;Example explained
Line 1The grammar accepted the statement, so this is not a syntax error: it failed when nmae was matched against the columns of staff.
Line 2The caret lands exactly on the bad identifier, because the analyzer knows the offset of the name it could not resolve.
Line 3HINT comes from comparing your identifier against the real column names, which makes it a suggestion rather than a verdict.
Line 4Its SQLSTATE is 42703 (undefined_column), which is what separates it from 42601 (syntax_error) even though psql prints both in the same shape.
The caret points past the mistake
A missing comma is reported at the following token, because that is where the parser could no longer continue.
SELECT id name dept FROM staff;Example explained
Line 1SELECT id name is well-formed SQL: name is read as the alias of id, so the parser has no reason to complain yet.
Line 2The grammar only breaks at dept, so the caret sits two tokens after the real problem, the missing comma following id.
Line 3Nothing here checks that staff or dept exist; the statement is rejected during parsing, so it fails the same way against an empty database.
An error with no position at all
A constraint violation carries DETAIL and a constraint name instead of a LINE and caret.
CREATE TABLE dept (code text PRIMARY KEY, label text);
INSERT INTO dept VALUES ('eng', 'Engineering');
INSERT INTO dept VALUES ('eng', 'Engines');Example explained
Line 1The second INSERT is textually perfect, so there is no LINE or caret: the problem was found while writing the row, not while reading the statement.
Line 2PRIMARY KEY created an index named dept_pkey, and quoting that name is how the server tells you which rule you broke.
Line 3DETAIL prints the column list and the colliding value, so you do not have to guess which of several unique constraints fired.
Line 4This is SQLSTATE 23505, and the 23 class means integrity constraint violation in any product that reports SQLSTATE.
Important notes
Only the structure transfers between products, not the words: MySQL reports error 1064 with SQLSTATE 42000 and quotes the fragment where it stopped, and SQLite says near "FROM": syntax error. Ask the same three questions of any of them.
In PostgreSQL, once a statement inside a BEGIN block fails, every later statement returns current transaction is aborted, commands ignored until end of transaction block. That is a consequence of the first error, so scroll up rather than debugging it.
Common mistakes
Deleting the token under the caret. In SELECT id name dept the caret sits on dept, so the query starts working with one fewer column instead of with the missing comma restored.
Ignoring the HINT line, then reading through the table definition looking for a column the server has already identified as a typo for staff.name.
Reading relation "Staff" does not exist as a missing table. Double quotes make the name case sensitive while unquoted Staff folds to staff, so creating "Staff" leaves you with two tables instead of a fix.
Try it yourself
Change, predict, then run
Run SELECT id name dept FROM staf; in the editor and note exactly which token the caret marks, then insert the missing comma and re-run. Explain why the new error now names staf even though you changed nothing about the table.
Open the SQL workspaceCheck your understanding
A statement fails with ERROR: duplicate key value violates unique constraint "dept_pkey" and DETAIL: Key (code)=(eng) already exists., and the output contains no LINE or caret. What does the missing position marker tell you?
- The statement parsed and planned successfully and failed while a row was being written, so the SQL text may be entirely correct
- The parser could not make sense of the statement at all, so it had no offset to report
- psql suppressed the position because the statement came from a file instead of being typed
- The constraint belongs to another table, so any position would refer to a statement you did not write
Show answer
A position is attached only when the server can map the failure onto an offset in the statement text, which is possible during parsing and name resolution. A unique violation is detected as the row is inserted, long after the text was accepted, so there is nothing to point at and DETAIL carries the specifics instead. Option 2 inverts the rule: an unparseable statement is exactly the case where PostgreSQL does print LINE and a caret, because the parser knows precisely how far it got.