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.