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

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

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

-- Этот комментарий поймут и Postgres и MySql
# А на такой Postgres будет ругаться!

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

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

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

select name, myText from my_table;
select Name, mytext from my_table;`

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

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

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

select name, myText from my_table; -- Не отработает, myText будет читаться как mytext
select name, "myText" from my_table; -- Правильно

Так же стоит учесть, что оператор 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 все поля, которые не участвуют в группировке мы должны задать явно с указанием аггрегирующей функции.

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

create table users
(
id bigserial primary key,
name varchar(255) not null,
age smallint default 0 null,
department varchar(20) not null
);

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

select count(id), avg(age), array_agg(name), department from users group by department;

Полностью можно ознакомиться в официальной документации: [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, если нужна проверка на беззнаковость, то добавьте соответствующую проверку
CREATE TABLE users (
id serial primary key,
name varchar(255),
age smallint CHECK(age > 0)
);
  • Полям с датами следует уделить особое внимание. Значительные различия имеют функции работы с датой и временем. [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 умеет, но создается немного по-другому: сначала создаётся тип с нужными значениями, затем при создании таблицы можно задать полю созданный тип
CREATE TYPE job_state AS ENUM ('pending', 'active', 'fail', 'success');CREATE TABLE jobs (
id bigserial,
name varchar(255),
payload jsonb,
state job_state
);
INSERT INTO jobs (name, payload, state)
VALUES ('do it', {"foo": "bar"}, 'pending')
  • В отличие от mysql, postgres поддерживает транзакции для создания/изменения таблиц и столбцов, а не только для данных.
  • postgres не умеет оперировать порядком столбцов BEFORE/AFTER конструкции не поддерживаются, только пересоздавать структуру.
  • есть особенности для изменения NULL/NOT NULL и значения по умолчанию. Если в mysql мы можем изменить всё одним запросом
ALTER TABLE jobs MODIFY name varchar(100) NULL DEFAULT 'cron_job' COMMENT 'job name';

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

ALTER TABLE jobs ALTER COLUMN name TYPE varchar(100);
ALTER TABLE jobs ALTER COLUMN name DROP NOT NULL;
ALTER TABLE jobs ALTER COLUMN name SET DEFAULT 'cron_jobs';
COMMENT ON COLUMN "jobs"."name" IS 'job name';
  • Mysql не поддерживает значения по умолчанию для типов BLOB,TEXT, JSON, GEOMETRY. В postgres c этим проблем нет.
  • Postgres младше 12 версии не умеет в вычислимые поля (generated columns) — только через триггеры делать
ALTER TABLE my_box ADD COLUMN volume integer as (width * height * len);
  • Для 12-й версии аналогичная операция будет с таким запросом
ALTER TABLE my_box ADD COLUMN volume integer GENERATED ALWAYS AS (width * height * len) STORED;

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

  • Возврат вставленного ID: можно получить сразу в запросе вставки данных
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
  • Функции currval и nextval для последнего вставленного и следующего id.
SELECT currval(pg_get_serial_sequence('users', 'id'));
  • Изменить следующее значение id
SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT MAX(id) FROM users) + 1000);

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

TRUNCATE TABLE users RESTART IDENTITY;

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

#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