Given a table with three columns:
CREATE TABLE tbl
column_scope text
, col1 int
, col2 int
);
I want to enforce, partitioned by column_scope
, that values in col1
, col2
, are unique across both columns - except for duplicates within the same row.
That is, within the scope, no two values in col1
and col2
can be the same, except for NULL
or for them to be the same row.
I tried adding an unique index (column_scope, col1, col2), however this would fail since it wouldn't take into consideration invalid cases such as:
row_1 -> (scope_1, x, NULL);
row_2 -> (scope_1, NULL, x);
or
row_1 -> (scope_1, x, y);
row_2 -> (scope_1, y, z);
I also tried adding a constraint to the table, however I can't seem to get the conditions right for assuring that col1
and col2
don't have shared values for a given column_scope
.
This is different from other questions as I don't care about the particular combination of values, I only care that values are not repeated among different columns, all within the scope of the third column.
Buckle up. This is an advanced solution.
Install two additional modules first (once per database): intarray and btree_gist. Then we can make this work with a single exclusion constraint:
CREATE EXTENSION intarray; -- required!
CREATE EXTENSION btree_gist; -- required!
CREATE TABLE tbl (
column_scope text NOT NULL
, col1 int -- can be null
, col2 int -- can be null
);
-- Add THIS exclusion constraint !!!
ALTER TABLE tbl ADD CONSTRAINT tbl_cross_col_unique
EXCLUDE USING gist (column_scope WITH =
, array_remove(ARRAY[col1, col2], null) gist__int_ops WITH &&);
Basically, the exclusion constraint disallows to "overlap" arrays built from col1
and col2
for the same column_scope
.
Note how I assume type integer
for col1
& col2
. Keeps it simple. For other types, you need to do more ...
You want to allow null values in col1
and col2
. So exclude those from the exclusion constraint. Lucky coincident - null values wouldn't be allowed in the generated array anyway.
The manual on array_remove()
:
Comparisons are done using
IS NOT DISTINCT FROM
semantics, so it is possible to removeNULL
s.
It all happens to fall into place.
There are many intricate details to this, concerning data types, index method, operator class, null values, index optimization, ... But that's beyond the scope of a simple question and enters the realm of paid consulting.
Closely related case with more details:
Alternatively, you could merge col1
and col2
into a single column of a separate table in a 1:n relationship (and add a tag to define the type). Either replacing what you have now, or additionally, just to enforce your constraint. Then, a plain UNIQUE
constraint can be applied. See:
This expression index implements a UNIQUE
index where (1,2)
and (2,1)
for (col1, col2)
are considered equal:
CREATE UNIQUE INDEX tbl_uni_idx ON tbl
(column_scope, GREATEST(col1, col2), LEAST(col1, col2));
See:
Null values are not considered equal unless you add the NULLS NOT DISTINCT
clause - which requires Postgres 15 or later. See: