sql - 如何在 postgres 中的文本列上添加唯一约束(忽略特殊字符)？

Question

如何在 Postgres 中的文本列上添加唯一约束(忽略特殊字符)？

CREATE TABLE my_table(
    SomeTextColumn citext
CONSTRAINT person_u_1 UNIQUE (SomeTextColumn)
);

在上表中，我尝试添加一个唯一约束，该约束将通过忽略传入数据中的特殊字符来查找唯一性

For example:
1. HelloWorld --> Gets inserted successfully
2. Hello World --> Should fail with duplicate constraint
2. Hello%$^&*W^%orld --> Should fail with duplicate constraint

Answer 1

您可以创建一个唯一索引来实现检查:

create unique index t_txt_unique on t(regexp_replace(txt, '\W', '', 'g'));

正则表达式从字符串中删除所有非单词字符，仅保留字母数字字符和取消划线的 _。您可以根据需要调整角色类别。

<强> Demo on DB Fiddle :

create table t (id int, txt citext);
create unique index t_txt_unique on t(regexp_replace(txt, '\W', '', 'g'));

insert into t values(1, 'HelloWorld');
-- ok

insert into t values(1, 'Hello World');
-- ERROR:  duplicate key value violates unique constraint "t_txt_unique"
-- DETAIL:  Key (regexp_replace(txt, '\W'::text, ''::text, 'g'::text))=(HelloWorld) already exists.

insert into t values(1, 'Hello%$^&*W^%orld');
-- ERROR:  duplicate key value violates unique constraint "t_txt_unique"
-- DETAIL:  Key (regexp_replace(txt, '\W'::text, ''::text, 'g'::text))=(HelloWorld) already exists.

insert into t values(1, 'Hello Mars');
-- ok