Отключаемое, нечувствительное к регистру уникальное ограничение

Возможно ли в PostgreSQL создать отложенное уникальное ограничение на столбец символов, но без учета регистра?

Предположим, что следующая базовая таблица:

CREATE TABLE sample_table ( 
   my_column VARCHAR(100)
);

Если отложенное ограничение не требуется, оно так же просто, как создание уникального индекса с функцией, например:

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

Отложенная проверка ограничений требует создания ограничения явно, например:

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

И, к сожалению, невозможно использовать произвольные функции в единственном ограничении.

Одним из возможных способов решения проблемы будет создание дополнительного столбца с тем же содержимым, что и my_column, но в верхнем регистре, обновляемом через триггер после каждого обновления/вставки, а затем создайте отложенное уникальное ограничение для этого искусственного столбца. Это, однако, звучит как действительно уродливый хак.

В качестве альтернативы, следует использовать CREATE CONSTRAINT TRIGGER и вручную проверять уникальность без учета регистра (конечно, по-прежнему необходим регулярный индекс). Это звучит немного сложнее для такого простого (и популярного, я полагаю) требования.

Есть ли более простой и/или более элегантный способ ограничения этого ограничения?

Ответы

Ответ 1

Вы можете обойти это ограничение, используя специальный тип citext, предоставляемый дополнительным модулем с тем же именем. Указание руководства:

Модуль citext предоставляет строковый тип строки без учета регистра, CITEXT. По сути, он внутренне вызывает меньше при сравнении значений. В противном случае он ведет себя почти так же, как text.

Он точно описывает ваше дело. Выполняется один раз в базе данных:

CREATE EXTENSION citext;

Затем вы можете:

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