Отложенное, без учета регистра уникальное ограничение

Question

Возможно ли в 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 и вручную проверять уникальность без учета регистра (конечно, регулярный индекс все еще был бы необходим). Это звучит слишком сложно для такого простого (и, я полагаю, популярного) требования.

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

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