Helpful error messages

One of the little things that make working with Postgres easier than with other database products, are its error messages. They usually include not only the actual error but also a “Hint” that more often than not actually helps to fix the problem.

Simple syntax errors

Take the following query:

select f.*
from foo f
  join bar b on bar.foo_id = f.id;

The reference to bar.foo_id is invalid because once a table alias is introduced, all references must use that alias.

Let’s see how different databases tell us about this:

Oracle

ORA-00904: "BAR"."FOO_ID": invalid identifier
  join bar b on bar.foo_id = f.id
                ^

Not very helpful. After all BAR does have a column named FOO_ID

SQL Server

The multi-part identifier "bar.foo_id" could not be bound

Again not helpful as there absolutely is a column named foo_id in the table bar.

PostgreSQL

ERROR: invalid reference to FROM-clause entry for table "bar"
  Hint: Perhaps you meant to reference the table alias "b".

It doesn’t claim there is no such column, but tells us the reference to it is invalid. And right after that, it also tells us how to fix the problem: by using the alias b rather then bar.

Constraint details

Consider the following table

create table t1 (x integer check (x > 0));

When inserting a negative value, we get the following error messages:

Oracle

ORA-02290: check constraint (THOMAS.SYS_C0090246) violated

So no mentioning of the value that caused the problem. With a single row insert this isn’t a big deal. But imagine a multi row insert (either using insert into ... select or Oracle’s INSERT ALL) where it would be really interesting to see the value that caused the error.

The same happens when violating a unique constraint:

ORA-00001: unique constraint (THOMAS.SYS_C0090247) violated

SQL Server:

The INSERT statement conflicted with the CHECK constraint "CK__target__x__6CD828CA". The conflict occurred in database "thomasdb", table "dbo.target", column 'x'

For unique constraints, SQL Server tells us which value violates the constraint:

Violation of UNIQUE KEY constraint 'UQ__target__3BD019E4E04F2359'. Cannot insert duplicate key in object 'dbo.target'. The duplicate key value is (1)

PostgreSQL

Postgres tells us the actual value that violated the constraint:

ERROR: new row for relation "target" violates check constraint "target_x_check"
  Detail: Failing row contains (-1).

The same is true when a unique constraint is violated (just like SQL Server):

ERROR: duplicate key value violates unique constraint "target_x_key"
  Detail: Key (x)=(1) already exists.

Dependency information

Take the following table definitions:

create table t1 (id integer primary key);
create table t2 (id integer primary key, t1_id integer references t1);

The table t1 can’t be dropped without dropping the foreign key first (if we don’t use the CASCADE option).

So what happens when we try to drop t1?

Oracle

ORA-02449: unique/primary keys in table referenced by foreign keys

It tells us the table is referenced by at least one foreign key, but we don’t know which one.

SQL Server

Could not drop object 't1' because it is referenced by a FOREIGN KEY constraint.

Again, it only tells us the table is referenced.

PostgreSQL

ERROR: cannot drop table t1 because other objects depend on it
  Detail: constraint t2_t1_id_fkey on table t2 depends on table t1
  Hint: Use DROP ... CASCADE to drop the dependent objects too.

It tells us the name of the foreign key that references t1 and the name of the table. If there are multiple foreign key constraints, all of them would be listed. And again the friendly HINT that tells us how we can deal with that situation.