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?
4
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:
- Create unique constraint with null columns
③ 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:
- Unique index on 3 columns where NULL conflicts with all other values in one column
1