Postgres: More verbose error message: I am missing the table name

Question

I see this message:

IntegrityError: null value in column "date" violates not-null constraint
DETAIL:  Failing row contains (10005, null, f, TEST, MAIL).

Is there a way to get a more verbose error message from PostgreSQL?

I am missing the table name.

you can see the model for which you are creating or updating the data. The table will be created in Django with the combination of app name and model name and ofcourse we can change it by overriding db_table in the meta data of the model. — MicroPyramid
– MicroPyramid, Commented Jan 31, 2017 at 14:26
@MicroPyramid I think it my mistake to talk about "django" here. I want a better error message from postgres. — guettli
– guettli, Commented Feb 1, 2017 at 11:54

score 11 · Accepted Answer · 2017-02-01 13:20:16Z

11

psql can do that using the VERBOSITY option:

psql (9.6.1)
Type "help" for help.

postgres> \set VERBOSITY verbose
postgres> insert into foobar (data) values ('x');
ERROR:  23502: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, x).
SCHEMA NAME:  public
TABLE NAME:  foobar
COLUMN NAME:  id
LOCATION:  ExecConstraints, execMain.c:1732
postgres>

This was introduced in 9.6. However, I don't know how this can be used from other clients.

Alternatively this can be obtained without setting VERBOSITY by using the meta command \errverbose

postgres> insert into foobar (data) values ('x');
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, x).

postgres> \errverbose
ERROR:  23502: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, x).
SCHEMA NAME:  public
TABLE NAME:  foobar
COLUMN NAME:  id
LOCATION:  ExecConstraints, execMain.c:1732

postgres>

This is apparently implemented on libpq level so it could theoretically be used from any program.

edited Feb 1, 2017 at 13:20

answered Feb 1, 2017 at 13:14

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

wildplasser Over a year ago

Yes, it is part of PQresultErrorField() For instance: github.com/JamesSjaalman/gigabert/blob/master/pgstuff.c (see under "extra:")

guettli Over a year ago

@a_horse_with_no_name I looked for something like this several months ago. Now it is available. Great news. Thank you.

guettli Over a year ago

Here is the follow up question: stackoverflow.com/questions/41996182/… "How to turn on PostgreSQL VERBOSITY in django db connection?"

Luciano Andress Martini Over a year ago

Is possible to get this details inside a PostgreSQL function?

NoelProf · Accepted Answer · 2022-12-09 22:48:26Z

4

When using psql:

\errverbose Reports the most recent error message in detail.

\set VERBOSITY verbose Shows the most context for any error reports for the current session or when the setting is revised down to default, terse, or sqlstate.

If running SQL scripts or batches of commands, a way to show context of an ERROR with psql is to use the --echo-errors option. The option prints failed SQL commands to standard error output.

If your sending a script to psql using the -f option shows the line number of the error.

answered Dec 9, 2022 at 22:48

NoelProf

9558 silver badges7 bronze badges

Collectives™ on Stack Overflow

Postgres: More verbose error message: I am missing the table name

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related