Что нужно учесть при миграции с Mysql в Postgres

Многие сейчас работают с базами через обёртки ORM/ActiveRecord, которые нивелируют большинство различий между базами, и на первый взгляд может показаться, что достаточно поправить пару написанных ручками запросов, подключить новую базу и поправить драйвер в конфиге библиотеки. Но вряд ли вам действительно легко удастся провернуть такой финт, чтобы ничего не отвалилось. Пристальное внимание следует уделить запросам “ручками”

Если в mysql мы можем использовать для строковых данных как одинарные, так и двойные кавычки, то postgres для строк требует только одинарные, двойные используются для системных обозначений — названий полей, таблиц, и т.п. В mysql для полей и таблиц используются `обратные кавычки`

Mysql поддерживает не стандартные комментарии, начинающиеся с #

Postgres такие не понимает и поддерживает только начинающиеся с двух дефисов. (Это стандартные комментарии по ANSI и mysql их тоже умеет)

mysql регистронезависим (по крайней мере под linux) для названий столбцов, ииндексов, процедур и событий это значит что запросы

— вернут одинаковые данные (Но! имена полей будут такими, как вы указали в запросе!)

Но имена баз и таблиц чувствительны к регистру.

Postgres такого не допускает. Вы можете иметь 2 разных поля с названиями userId и userid. Если вы хотите использовать поле, содержащее заглавные буквы, то его обязательно нужно заключать в кавычки.

Так же стоит учесть, что оператор LIKE в Mysql регистронезависим. В постгрес для такого поиска следует использовать оператор ILIKE, а LIKE учитывает регистр

  • В mysql операторы && эквивалентен AND, а так же || эквивалентен OR, в postgres оператор || используется для конкатенации строк,
  • В mysql оператор ^ используется как битовый XOR, в postgres для этого используется #, а ^ для возведения в степень.
  • По умолчанию не умеет SOUNDS LIKE, но при необходимости можно воткнуть расширение fuzzystrmatch

Хотя различные параметры для ограничений sql_mode в mysql присутствовали давно, полноценной “строгостью” к введенным данным, причём по умолчанию, он обзавелся только к версии 5.7 Поэтому, если вы использовали более ранние версии mysql, или новые, но с отключенными strict_mode, то переезд в postgres может преподнести вам много боли.

В первую очередь — при попытке вставить данные большего размера, чем позволяет поле базы.

И во вторую — запросы c группировкой. В mysql без параметра ‘only_full_group_by’ были допустимы запросы вида

select *, count(id) from my_table group by some_field;

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

Например для таблицы

Запрос может иметь вид:

Полностью можно ознакомиться в официальной документации: [MySql] [Postgres] Я отмечу здесь только некоторые нюансы

  • тип bool, который по факту в MySql является алиасом к tinyint(1) и умеет принимать значения 0/1. В postgres boolean — отдельный тип, который принимает значения (false|true)(‘yes’|‘no’) (‘f’|‘t’) (‘y’|‘n’) (‘0’|‘1’) — но при этом будет ругаться на 0|1 переданный без кавычек как на значение типа integer. И это запросто может стать закавыкой при миграции из mysql; Для postgres эти значения придётся либо взять в кавычки, либо явно обозначить как 0::bool, 1::bool
  • аналога tinyint в postgres нет. Минимальный размер — int2 (smallint) со значениями в диапазоне -32768 to +32767
  • нет прямого аналога unsigned, если нужна проверка на беззнаковость, то добавьте соответствующую проверку
  • Полям с датами следует уделить особое внимание. Значительные различия имеют функции работы с датой и временем. [mysql] [postgres]
  • Часто используемая в mysql фишка для обновления даты при изменении строки updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT TIMESTAMPв postgres не поддерживается. Для такого функционала придётся написать триггер (код легко гуглится). Или разруливать на уровне приложения. (Просто DEFAULT CURRENT_TIMESTAMP сработает)
  • JSON: идентичен при простых операциях вставки значений, но значительные отличия в синтаксисе доступа и совершенно разные функции. Так же следует учесть что в postgres есть более эффективный аналог jsonb (чем отличается?)
  • со spatial/geo -типами в mysql не работала, поэтому ничего отметить не могу, но подозреваю что различий предостаточно
  • В postgres отсутствует аналог “SET” . Из вариантов — ближе всего — массив с дополнительными проверками
  • Альтернативой Binary типу в postgres служит Bytea, но напрямую данные из одного в другой сконвертировать не получится. Решения гуглятся
  • Enum — postgres умеет, но создается немного по-другому: сначала создаётся тип с нужными значениями, затем при создании таблицы можно задать полю созданный тип
  • В отличие от mysql, postgres поддерживает транзакции для создания/изменения таблиц и столбцов, а не только для данных.
  • postgres не умеет оперировать порядком столбцов BEFORE/AFTER конструкции не поддерживаются, только пересоздавать структуру.
  • есть особенности для изменения NULL/NOT NULL и значения по умолчанию. Если в mysql мы можем изменить всё одним запросом

То в postgres аналогичная операция будет выглядеть так

  • Mysql не поддерживает значения по умолчанию для типов BLOB,TEXT, JSON, GEOMETRY. В postgres c этим проблем нет.
  • Postgres младше 12 версии не умеет в вычислимые поля (generated columns) — только через триггеры делать
  • Для 12-й версии аналогичная операция будет с таким запросом

В отличие от mysql, postgres может иметь несколько последовательстей (sequences) на одну таблицу, информация о которых хранится отдельно в служебной нформации

  • Возврат вставленного ID: можно получить сразу в запросе вставки данных
  • Функции currval и nextval для последнего вставленного и следующего id.
  • Изменить следующее значение id

Может возникнуть проблема при использовании операции truncate, так как по умолчанию счетчик последовательности остаётся прежним. Чтобы исправить это — нужно вызавать:

Ну в общем пока вроде всё, что вспомнила. Про хранимки/триггеры я даже особо не упоминаю — само собой, если используются, то придётся переписать. Если у вас тоже есть аналогичный опыт — делитесь.

#php,#yii,#laravel,#javascript, #python,#linux,#archlinux,#vue

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store