postgresqldatabase-designconstraints

Table constraint for range and null values

Version PG PostgreSQL 16.3.

I need to add a constraint on a table on several fields depending on the range of values ​​"1,2,3,4" and null .

If we already have records where my_field4 is filled with 1 or 2 or 3 or 4, then we can no longer insert a record with my_field4 = null. Moreover, if we have a record with my_field4 filled in with, for example, 1, then we can insert another record with the value my_field4 = 2.

I do this, but the restriction does not work for records in which the my_field4 field has a null value.

ALTER TABLE sch.my_tbl ADD CONSTRAINT check_auto 
exclude  (field1  WITH =,field2 WITH =, field3 WITH =, my_field4 WITH =) WHERE ((my_field4 in(1,2,3,4,null)));

For example:

INSERT INTO sch.my_tbl (field1, field2, field3, my_field4) VALUES(1, 'two', 3, 1);--Should be OK
INSERT INTO sch.my_tbl (field1, field2, field3, my_field4) VALUES(1, 'two', 3, 2);--Should be OK
INSERT INTO sch.my_tbl (field1, field2, field3, my_field4) VALUES(1, 'two', 3, null);--Should be  unique constraint error
INSERT INTO sch.my_tbl (field1, field2, field3, my_field4) VALUES(2, 'text', 3, null);--Should be  OK
INSERT INTO sch.my_tbl (field1, field2, field3, my_field4) VALUES(2, 'text', 3, 1);--Should be  unique constraint error

I want to prohibit the insertion of a record where (f1,f2, f3) corresponds to an existing row, while f4 can only be with the values ​​1,2,3,4 or null for the same values ​​of f1,f2, f3. If there is a recording How to construct a constraint correctly?

Solution

  • The below table definition enforces your rules.
    Notably, null also conflicts with other null entries.

    Requires additional module btree_gist. Create once per database:

    CREATE EXTENSION btree_gist;

    Then:

    CREATE TABLE tbl (
  col1 int NOT NULL
, col2 text NOT NULL
, col3 int NOT NULL
, col4 int CHECK (col4 IN (1,2,3,4))  -- ①
, CONSTRAINT tbl_4fld_uniq UNIQUE NULLS NOT DISTINCT (col1, col2, col3, col4)  -- ②
, CONSTRAINT tbl_col4_null_vs_values EXCLUDE
     USING gist(hash_record_extended((col1, col2, col3), 0) WITH =
              , int8range(col4, col4, '[]') WITH &&)  -- ③
);

    fiddle

    ① Only allow said values for col4; null passes implicitly.

    ② Almost, but not quite enforce your restrictions. Still allows null alongside other values. See:

    ③ The exclusion constraint on a range makes null conflict with other values, because null signifies "unbounded" in a range, so the range [null, null] conflicts with every other range.

    hash_record_extended((col1, col2, col3) is an (optional) optimization, also explained in below related answer.

    Very similar case (a bit more complex, yet) with more explanation: