postgresql Postgres对数组列和文本列具有唯一约束

g9icjywg  于 2022-12-12  发布在  PostgreSQL
关注(0)|答案(1)|浏览(121)
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?

k3bvogb1

k3bvogb11#

您可以创建一个辅助函数,对数组元素进行排序,并在唯一索引中使用该函数:

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));

相关问题