С MS SQL на PostgreSQL: что меняется в коде и данных

Перенос с MS SQL Server на PostgreSQL ломается не на таблицах и не на объёме данных. Ломается он в пяти местах: в T-SQL-логике хранимых процедур, в сравнении и сортировке строк, в датах и времени, в автоинкрементных ключах после загрузки и в том, как две СУБД ведут себя при одновременной работе многих сессий. Всё остальное — типы, ключи, индексы — переносится почти формально.

Эта статья — инженерный разбор различий: что именно отличается, чем это проявляется и как проверить. Это не гарантия, что всё заработает: версию SQL Server и состав объектов нужно смотреть на конкретной системе. Общую схему переноса с этапами, ценами и порядком работ см. на странице миграции MS SQL на PostgreSQL.

Что на самом деле меняется при переходе с MS SQL на PostgreSQL

Различия удобно разложить по слоям: на нижних всё просто, на верхних приходится читать код и мерить.

Типы данных: чем заменить DATETIME, BIT, MONEY и UNIQUEIDENTIFIER

Основная часть типов сопоставляется напрямую: int, bigint, smallint, numeric, date, xml имеют одноимённые аналоги. Остальное — в таблице.

Если в базе есть HIERARCHYID, GEOGRAPHY, SQL_VARIANT или типы CLR, прямого переноса нет. Для иерархий и геоданных есть расширения (ltree, PostGIS), но решение принимают по тому, какие функции типа реально вызывает код.

Строки и регистр: почему поиск по имени перестал находить записи

Самое частое расхождение после переноса — регистр. Типовые правила сортировки SQL Server нечувствительны к регистру (в названии правила обычно есть CI), поэтому WHERE name = 'ромашка' находит «Ромашка» и «РОМАШКА». В PostgreSQL сравнение по умолчанию регистрозависимо. Данные при этом не меняются, меняется результат запроса.

Условный пример (не из практики заказчика): отчёт группирует продажи по названию контрагента. В SQL Server «ООО Ромашка» и «ООО РОМАШКА» складывались в одну строку, в PostgreSQL выходит две. Число строк в таблицах сходится, а итоги по группам нет.

Варианты решения, у каждого свои ограничения:

• привести данные и запросы к одному регистру: lower() в запросе плюс индекс по выражению;
• расширение citext для столбцов, где регистр не нужен;
• недетерминированные правила сортировки на ICU (с версии 12): они дают сравнение без учёта регистра, но с ограничениями, например по оператору LIKE; сверяем с документацией вашей версии;
• ILIKE для поиска по шаблону.

Ещё несколько расхождений в строках:

• Пробелы в конце. SQL Server при сравнении игнорирует хвостовые пробелы, для varchar и text в PostgreSQL они значимы. Функция LEN пробелы не считала, length() считает.
• Шаблоны LIKE. Конструкции с квадратными скобками ([a-z], [^0-9]) в PostgreSQL не работают, их заменяют регулярными выражениями (~) или SIMILAR TO.
• Склейка строк. Оператор + заменяется на || или concat(). Различие в NULL: || с NULL даёт NULL, concat() NULL пропускает.
• Имена объектов. PostgreSQL приводит имена без кавычек к нижнему регистру. Если инструмент перенёс «ВерблюжийРегистр» в кавычках, придётся брать в кавычки имя в каждом запросе. Практичнее с самого начала перейти на имена в нижнем регистре.
• Сортировка. Порядок строк зависит от правил, заданных при создании базы, и от версии библиотеки операционной системы или ICU. После обновления ОС индексы по текстовым столбцам, возможно, придётся перестроить. Кодировку и правила сортировки выбирают до создания базы: потом их не поменять без пересоздания.

NULL и пустые строки: где поведение расходится

В отличие от Oracle, SQL Server и PostgreSQL различают пустую строку и NULL одинаково, поэтому тут сюрпризов меньше. Но расхождения есть, и все они тихие.

• Порядок сортировки. В SQL Server NULL при сортировке по возрастанию идут первыми, в PostgreSQL — последними. Пользователь видит перевёрнутый список. Для сохранения поведения пишут ORDER BY x NULLS FIRST.
• Ограничения уникальности. Индекс UNIQUE в SQL Server допускает единственное значение NULL, в PostgreSQL — любое число. Ограничения, которые защищали от дублей с пустым значением, перестают защищать. В новых версиях есть NULLS NOT DISTINCT, но версию нужно проверить.
• ISNULL и COALESCE. ISNULL возвращает тип первого аргумента, COALESCE подбирает общий тип. На строках разной длины и числах разной точности результат может отличаться.
• Сравнение с NULL. Если приложение запускало код с SET ANSI_NULLS OFF, условия = NULL придётся переписывать на IS NOT DISTINCT FROM или IS NULL.

Даты и часовые пояса: GETDATE(), DATEDIFF и timestamptz

В SQL Server даты чаще всего хранятся без пояса, как локальное время сервера. Безопаснее всего переносить их в timestamp, а не в timestamptz: значения остаются теми же. Перейти на timestamptz можно позже, отдельным решением, когда понятно, в каком поясе писали данные.

• Текущее время. GETDATE() заменяется на now() или current_timestamp, но они возвращают timestamptz и фиксируются на начало транзакции. Если нужно время вызова, берут clock_timestamp(). Для времени по UTC: now() AT TIME ZONE 'UTC'.
• DATEDIFF. Считает не прошедшее время, а число пересечённых границ: DATEDIFF(day, ...) между 23:59 и 00:01 следующих суток даёт единицу. В PostgreSQL вычитание дат даёт интервал или целое число суток, и результат на пограничных случаях другой.
• Начало месяца. Классический приём DATEADD(month, DATEDIFF(month, 0, d), 0) заменяется на date_trunc('month', d). Приведение числа к дате в духе CAST(0 AS datetime) в PostgreSQL не работает, пустую дату выражают через NULL или явное значение.
• AT TIME ZONE. Конструкция есть в обеих СУБД, но ведёт себя по-разному: в PostgreSQL она превращает timestamp в timestamptz и обратно. Каждое место использования проверяют отдельно.

IDENTITY и последовательности: что сломается после загрузки данных

Столбцу IDENTITY в PostgreSQL соответствует столбец GENERATED ... AS IDENTITY (с версии 10) или обычная последовательность. Основная ловушка — загрузка данных вместе с готовыми ключами: COPY и INSERT с явными значениями последовательность не сдвигают. Первая же вставка из приложения получит ключ 1 и упадёт на нарушении уникальности.

Порядок действий после загрузки:

1. для каждой таблицы с автоключом найти последовательность через pg_get_serial_sequence();
2. выставить её на максимум существующего ключа: setval(pg_get_serial_sequence('orders', 'id'), max(id)) из той же таблицы;
3. для пустых таблиц последовательность оставить как есть;
4. сверить значения последовательностей со скриптом перед каждой репетицией и перед боевым переключением.

Остальные соответствия: SET IDENTITY_INSERT ON заменяется столбцом GENERATED BY DEFAULT или вставкой с OVERRIDING SYSTEM VALUE; SCOPE_IDENTITY() и @@IDENTITY — фразой RETURNING id; DBCC CHECKIDENT — установкой значения последовательности. Нумерацию «без пропусков» на последовательностях строить нельзя в обеих СУБД: откат транзакции значение не возвращает.

TOP, постраничная выборка, APPLY, MERGE и OUTPUT: что переписывается в запросах

Синтаксис, который в приложении придётся менять почти наверняка. ORM закрывает часть случаев, но SQL-вставки в код и отчёты остаются.

Для постраничного вывода на больших таблицах лучше «клавишная» выборка: WHERE (created, id) > (:c, :i) ORDER BY created, id LIMIT 20. Она не читает пропущенные страницы и не «плывёт» при вставках.

Хранимые процедуры T-SQL и PL/pgSQL: процедуры, транзакции, курсоры, временные таблицы

Здесь работа самая объёмная, и здесь автоматический конвертер заметнее всего ошибается. Ниже — места, где логика расходится по смыслу, а не по синтаксису.

Процедуры и функции

В T-SQL процедура может вернуть несколько наборов результатов, и приложение читает их по очереди. В PostgreSQL такого поведения нет: функция возвращает один набор (RETURNS TABLE, SETOF) либо курсоры. Команда CREATE PROCEDURE и вызов CALL появились в версии 11 и позволяют управлять транзакцией внутри. Приложение, которое ждёт несколько наборов из одного вызова, переписывают на несколько вызовов.

Транзакции и ошибки

Это самое неочевидное различие. Если в SQL Server внутри открытой транзакции команда упала, транзакция может продолжаться (при XACT_ABORT OFF). В PostgreSQL после любой ошибки вся транзакция считается проваленной, пока её не откатят целиком или до точки сохранения. Блок BEGIN ... EXCEPTION ... END в PL/pgSQL создаёт вложенную подтранзакцию, и использование его в цикле заметно удорожает выполнение. Завершить транзакцию изнутри можно только в процедуре, вызванной на верхнем уровне и вне блока с обработчиком исключений.

Коды ошибок тоже другие. Приложение, которое проверяет номера SQL Server (1205 — взаимная блокировка, 2627 и 2601 — нарушение уникальности), переписывается на коды SQLSTATE: 40P01 — взаимная блокировка, 23505 — нарушение уникальности, 23503 — внешний ключ, 40001 — сбой сериализации.

Временные таблицы и табличные переменные

Табличных переменных в PostgreSQL нет, вместо них временные таблицы (CREATE TEMP TABLE) либо массивы и общие табличные выражения. Локальная временная таблица T-SQL живёт до конца процедуры, а в PostgreSQL — до конца сессии, если не задано ON COMMIT DROP. С пулом соединений это даёт конфликты имён между вызовами. Глобальных временных таблиц нет. Автоочистка временные таблицы не обрабатывает, поэтому для больших нужен ручной ANALYZE.

Курсоры и динамический SQL

Конструкции DECLARE c CURSOR ... FETCH NEXT ... @@FETCH_STATUS заменяются циклом FOR rec IN SELECT ... LOOP, а чаще — одним запросом. EXEC(@sql) и sp_executesql заменяются на EXECUTE format(...) USING ...: имена объектов вставляют через %I, значения передают параметрами, иначе остаётся риск внедрения SQL.

XML и JSON

JSON в SQL Server чаще всего лежит в строковом столбце и читается функциями OPENJSON, JSON_VALUE. В PostgreSQL для него есть тип jsonb с операторами ->, ->> и функциями jsonb_array_elements, jsonb_to_recordset. Методы XML .value() и .nodes() заменяются на xpath() и xmltable; поддержка XPath там ограничена первой версией языка, что для сложных выражений приходится учитывать.

Что вообще выходит за рамки SQL

• xp_cmdshell, сборки CLR, OPENROWSET, BULK INSERT — прямых аналогов нет, функциональность переносится в приложение или во внешний скрипт, а загрузка файлов делается через COPY;
• связанные серверы заменяются расширениями-обёртками внешних данных, их доступность в вашей сборке проверяем;
• полнотекстовый поиск CONTAINS и FREETEXT заменяется на tsvector и tsquery с индексом GIN; для русского языка есть встроенная конфигурация;
• задания SQL Server Agent переезжают на pg_cron или на планировщик ОС, пакеты SSIS и отчёты SSRS — в другие средства, и эта работа сопоставима с переносом самой базы.

Блокировки, MVCC и VACUUM: почему конкурентная работа ведёт себя иначе

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

• Старые версии строк. Каждое изменение создаёт новую версию строки, старая остаётся до очистки. Очистку выполняет автоочистка (autovacuum), а долгая открытая транзакция, в том числе «зависшая» в состоянии idle in transaction, мешает ей и раздувает таблицы. После массовых загрузок и удалений нужен ANALYZE, иногда VACUUM; место таблице автоочистка почти не возвращает. Для сжатия есть VACUUM FULL, блокирующий таблицу, и внешние инструменты перепаковки.
• Уровни изоляции. По умолчанию, как и в SQL Server, чтение подтверждённых данных. READ UNCOMMITTED в PostgreSQL ведёт себя как READ COMMITTED: грязного чтения нет. На REPEATABLE READ и SERIALIZABLE транзакция может получить сбой сериализации (40001), и приложение должно уметь повторять её целиком.
• Хинты блокировок. NOLOCK убирают, UPDLOCK заменяют на SELECT ... FOR UPDATE, READPAST — на FOR UPDATE SKIP LOCKED. Прикладные блокировки sp_getapplock заменяются на консультативные блокировки (pg_advisory_xact_lock).

Индексы, планировщик и статистика: почему запрос после переноса медленнее

Запрос, который летал на SQL Server, после переноса может выполняться иначе. Это нормально: планировщик другой, и хинтов для принудительного плана нет. Что менять в первую очередь:

• Кластеризованный индекс. В PostgreSQL таблица хранится кучей, порядка строк по ключу нет. Команда CLUSTER упорядочивает таблицу один раз и порядок не поддерживает. Запросы, рассчитанные на физический порядок, перестраивают индексами.
• Покрывающие и фильтруемые индексы. Столбцы INCLUDE поддерживаются с версии 11, фильтруемые индексы заменяются частичными (WHERE в индексе). Покрывающий индекс даёт сканирование только по индексу лишь тогда, когда автоочистка поддерживает карту видимости.
• Индексированные представления. Заменяются материализованными представлениями, которые обновляются командой, а не автоматически. Это решение по расписанию свежести данных.
• Статистика. После загрузки выполняют ANALYZE. Для коррелирующих столбцов есть расширенная статистика (CREATE STATISTICS). Планы читают через EXPLAIN (ANALYZE, BUFFERS), нагрузку находят через pg_stat_statements.

Поэтому после переноса обязателен прогон реального профиля запросов, снятого до переноса, а не только проверка правильности данных.

Как проверить перенос: сверка и чек-лист после загрузки

Перенос верен, когда проверено четыре вещи: данные, результаты, поведение и время. Таблица ниже — минимум для приёмки. Все проверки автоматизируют и запускают при каждой репетиции переноса, а не один раз перед переключением.

Что делаем мы и с чего начать

Мы не обещаем конвертер «в один клик»: переносим схему и данные, читаем и переписываем процедуры, собираем сверку и гоняем нагрузку на тестовом контуре. Исходники и скрипты остаются у вас, работаем в вашем контуре. Проект начинается с обследования: 200 000 ₽ за одну базу и одно-два приложения, 350 000 ₽ за несколько систем, с вычетом из следующего этапа. На выходе — инвентаризация объектов и потребителей, отчёт о совместимости и вилка бюджета. Похожие задачи: миграция на PostgreSQL, переход с Oracle, разбор задачи с названным сроком.