Viswamitra's blog

psql is a command line tool which used in Linux/Unix operating systems to connect to PostgreSQL. psql provides different interesting features which makes a DBA life easier. In this series of posts , I will write some useful hacks for day to day life of a DBA/Developer.

errverbose

This is a new feature in PostgreSQL 9.6, we will see a use case for this

Session 1:

postgres=# begin;
BEGIN
postgres=# set transaction isolation level serializable ;
SET
postgres=#
postgres=# create table test_1(id int);
CREATE TABLE
postgres=# commit;
COMMIT

Session 2:

postgres=# begin;
BEGIN
postgres=# set transaction isolation level serializable ;
SET
postgres=# create table test_1(id int);
ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(test_1, 2200) already exists.

I started both sessions at same time and started creating table, but committed the first session before the second one. I got an error that some unique constraint violated(table already exists). I want to know more info like for which table constraint violation happened, with additional details. I can simply use the “\errverbose” which gives more information about error like below.

postgres=# \errverbose
ERROR: 23505: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(test_1, 2200) already exists.
SCHEMA NAME: pg_catalog
TABLE NAME: pg_type
CONSTRAINT NAME: pg_type_typname_nsp_index
LOCATION: _bt_check_unique, nbtinsert.c:433

You can see that the it gives table name along with that it give source code file and line in that (beauty of opensource :)).