Postgres having unique Constraint on Array Column And Text Column-CodePudding

create table my_table(
id varchar(255) not null primary key,
text_column varchar(255) not null,
array_column text[] not null
);

My table state is

id|text_column|array_column|
-- ----------- ------------ 
1 |Abcd       |{a,b}       |
2 |Abcd       |{a}         |
3 |Xyz        |{a,b}       |

I would want this to fail

insert into my_table values ('4', 'Abcd', '{"b", "a"}');
insert into my_table values ('5', 'Abcd', '{"a", "b"}');

I am trying to impose the unique constraint on text_column and array_column.

Array_column is not sorted.

Also is it better way to do?

CodePudding user response：

You could create an auxiliary function that sorts the elements of an array and use that in a unique index:

CREATE FUNCTION array_sort(anyarray) RETURNS anyarray
   IMMUTABLE LANGUAGE sql AS
'SELECT array_agg(a.e ORDER BY a.e) FROM unnest($1) AS a(e)';

CREATE UNIQUE INDEX ON my_table (text_column, array_sort(array_column));