Проблемы при переходе с 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 ее потребуется поддерживать отдельным полем типа smallint. Такого в своей практике не встречал. Но сталкивался с необходимостью модификации значений типа 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; /* 12000-01-0100:00:002000-01-01 00:00:00 22000-01-0101:01:01.1234572000-01-01 01:01:01.123 3NULLNULL */

Очень похожая проблема возникает даже просто с типом 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; /* 12000-01-01 2NULL */