Сравнение Microsoft SQL Server и PostgreSQL без учета регистра

Чувствительность к регистру в операциях сравнения строк — известная проблема в PostgreSQL. Существует несколько способов ее решения, мы рассмотрим три из них.

Предположим, что у нас есть следующая таблица в Microsoft SQL Server:

   create table tlike (id int, title varchar(20), title2 varchar(20));
   insert into tlike values
        (1, 'company', 'CoMpAnY'),
        (2, 'CompAny', 'CoMpAnY'),
        (3, 'CoMpAnY', 'CoMpAnY'),
        (4, 'COMPANY', 'company'),
        (5, 'COMPANY', 'CO_PA%Y');

И нам нужно, чтобы после переноса в PostgreSQL следующие запросы обрабатывались точно так же, как в Microsoft SQL Server:

   select * from tlike where title = title2;
   select * from tlike where title like title2;
   select * from tlike where title in (title2);

Для решения этой проблемы выполните одно из следующих действий:

1. Используйте тип citext (более детальную информацию вы можете найти в документации PostgreSQL). Для этого необходимо один раз создать расширение в базе данных:

   create extension citext;

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

   /* citext as column type*/
   create table tlike 
   (
    id integer,
    title citext,
    title2 citext 
   );
   
   insert into tlike values <...>
   
   select * from tlike where title = title2 and id > 0;
   select * from tlike where title ilike title2 and id > 0;
   select * from tlike where title in (title2);

   /* citext as a variable type */
   DO $$
   DECLARE
   title  citext default 'company';
   title2 citext default 'COMPANY';
   BEGIN
    IF (title = title2 ) THEN
        RAISE NOTICE 'equal';
    END IF;   
    IF (title ilike title2 ) THEN
        RAISE NOTICE 'ilike';
    END IF;   
   END;$$;    

2. Вы также можете добавить "lower" для каждого сравнения строк (переменных, полей таблиц, констант). Если вы используете "=" или "in" для сравнения строк, то обе части сравнения должны быть обернуты в "lower". А если используется "like", то оно будет заменено на "ilike" (как в предыдущем примере). Примеры использования:

   /* compare the column */
   create table tlike 
   (
    id integer,
    title varchar(20),
    title2 varchar(20) 
   );
   
   insert into tlike values <...>
   
   select * from tlike where lower(title) = lower(title2) and id > 0;
   select * from tlike where title ilike title2 and id > 0;
   select * from tlike where lower(title) in (lower(title2));

   /* comparison of variables */
   DO $$
   DECLARE
   title  varchar(20) default 'company';
   title2 varchar(20) default 'COMPANY';
   BEGIN
    IF (lower(title)  = lower(title2)) THEN
        RAISE NOTICE 'equal';
    END IF;   
    IF (title ilike title2 ) THEN
        RAISE NOTICE 'ilike';
    END IF;   
   END;$$;

3. Также вы можете создать собственные COLLATION и использовать их следующим образом:
   Добавьте COLLATION к типам столбцов и переменным (deterministic = false). При сравнении с like он будет заменен на ilike и будет добавлена COLLATION (deterministic = true). Примеры использования:

   CREATE COLLATION IF NOT EXISTS swcol_ci_nondet (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
   CREATE COLLATION IF NOT EXISTS swcol_ci_det (provider = icu, locale = 'und-u-ks-level2', deterministic = true);
   /* compare the column */
   create table tlike 
   (
    id integer,
    title varchar(20)  COLLATE swcol_ci_nondet,
    title2 varchar(20) COLLATE swcol_ci_nondet
   );
   insert into tlike values  <...>
   
   select * from tlike where title = title2 and id > 0;
   select * from tlike where title ilike title2 COLLATE swcol_ci_det and id > 0;
   select * from tlike where title in (title2);

   /* comparison of variables */
   DO $$
   DECLARE
   title  varchar(20) COLLATE swcol_ci_nondet default 'company';
   title2 varchar(20) COLLATE swcol_ci_nondet default 'COMPANY';
   BEGIN
    IF (title = title2) THEN
        RAISE NOTICE 'equal';
    END IF;   
    IF (title ilike title2 COLLATE swcol_ci_det) THEN
       RAISE NOTICE 'ilike';
    END IF;   
   END;$$;