Проблемы при переходе с 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; /*
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 */