Postgresql – uniqueness in array column type throughout all records

arraypostgresql

There is a table

id serial primary key,
names varchar(20)[]

it contains such record

1, {"one", "two", "three"}

Then, next record is inserting (notice one array element already exists in existing record)

2, {"four", "one"}

or existing record is updated by adding one more element to the array, but such string element is already used in some other record.

Is it possible to create a unique constraint which wouldn't allow repeating elements inside arrays globaly throughout all rows and not just inside the array value itself?

Best Answer

An exclusion constraint would seem like the weapon of choice:

ALTER TABLE tbl ADD CONSTRAINT tbl_names_overlap EXCLUDE USING gist (names WITH &&);

Alas, as of Postgres 11, still no GiST indexes for arrays. And GIN indexes are not (yet) allowed to implement exclusion constraints. There is a TODO item "Allow GIN indexes to be used for exclusion constraints", but I wouldn't know of any recent progress.

There would be a way with integer numbers after installing the additional module intarray - which provides GiST operator classes. So if your "numbers" are actual numbers ...

Else, here is a workaround I came up with some years ago:

Related Question