Проблемы при переходе с MS SQL на PostgreSQL. Типы данных
Исходя из того, что предыдущую статью не заминусовали и даже не сильно критиковали, попробую продолжить серию и поделиться с проблемами некоторых различий типов данных в MS SQL и PostgreSQL.
Деньги
Извиняюсь за дословный перевод. Начну я с типа money. Этот тип есть в MS SQL и PostgreSQL и вроде бы даже имеет одинаковую точность. Вот только в MS SQL эта точность фиксирована и всегда четыре знака после десятичной точки. В PostgreSQL точность значений типа money зависит от lc_monetary, по умолчанию наследуемого из системной локали. В подавляющем большинстве случаев которые встречал — это все же два знака после десятичной точки, а вовсе не четыре, как в MS SQL.
Если в БД MS SQL в полях типа money хранятся значения не только наиболее популярных валют, требующих только два знака после десятичной точки, то использование типа money в PostgreSQL становится проблематичным.
Даже если в БД MS SQL стабильно используются только два знака после запятой, некоторые вычисления в SQL запросах могут в промежуточных значениях использовать все четыре знака после запятой, что приведет к различию в результатах.
Исходя из вышеперечисленного, я не рекомендую использовать тип money в PostgreSQL, даже если он был использован в MS SQL. Надежней конвертировать его в тип decimal (19,4), пусть даже ценой весьма незначительной потери производительности.
Даты и время
Не буду заострять внимание на том, что в MS SQL нет прямого аналога типу timestamp with time zone. Тип datetime в MS SQL имеет прямое соответствие timestamp without time zone. Тонкости использования этих типов в PostgreSQL обсуждались на Хабре, например, тут.
А вот те неприятности, которые может доставить наличие в MS SQL типа datetime2 стоит рассмотреть. Дело в том, что datetime2 поддерживает дискретность до 100 наносекунд (7 знаков после десятичной точки в секундах), тогда как timestamp в PostgreSQL — лишь 1 микросекунду (6 знаков после десятичной точки в секундах). Потеря одного знака точности может привести к изменению порядка следования записей при сортировке.
Если вдруг точность до 100 наносекунд жизненно необходима, то в PostgreSQL ее потребуется поддерживать отдельным полем типа tinyint. Такого в своей практике не встречал. Но сталкивался с необходимостью модификации значений типа datetime2 таким образом, чтобы они стали различаться в пределах шести знаков после десятичной точки для сохранения порядка сортировки. Потеря точности в микросекундах или даже в десятках микросекунд, обычно, не критична. А в возможность последовательной вставки более миллиона записей в секунду в одну таблицу MS SQL верится с трудом.
Если для миграции данных или в период опытной эксплуатации используется TDS_FDW, то тут даты и время приносят дополнительные проблемы.
Начнем с уже рассмотренного выше datetime2. В дальнейшем будем считать, что у нас уже создан FOREIGN SERVER
CREATE SERVER IF NOT EXISTS tds_test
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername 'ms-sql-server.domain.name',
tds_version '7.4', msg_handler 'notice');
CREATE USER MAPPING IF NOT EXISTS FOR you_pgsql_username
SERVER tds_test OPTIONS (username 'you_mssql_username',
password 'you_mssql_password');Создадим в MS SQL таблицу, не забывая о том, что у аккаунта you_mssql_username, который мы выше использовали для доступа к MS SQL серверу, должны быть права на просмотр плана выполнения и на чтение из создаваемой таблицы.
USE test_db
GRANT SHOWPLAN TO you_mssql_username
DROP TABLE IF EXISTS tmp_dates
CREATE TABLE tmp_dates (
ID int NOT NULL,
Val1 datetime2 NULL,
Val2 datetime NULL)
INSERT tmp_dates (ID, Val1, Val2)
VALUES (1, '2000-01-01', '2000-01-01'),
(2, '2000-01-01 01:01:01.1234567', '2000-01-01 01:01:01.123'),
(3, NULL, NULL)
GRANT SELECT ON tmp_dates TO you_mssql_usernameСоздаем соответствующую таблицу в PostgreSQL
DROP FOREIGN TABLE IF EXISTS tds_test_tmp_dates;
CREATE FOREIGN TABLE IF NOT EXISTS tds_test_tmp_dates (
ID integer NOT NULL,
Val1 timestamp NULL,
Val2 timestamp NULL
) SERVER tds_test OPTIONS ( table_name 'test_db.dbo.tmp_dates',
row_estimate_method 'showplan_all' );При попытке обратиться к этой таблице получаем:
SELECT * FROM tds_test_tmp_dates;
SQL Error [22007]: ERROR: invalid input syntax for type timestamp:
"Jan 1 2000 12:00:00:0000000AM"Проблема именно в двоеточии после десятичной точки для типа datetime2. Пересоздаем внешнюю таблицу и проверяем с убранным двоеточием:
DROP FOREIGN TABLE IF EXISTS tds_test_tmp_dates;
CREATE FOREIGN TABLE IF NOT EXISTS tds_test_tmp_dates (
ID integer NOT NULL,
Val1 varchar NULL,
Val2 timestamp NULL
) SERVER tds_test OPTIONS ( table_name 'test_db.dbo.tmp_dates',
row_estimate_method 'showplan_all' );
DROP TABLE IF EXISTS tmp_dates;
CREATE TEMP TABLE tmp_dates (
ID integer NOT NULL,
Val1 varchar NULL,
Val2 timestamp NULL );
INSERT INTO tmp_dates (ID, Val1, Val2)
SELECT ID,
regexp_replace(Val1,'(:..:..):','\1.')::timestamp,
Val2
FROM tds_test_tmp_dates;
SELECT * FROM tmp_dates; /*
1 2000-01-01 00:00:00 2000-01-01 00:00:00
2 2000-01-01 01:01:01.123457 2000-01-01 01:01:01.123
3 NULL NULL */Очень похожая проблема возникает даже просто с типом date. Опять создаем таблицу на MS SQL
DROP TABLE IF EXISTS tmp_dates
CREATE TABLE tmp_dates (
ID int NOT NULL,
Val date NULL)
INSERT tmp_dates (ID, Val)
VALUES (1, '2000-01-01'),
(2, NULL)
GRANT SELECT ON tmp_dates TO you_mssql_username И внешнюю таблицу на PostgreSQL
DROP FOREIGN TABLE IF EXISTS tds_test_tmp_dates;
CREATE FOREIGN TABLE IF NOT EXISTS tds_test_tmp_dates (
ID integer NOT NULL,
Val date NULL
) SERVER tds_test OPTIONS ( table_name 'test_db.dbo.tmp_dates',
row_estimate_method 'showplan_all' );
SELECT * FROM tds_test_tmp_dates;
SQL Error [22007]: ERROR: invalid input syntax for type date:
"Jan 1 2000 12:00:00:AM"И снова тоже самое двоеточие. Решение совершенно аналогичное.
DROP FOREIGN TABLE IF EXISTS tds_test_tmp_dates;
CREATE FOREIGN TABLE IF NOT EXISTS tds_test_tmp_dates (
ID integer NOT NULL,
Val varchar NULL
) SERVER tds_test OPTIONS ( table_name 'test_db.dbo.tmp_dates',
row_estimate_method 'showplan_all' );
DROP TABLE IF EXISTS tmp_dates;
CREATE TEMP TABLE tmp_dates (
ID integer NOT NULL,
Val date NULL );
INSERT INTO tmp_dates (ID, Val)
SELECT ID, regexp_replace(Val,' ..:..:..:..','')::date
FROM tds_test_tmp_dates;
SELECT * FROM tmp_dates; /*
1 2000-01-01
2 NULL */