PostgreSQL – Name of Not Null Constraint is Missing in Error Message

constraintpostgresqlpostgresql-9.6

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?

Best Answer

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.

Related Solutions

Sql-server – Check constraint only one of three columns is non-null

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK 
(
    ( CASE WHEN col1 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col2 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col3 IS NULL THEN 0 ELSE 1 END
    ) = 1
)
GO

PostgreSQL – Enforce NOT NULL with CHECK Constraint for New Rows

Mark all your existing rows as old:

ALTER TABLE integrations.billables
ADD COLUMN is_old BOOLEAN NOT NULL DEFAULT false;

UPDATE integrations.billables SET is_old = true;

And set up the constraint to ignore old rows:

ALTER TABLE integrations.billables
ADD CONSTRAINT cc_at_least_one_mapping_needed_billables 
CHECK (
    NOT(("qb_id", "xero_id", "freshbooks_id", "unleashed_id", "csv_data", "myob_id") IS NULL)
    OR is_old
);

(Yes, that IS NULL check works. See here.)

Advantages of this mechanism:

Constraint remains valid
You can continue to update old rows without filling in this value

Downsides:

A similar situation down the road will be messy. You would have to add a second boolean column or some other hoop jumping for the second new column.
If you want to force updated rows to be given a value, this won't do it.
This has the potential for abuse, since someone could just flip the is_old flag to true. (This can be addressed, though. See below.) This isn't something to be concerned about if end users can't access the database directly and you can trust the developers not to do wacky things with the data.

If you are worried about somebody changing the flag, you could set up a trigger to prevent any inserts or updates from setting is_old to true:

CREATE FUNCTION throw_error_on_illegal_old()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    IF NEW.is_old THEN
      -- Need to make sure we don't try to access
      -- OLD in an INSERT
      IF TG_OP = 'INSERT' THEN
        RAISE 'Cannot create new with is_old = true';
      ELSE
        IF NOT OLD.is_old THEN
          RAISE 'Cannot change is_old from false to true';
        END IF;
      END IF;
    END IF;
    -- If we get here, all tests passed
    RETURN NEW;
  END
  $$
;

CREATE TRIGGER billables_prohibit_marking_row_old
BEFORE INSERT OR UPDATE ON integrations.billables
FOR EACH ROW EXECUTE PROCEDURE throw_error_on_illegal_old()
;

You do still have to trust that no one who can modify the database schema is going to come along and drop your trigger or something, but if they're going to do that, they could drop the constraint, too.

Here's a SQLFiddle demo. Note that the "should skip" row is not in the output (as we desired).

Best Answer

Related Solutions

Sql-server – Check constraint only one of three columns is non-null

PostgreSQL – Enforce NOT NULL with CHECK Constraint for New Rows

Related Question