Unique constraint for multi-column permutations

Question

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

eg. If ('foo', 'bar', 'shiz') exists in db, ('bar', 'shiz', 'foo') will be excluded as not unique.

Answer 1

You can use hstore to create a 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])); 

may be a better idea, as it will work the same, but should take up less space ( fiddle ).

Answer 2

For only three columns, this unique index using only basic expressions should work very well. No additional modules, such as hstore or custom functions, are required:

 CREATE UNIQUE INDEX t_abc_uni_idx ON t ( LEAST(a,b,c) , GREATEST(LEAST(a,b), LEAST(b,c), LEAST(a,c)) , GREATEST(a,b,c) ); 

SQL fiddle

Also need less disk space:

 SELECT pg_column_size(row(hstore(t))) AS hst_row ,pg_column_size(row(hstore(ARRAY[a,b,c], ARRAY[a,b,c]))) AS hst1 ,pg_column_size(row(hstore(ARRAY[a,b,c], ARRAY[null,null,null]))) AS hst2 ,pg_column_size(row(ARRAY[a,b,c])) AS arr ,pg_column_size(row(LEAST(a,b,c) , GREATEST(LEAST(a,b), LEAST(b,c), LEAST(a,c)) , GREATEST(a,b,c))) AS columns FROM t; hst_row | hst1 | hst2 | arr | columns ---------+------+------+-----+--------- 59 | 59 | 56 | 69 | 30 

Numbers are the bytes for the index row in the example in pg_column_size() , measured with pg_column_size() . In my example, only individual characters are used, the difference in size is constant.

Answer 3

You can do this by creating a unique index for a function that returns a sorted array of values ​​in columns:

 CREATE OR REPLACE FUNCTION sorted_array(anyarray) RETURNS anyarray AS $BODY$ SELECT array_agg(x) FROM (SELECT unnest($1) AS x FROM test ORDER BY x) AS y; $BODY$ LANGUAGE sql IMMUTABLE; CREATE UNIQUE index ON test (sorted_array(array[first,second,third])); 

Answer 4

Suggestion from an employee, variation of @julien's idea:

Disconnect the terms in alphabetical order and place a separator on either side of each term. Concentrate them and place them in a separate field, which will become the main key.

Why a separator? Thus, “a”, “aa”, “aaa” and “aa”, “aa”, “aa” can be inserted.