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?

  • Why don't you normalize your model? Then you can add a unique index. – a_horse_with_no_name Apr 23 '19 at 10:41
  • that is a known solution, whereas I just wanted to clarify to myself whether it is possible with array collumn – gumkins May 14 '19 at 6:18

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:

| improve this answer | |

You could make this with another table (where each row will be an ID and a single name) with a composite PK, but using an ARRAY is not possible.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.