Сравнение текстовых значений при миграции с Microsoft SQL Server в PostgreSQL

Зачастую при переходе с Microsoft SQL Server на PostgreSQL возникает проблема при сравнении текстовых значений. В отличие от Microsoft SQL Server база данных PostgreSQL чувствительна к регистру. Например, строки "CoMpANy" и "Company" равнозначны в Microsoft SQL Server, а в PostgreSQL — нет. Это различие может привести к разным результатам в Microsoft SQL Server и PostgreSQL при выполнении запросов, использующих сравнение текстовых значений. Также последствием может стать нарушение уникальности первичного ключа, если он создан на столбце текстового типа.

Для устранения этой проблемы при миграции с Microsoft SQL Server в PostgreSQL мы предлагаем следующие решения:

Создадим таблицу "customers" с двумя текстовыми полями "first_name" и "last_name" и заполним ее данными.

 create table customers (
   first_name varchar(64),
   last_name varchar(64)
 );

1. При сравнении текстовых данных нужно использовать "ilike" вместо "=":

   Microsoft SQL Server	PostgreSQL
   select * from customers where first_name = 'john'	select * from customers where first_name ilike 'john'

2. Для более сложного сравнения текстовых полей можно использовать функции LOWER() или UPPER():

   Microsoft SQL Server	PostgreSQL
   select * from customers where first_name in ('john', 'Stive')	select * from customers where LOWER(first_name) in (LOWER('John'), LOWER('Stive'))

3. При создании таблиц укажите для текстовых полей параметр collation без учета регистра:

   3.1. Создайте нечувствительное к регистру сглаживание. Более детальную информацию вы можете найти в документации PostgreSQL.

   CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

   3.2. Укажите эту корреляцию для всех текстовых столбцов в таблицах:

   create table customers (
   first_name varchar(64) COLLATE case_insensitive,
   last_name varchar(64) COLLATE case_insensitive
   );

   В этом случае нет необходимости менять запрос:

   Microsoft SQL Server	PostgreSQL
   select * from customers where first_name = 'john'	select * from customers where first_name = 'john'

4. Во время миграции вы можете преобразовать все текстовые данные в нижний или верхний регистр. В этом случае результаты сравнения текстовых данных будут одинаковыми в Microsoft SQL Server и PostgreSQL:
   Microsoft SQL Server

   first_name	last_name
   'John'	'Le'
   'Stive'	'Maison'
   'JOHN'	'SMITH'

   PostgreSQL

   first_name	last_name
   'john'	'le'
   'stive'	'maison'
   'john'	'smith'

5. Если есть столбец, который часто используется для поиска, можно создать дополнительный столбец, в котором текущее значение будет написано в верхнем регистре. Таким образом, при сравнении не нужно будет каждый раз делать значение из исходного столбца прописным, а использовать уже сделанную прописной строку из дополнительного столбца.

   create table customers (
   first_name varchar(64),
   last_name varchar(64),
   first_name_upper varchar(64) GENERATED ALWAYS AS (UPPER(first_name )) STORED
   );

   first_name	last_name	last_name_upper
   'john'	'le'	'JOHN'
   'stive'	'maison'	'STIVE'
   'john'	'smith'	'JOHN'

   
   

   Microsoft SQL Server	PostgreSQL
   select * from customers where first_name = 'john'	select * from customers where first_name_upper = UPPER('john')

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

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

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

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