Name of not null constraint is missing in error message

Question

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?

Jasen · Accepted Answer · 2018-06-12T08:47:05.877

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.

Evan Carroll · Answer 2 · 2018-06-12T17:00:38.837

That's because you're adding a check constraint when you run,

ALTER TABLE foo
  ADD CONSTRAINT myconstraint CHECK(bar IS NOT NULL);

Which produces this message.

ERROR:  new row for relation "foo" violates check constraint "myconstraint"
DETAIL:  Failing row contains (null).

That's not the same as,

ALTER TABLE foo
  ALTER COLUMN bar
  SET NOT NULL;

One of them is a CHECK predicate the other is a NOT NULL bit in a NULL Bitmap. Presumably, the planner could use the NOT NULL bit in certain circumstances to plan this more efficiently, though afaik it doesn't yet currently do so.

What's happening

From irc.freenode.net/#postgresql,

11:54 < RhodiumToad> NOT NULL is syntactically a constraint, but it doesn't currently get a pg_constraint entry

I believe that what's supposed to be supported is

ADD CONSTRAINT foo NOT NULL

And, then

ALTER TABLE myTable
  ALTER CONSTRAINT foo
  DROP NOT NULL;

But it seems, and I am guessing, that PostgreSQL implements NOT NULL not as an actual constraint but as a function of the storage system and so there is no entry in pg_constraint

Name of not null constraint is missing in error message

2 Answers2

What's happening

See also