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:
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
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
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
Consider the following table
create table t1 (x integer check (x > 0));
When inserting a negative value, we get the following error messages:
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
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)
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.
Take the following table definitions:
create table t1 (id integer primary key); create table t2 (id integer primary key, t1_id integer references t1);
t1 can’t be dropped without dropping the foreign key first (if we don’t use the
So what happens when we try to drop
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.
Could not drop object 't1' because it is referenced by a FOREIGN KEY constraint.
Again, it only tells us the table is referenced.
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.