Unique constraint for permutations across multiple columns

Question

Given the following three columns in a Postgres database: first, second, third; how can I create a constraint such that permutations are unique?

E.g. If ('foo', 'bar', 'shiz') exist in the db, ('bar', 'shiz', 'foo') would be excluded as non-unique.

Answer 1

You could use hstore to create the unique index:

CREATE UNIQUE INDEX hidx ON test USING BTREE (hstore(ARRAY[a,b,c], ARRAY[a,b,c]));

Fiddle

UPDATE

Actually

CREATE UNIQUE INDEX hidx ON test USING BTREE (hstore(ARRAY[a,b,c], ARRAY[null,null,null]));

might be a better idea since it will work the same but should take less space (fiddle).