PostgreSQL unique value acreoss multiple columns

Question

I have PostgreSQL table

id    ColA    ColB
------------------
1     'a'     'b'
2     'c'     'd'

I want to make values in ColA and ColB to be unique across both columns i.e. any of these inserts would be forbidden:

INSERT INTO table (ColA,ColB) values('a','e');
INSERT INTO table (ColA,ColB) values('z','a');
INSERT INTO table (ColA,ColB) values('d','g');

and any of these inserts would be allowed:

INSERT INTO table (ColA,ColB) values('z','e');
INSERT INTO table (ColA,ColB) values('l','k');

So

CONSTRAINT unique_name UNIQUE (ColA,ColB)

is not suited, because it will allow any of previous 4 inserts.

pozs · Accepted Answer · 2015-06-12 15:15:46Z

Sadly, this cannot be solved easily with simple unique contraints / indexes (if it can be solved with them at all).

What you need, is an exclusion constraint: the ability to exclude some rows, based on something like collision. Unique constraints are just specific exclusion constraints (they are based on equality collisions).

So, in theory, you just need to exclude every row1, where there is already a row2, for which this expression is true: ARRAY[row1.cola, row1.colb] && ARRAY[row2.cola, row2.colb]

This index could do the job (currently only gist indexes support exclusion constraints):

ALTER TABLE table_name
  ADD CONSTRAINT table_name_exclusion
  EXCLUDE USING gist ((ARRAY[cola, colb]) WITH &&);

But unfortunately, there is no default operator class for arrays (which uses gist). There is an intarray module, which provides one for only integer arrays, but nothing for text arrays.

If you really want to work this out, you can always abuse the range types (f.ex. I used the adjacent -|- operator, which handles all the cases, which cannot be handled with unique) ...

-- there is no built-in type for text ranges neither,
-- but it can can be created fairly easily:
CREATE TYPE textrange AS RANGE (
  SUBTYPE = text
);

ALTER TABLE table_name
  ADD CONSTRAINT table_name_exclusion
  EXCLUDE USING gist ((textrange(least(cola, colb), greatest(cola, colb))) WITH -|-);

-- the exclusion constraint above does not handle all situations:

ALTER TABLE table_name
  ADD CONSTRAINT table_name_check
  CHECK (cola is distinct from colb); -- without this, empty ranges could be created,
                                      -- which are not adjacent to any other range

CREATE UNIQUE INDEX table_name_unique
  ON table_name ((ARRAY[least(cola, colb), greatest(cola, colb)]));
     -- without this, duplicated rows could be created,
     -- because ranges are not adjacent to themselves

... but I'm afraid, your original problem could be solved much easier with a little database refactoring; which brings us to the question: what problem, do you want to solve with this?

I have a similar problem: a user table has two columns for email addresses. One for the regular email and another one for a new not validated email. Now I want them to be unique no matter whether they are validated already or not yet.

Collectives™ on Stack Overflow

PostgreSQL unique value acreoss multiple columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related