CODEWITHSUNDEEP
pythonpostgresqlloggingpsycopg2
egst
egst

Reputation: 1804

PostgreSQL explicit not-null violation check happens before the check constraint?

I'm trying to use named constraints to be able to tell what and where exactly happened from the error messages. For example, when I set a not-null constraint on a column "foo" in a table "bar" and then try to add a row with a null value in it I get:

ERROR: null value in column "foo" violates not-null constraint

I don't get the name of the table, and I can't use this error message to extract any useful information. On the other hand, when I remove the not-null constraint and add a check (foo is not null) constraint, which can be named to something like bar__foo__nn, I immediately get the table name, the column name, and something like an error code "nn" for not-null violation. I can even parse this constraint name to extract the needed information for further error logging in my application.

From what I've read, the explicit not-null constraint should be more efficient and allow more optimizations. Also, primary keys must have the explicit not-null constraint set. So I tried using both, but what happens is, that I only get the "default" not-null violation error above and no mention of my "custom" named constraint violation.

Is there any way to use the explicit not-null constraint and still have some convenient way to "customize" the potential not-null violation error?

EDIT

I didn't mention, that I access the database with Python using psycopg2. I didn't want to make this psycopg2 specific as I use postgresql in other environments as well and a general overview of how this works in postgresql would be useful. For example, how to extract the needed information (the table name and the column name) inside a stored procedure/function to serialize it in some way inside the postgresql code (in the exception clause), so that the application connecting to the database (be it Python with psycopg2 or anything else) sees the errors the way I want them to be when calling the stored procedures.

Upvotes: 0

Views: 602

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247260

You tagged , so I assume you are interested in the PostgreSQL log file.

Unless you set log_min_error_statement to fatal or higher, the statement will always be included in the error message:

2020-04-02 14:40:28.253 CEST [3414] ERROR:  null value in column "a1" violates not-null constraint
2020-04-02 14:40:28.253 CEST [3414] DETAIL:  Failing row contains (12, null).
2020-04-02 14:40:28.253 CEST [3414] STATEMENT:  INSERT INTO a VALUES (12, NULL);

Hint: if you use log_destination = csvlog, the log file is easier to parse.

If you are interested in the message sent to the client, it depends on the client or API you are using to access PostgreSQL. The server sends the information along with the error message.

This is how it works with the psql client:

test=> \set VERBOSITY verbose
test=> INSERT INTO a VALUES (12, NULL);
ERROR:  23502: null value in column "a1" violates not-null constraint
DETAIL:  Failing row contains (12, null).
SCHEMA NAME:  laurenz
TABLE NAME:  a
COLUMN NAME:  a1
LOCATION:  ExecConstraints, execMain.c:1960

Since you are interested in Python, here is a sample program that runs the same statement:

#!/usr/bin/python3

from psycopg2 import connect, Error
from psycopg2.extensions import quote_ident

conn = connect("dbname=test user=laurenz")

cur = conn.cursor()

try:
    cur.execute("INSERT INTO a VALUES (12, NULL)")
except Error as e:
    print("Error: {}".format(e.diag.message_primary))
    print("Detail: {}".format(e.diag.message_detail))
    print("SQLSTATE: {}".format(e.diag.sqlstate))
    print("Table: {}.{}".format(
               quote_ident(e.diag.schema_name, cur),
               quote_ident(e.diag.table_name, cur)
         ))

conn.commit()

cur.close()
conn.close()

The result is:

Error: null value in column "a1" violates not-null constraint
Detail: Failing row contains (12, null).
SQLSTATE: 23502
Table: "laurenz"."a"

Upvotes: 1

Related Questions