可延迟，不区分大小写的唯一约束 [英] Deferrable, case-insensitive unique constraint

问题描述

在PostgreSQL中，有可能在字符列上创建一个可延迟的唯一约束，但不区分大小写？

Is it possible in PostgreSQL to create a deferrable unique constraint on a character column, but case-insensitive?

让我们假设以下基本表：

Let's assume the following basic table:

CREATE TABLE sample_table ( 
   my_column VARCHAR(100)
);

如果不需要deferrable约束，就像使用函数创建唯一索引一样简单，例如： / p>

If deferrable constraint is not needed, it is as simple as creating unique index with function, e.g.:

CREATE UNIQUE INDEX my_unique_index ON sample_table(UPPER(my_column));

延迟约束检查需要明确创建约束，例如：

Deferred constraint check requires creating the constraint explicitly, e.g.:

ALTER TABLE sample_table 
 ADD CONSTRAINT my_unique_constraint UNIQUE(my_column)
 DEFERRABLE INITIALLY IMMEDIATE;

不幸的是，不可能在唯一约束中使用任意函数。

And unfortunately it is not possible to use arbitrary functions in unique constraint.

一种可能的解决方法是创建与 my_column 相同内容的附加列，但大写，在每次更新/插入后通过触发器更新，然后在此人工列上创建可延迟唯一约束。

One possible workaround would be to create additional column with the same content as my_column, but upper case, updated via a trigger after each update/insert, then create a deferrable unique constraint on this artificial column. This, however, sounds like a really ugly hack.

或者，应该可以使用 CREATE CONSTRAINT TRIGGER 并手动检查不区分大小写的唯一性（当然，仍然有必要使用常规索引）。这听起来有点过于复杂，对于这样一个简单（和流行，我想）要求。

Alternatively, it should be possible to use CREATE CONSTRAINT TRIGGER and manually check for case-insensitive uniqueness (of course a regular index would still be necessary). This sounds a bit overcomplicated for such a simple (and popular, I suppose) requirement.

有没有更简单和/或更优雅的方式围绕这个限制？

Is there any simpler and/or more elegant way around this limitation?

推荐答案

您可以使用特殊类型 citext 。引用手册：

You can circumvent the restriction by using the special type citext provided by the additional module of the same name. Quoting the manual:

citext 模块提供了一种不区分大小写的字符串类型，
citext。基本上，当比较值时，它在内部调用lower。
否则，它的行为几乎就像 text 。

The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text.

完全符合您的情况。每个数据库运行一次：

It addresses your case exactly. Run once per database:

CREATE EXTENSION citext;

然后您可以：

CREATE TABLE sample_table ( 
   my_column citext
  ,CONSTRAINT my_unique_constraint UNIQUE(my_column)
   DEFERRABLE INITIALLY IMMEDIATE
);

这篇关于可延迟，不区分大小写的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！