Name of not null constraint is missing in error message

The relation was created in Postgres 9.6 with use of the following commands:

CREATE TABLE import_event (
   import_event_id BIGINT NOT NULL,
   filename VARCHAR (200) CONSTRAINT NN_import_event__filename NOT NULL
 );

 ALTER TABLE ONLY import_event ADD CONSTRAINT PK_import_cak_key PRIMARY KEY (import_event_id);

Notice that not null constraint is named.

Then the following command was executed

insert into import_event (import_event_id) values (1);

Of course, the constraint violation error was raised. The content of the message is as follows

ERROR:  null value in column "filename" violates not-null constraint
 DETAIL:  Failing row contains (1, null).**strong text**

The problem is that the name of not null constraint is missing in that message. However, the correct error message:

ERROR:  new row for relation "import_event" violates check constraint "nn_import_event__filename"

is produced when not null constraint is declared as follows

ALTER TABLE ONLY import_event ADD CONSTRAINT NN_import_event__filename CHECK(filename IS NOT NULL);

Is that a bug in Postgres 9.6 or is there a problem with constraint declaration?

So far as I can tell the bit that goes CONSTRAINT NN_import_event__filename is just ignored.

the name nn_import_event__filename is not present when I dump the database, only the not-null attribute remains.

This does not match the the example given in the documentaion which claims that it is possible to name a not null constraint.


Define two NOT NULL column constraints on the table distributors, one of which is explicitly given a name:

CREATE TABLE distributors (
did integer CONSTRAINT no_null NOT NULL,
name varchar(40) NOT NULL
);

So, you've found a bug. if you report it it will probably be corrected fairly quickly, but the fix may be merely an update to the documentation.

StackExchange Database Administrators Q#209305, answer score: 5