Unique Constraint For Permutations Across Multiple Columns

February 04, 2024 Post a Comment

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') exis

Solution 1:

You could use hstore to create the unique index:

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

Fiddle

UPDATE

Actually

CREATEUNIQUE 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).

Solution 2:

For only three columns this unique index using only basic expressions should perform very well. No additional modules like hstore or custom function needed:

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 needs the least 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 bytes for index row in the example in the fiddle, measured with pg_column_size(). My example uses only single characters, the difference in size is constant.

Solution 3:

You can do this by creating a unique index on a function which returns a sorted array of the values in the columns:

CREATEOR REPLACE FUNCTION sorted_array(anyarray)
RETURNS anyarray
AS $BODY$
  SELECTarray_agg(x) FROM (SELECTunnest($1) AS x FROM test ORDERBY x) AS y;
$BODY$
LANGUAGEsql IMMUTABLE;

CREATEUNIQUE index ON test (sorted_array(array[first,second,third]));

Solution 4:

Suggestion from co-worker, variation of @julien's idea:

Sort the terms alphabetically and place a delimiter on either side of each term. Concatenate them and place them in a separate field that becomes the primary key.

Why the delimiter? So that, "a", "aa", "aaa" and "aa", "aa", "aa" can both be inserted.

Lab Sql