Карманный справочник: сравнение синтаксиса MS SQL Server и PostgreSQL

Приветствую, уважаемые хабражители!

Я занимаюсь переводом кода из MS SQL Server в PostgreSQL с начала 2019 года и сегодня  продолжу сравнение этих СУБД.

В прошлой публикации мы рассматривали отличия в быстродействии MS SQL Server и PostgreSQL для «1C».

В Ozon есть решения и на MS SQL Server, и на PostgreSQL: первая используется в логистике и системах внутренних сервисов, вторая — в mission critical-подсистемах, от которых напрямую зависит бизнес компании (склад, корзина, оплата картами, платежи, информация о товарах на сайте и др.).

Периодически появляются задачи перевода решений из огромных монолитных баз из MS SQL Server в PostgreSQL. Поэтому давайте сравним основные конструкции синтаксиса этих СУБД для правильного чтения кода, а также для того, чтобы быстро изменять код из MS SQL Server для PostgreSQL и наоборот.

Начнём с сопоставления типов.

Сопоставление типов

Примечание. Типы CHAR и VARCHAR лучше не использовать. Причины подробно описаны здесь.

Более подробно о типах данных:

1. MS SQL Server

2. PostgreSQL

Теперь перейдём к сопоставлению синтаксиса MS SQL Server и PostgreSQL.

Сопоставление синтаксиса MS SQL Server и PostgreSQL

I. Регистрозависимое обращение к схемам, таблицам (представлениям) и их полям и другим объектам базы данных

В MS SQL Server при обращениях к объектам можно использовать квадратные скобки (они обязательны, только если в названии объекта или его поля присутствуют недопустимые символы):

[schema]
[table]
[view]
[object]
[table].[field]
[view].[field]
[schema].[table]
[schema].[view]
[schema].[object]
[schema].[table].[field]
[schema].[view].[field]

В PostgreSQL для этого используются двойные кавычки (они обязательны, только если в названии объекта присутствуют заглавные буквы или есть недопустимые символы в названии объекта или его поля):

"schema"
"table"
"view"
"table"."field"
"view"."field"
"schema"."table"
"schema"."view"
"schema"."table"."field"
"schema"."view"."field"

II. Выборка заданных N данных

III. Постраничная загрузка данных (скользящее окно)
Задача: извлечь 100 строк начиная с 202-й строки включительно по возрастанию даты рождения:

IV. Выборка первого непустого значения

V. Тернарный оператор IIF

VI. Создание псевдонима

VII. Выражения CASE

VIII. Работа с переменными

Объявление переменной

Присвоение переменной значения

Вывод значения на консоль

IX. Управление выполнением кода

Выполнение скрипта

В MS SQL Server:

declare @_query int;
set @_query=777;
set @query=1+8;
RAISERROR(@_query, 1, 1) WITH NOWAIT; --PRINT @_query;

В PostgreSQL:

Шаблон:

do $$
<объявление переменных> 
begin
<код> 
end;
$$;

Пример (вывод информации):

do $$
declare _query int;
begin
_query:=777;
_query:=1+8;
RAISE NOTICE '%', _query;
end;
$$;

Пример (передача значения клиенту):

do $$
declare _query int;
begin
_query:=777;
_query:=1+8;
PERFORM
set_config('my._query',
_query::text, FALSE);
end;
$$;
SELECT current_setting
('my._query');

Для PostgreSQL:

1. В DBeaver (бобре) нужно нажать CTRL+SHIFT+O при отсутствии окна вывода, а в pgAdmin вывод происходит автоматически.

2. В psql и так всё работает.

Цикл WHILE

Логическое ветвление

Более подробно про управление выполнением кода:

1. Управление выполнением кода в MS SQL Server

2. Управляющие структуры в PostgreSQL

X. Функции для работы со строками

Определение длины строки (количество символов в строке)

Возвращение символа по его коду:

Конкатенация строк

Нахождение позиции вхождения подстроки

Регистронезависимое сравнение и поиск данных

Примечание. В PostgreSQL рекомендуется произвести оптимизацию через создание функционального индекса:

create [concurrently] index idx_lower_<field> 
on <schema>.<table> (lower(<field>));

--После создания concurrently-индекса, 
--его необходимо проверить на наличие битых индексов следующим запросом:
SELECT indexrelid::regclass FROM pg_index where not indisvalid;

--Далее для обновления статистики по нужной таблице 
--необходимо выполнить команду ANALYZE:
ANALYZE <table>;

Более подробно про команду ANALYZE.

Слияние строк по запросу в одну строку по заданному разделителю

В MS SQL Server можно использовать функцию STUFF следующим образом:

STUFF(( SELECT DISTINCT
                 ', ' + CONVERT(varchar, tbl.<поле>)
          FROM
            <схема>.<таблица> tbl
          [WHERE
            <условия>]
          FOR XML PATH(''))
      , 1
      , 1
      , '') AS STUFF_tbl;

Также начиная с версии 2017 доступна функция STRING_AGG.

В PostgreSQL для этого можно использовать функцию string_agg таким образом:

string_agg((SELECT
distinct ', ' ||
cast(tbl.<поле> as VARCHAR) FROM
<схема>.<таблица> tbl,
[WHERE
<условия>]
), 1, 1,
'') AS
string_agg_field;

Более подробно про функции для работы со строками:

1. MS SQL Server

2. PostgreSQL

XI. Функции для работы с датой и временем

Получение текущей даты и времени (локальное время)

Получение текущей даты

Пример преобразования формата даты и времени из строки public_date:

В MS SQL Server:

FORMAT(public_date, 'dd.MM.yyyy HH:mm:ss', 'ru-RU') — предпочтительный способ

convert(varchar(32),convert(datetime,public_date,104),120)

В PostgreSQL:

to_char(to_timestamp(public_date, 'dd.MM.yyyy hh24.mi'), 'yyyy-mm-dd hh24:mi:ss')

Приращение даты/времени

XII. Получение количества строк, затронутых при выполнении последней команды

XIII. Выполнение динамического SQL-кода

XIV. Проверка и приведение типов

Проверка строки на то, что она является числом

В MS SQL Server:

встроенная функция isnumeric(val)

В PostgreSQL:

CREATE OR REPLACE
FUNCTION
dbo.isnumeric(_input varchar(255) DEFAULT NULL::varchar(255))
RETURNS bit
LANGUAGE plpgsql
AS $function$
/*
Проверяет,
является ли входная строка
числом
*/
declare _result bit;
begin
begin
perform
_input::numeric;
_result:=1::bit;
exception
when others THEN
_result:=0::bit;
end;
return
_result;
end;
$function$
;

Безопасное приведение типа

В MS SQL Server:

try_cast(val as <type>)

Примечание. try_cast в MS SQL Server возвращает NULL, если значение невозможно привести к заданному типу, в других случаях — работает как оператор CAST.

В PostgreSQL есть два способа:

1) через обработку ошибок:

declare _result оператор CAST <type>;
... 
BEGIN
_result :=
cast(val as <type>);

exception

when others then

_result :=null;
end;

2) через реализацию функции:

CREATE OR REPLACE
FUNCTION
dbo.try_cast(value character varying, typename CHARACTER varying)
returns text
LANGUAGE plpgsql
AS $function$
declare _sql_command text;
DECLARE _result text;
begin
_result=value;
_sql_command :=
'select
cast('||''''||
value||''''||' as '||
typename||');';
BEGIN
execute _sql_command;
exception
when others then
_result :=null;
end;
return
_result;
end;
$function$
;

Функция в итоге не возвращает преобразованное в заданный тип значение.
Функция на вход принимает текст и возвращает текст.
Если значение невозможно привести к заданному типу, то возвращается NULL.

Пример использования (чтобы было как в MS SQL Server):

cast(dbo.try_cast(val::text, '<type>') as <type>)

XV. DML-команды

Обновление данных

Пример в MS SQL Server:

Обновление поля Name в таблице Production.ScrapReason для тех строк, для которых есть соответствующие записи в таблице Production.WorkOrder по равенству ScrapReasonID и у которых значение ScrappedQty больше 300:

UPDATE
  sr
SET
  sr.Name = 'Name'
OUTPUT
  deleted.*
, inserted.*
FROM Production.ScrapReas sr
  JOIN Production.WorkOrder wo ON (sr.ScrapReasonID = wo.ScrapReasonID)
                                  AND (wo.ScrappedQty > 300);

Ключевое слово OUTPUT позволяет получить данные об обновлении.

Пример в PostgreSQL:

Обновление поля Name в таблице production.scrapreason для тех строк, для которых есть соответствующие записи в таблице production.workorder по равенству scrapreasonid и у которых значение scrappedqty больше 300:

update
production.scrapreason as sr
set sr.Name = 'Name'
from production.workorder as wo
where (sr.scrapreasoid = wo.scrapreasonoid)
and (wo.scrappedqty > 300)
returning *;

Ключевое слово returning позволяет получить данные об обновлении.

Более подробно о команде UPDATE:

1. MS SQL Server

2. PostgreSQL

Удаление данных

Пример в MS SQL Server:

Удаление из таблицы Sales.SalesPersonQuotaHistory тех записей, для которых есть соответствующие записи в таблице Sales.SalesPerson по равенству BusinessEntityID и у которых значение SalesYTD больше 2500000.00:

DELETE FROM
spqh
OUTPUT deleted.*
FROM Sales.SalesPersonQuotaHistory spqh
  INNER JOIN Sales.SalesPerson sp ON (spqh.BusinessEntityID = sp.BusinessEntityID)
WHERE
  (sp.SalesYTD > 2500000.00);

Ключевое слово OUTPUT позволяет получить данные об удалении.

Пример в PostgreSQL:

Удаление из таблицы sales.salespersonquotahistory тех записей, для которых есть соответствующие записи в таблице sales.salesperson по равенству businessentitid и у которых значение salesytd больше 2500000.00:

delete from
sales.salespersonquotahistory AS spqh
using
sales.salesperson AS sp
where
(spqh.businessentityid = sp.businessentitid)
and (sp.salesytd > 2500000.00)
returning *;

Ключевое слово returning позволяет получить данные об удалении.

Более подробно о команде DELETE:

1. MS SQL Server

2. PostgreSQL

Получение изменённых записей

Удаление дубликатов (дублирующих строк):

В MS SQL Server:

with dbl_in_stage as (
select
row_number() over
(partition by
<field_1>, ...,
<field_N> order by
1) as rn
from
<схема>.<таблица>
as stg
)
delete from
dbl_in_stage where
rn > 1;

В PostgreSQL:

with x as (
select a, ctid, row_number() over(partition by a order by ctid) rn
from t
)
delete from t
using x
where t.a = x.a
and t.ctid = x.ctid
and x.rn > 1;

или более сложный вариант:

delete from <схема>.<таблица>
where ctid=any(
  array(select unnest(ctids[2:]) 
        from (
          select array_agg(
            ctid order by string_to_array(
              regexp_replace(ctid::text, E'\\(|\\)','','g'),',')::bigint[]) ctids 
          FROM <схема>.<таблица> as T group by T::text) as T)::tid[]);

Примечание. Оптимальный вариант — внести в таблицу уникальный ключ, так как работа с метаданными увеличивает нагрузку на систему.

При наличии уникального ключа удалять дубликаты в PostgreSQL можно следующим образом:

delete from <схема>.<таблица> where <уникальный ключ> in
(select <уникальный ключ>
from (
select *, row_number() over (partition by <field_1>, ..., <field_N> order by 1) as rn
from <схема>.<таблица>
) as tbl where rn > 1);

XVI. DDL-команды для работы с таблицами

Удаление таблицы с предварительной проверкой

В MS SQL Server:

Для основной таблицы:

DROP TABLE IF EXISTS <schema>.<table>;

Для локальной временной таблицы:

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#<table>%')
BEGIN
DROP TABLE #<table>;
END;

Для глобальной временной таблицы:

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '##<table>%')
BEGIN
DROP TABLE ##<table>;
END;

Здесь:

1. #<table> — локальная временная таблица, которая видна только в текущей сессии

2. ##<table> — глобальная временная таблица, которая видна всем пока она существует

Все временные таблицы живут, либо пока активна сессия, во время которой они были созданы, либо пока их явно не удалят.

В PostgreSQL:

Для основной таблицы:

drop table if exists
<schema>.<table>;

Для временной таблицы:

drop table if exists <table>;

Более детально про удаление таблиц:

1. MS SQL Server

2. PostgreSQL

Создание таблицы через выборку

В MS SQL Server:

Для основной таблицы:

select ...
into <table>
from …

Для временной таблицы:

select ...
into #<table>
from …

В PostgreSQL:

Для основной таблицы:

create table <table> as
select ...

Для временной таблицы:

create temp table <table> as
select …

Более детально про создание таблиц через выборку:

1. MS SQL Server

2. PostgreSQL

Создание/изменение и удаление значения по умолчанию для колонки таблицы

В MS SQL Server:

Добавление:

ALTER TABLE
<схема>.<таблица>
ADD CONSTRAINT
<название_правила>
DEFAULT
<значение_по_умолчанию> FOR <поле>;

Выборка всех значений по умолчанию:

SELECT SCHEMA_NAME(t.[schema_id]) AS sch
, t.name AS tbl
, col.name AS colname
, dc.definition AS def
FROM sys.default_constraints dc
  INNER JOIN sys.columns col ON dc.parent_object_id = col.[object_id]
  INNER JOIN sys.tables t ON t.[object_id] = col.[object_id];

Удаление:

DROP DEFAULT IF EXISTS <название_правила>;

Изменение происходит через удаление и добавление.

В PostgreSQL:

Создание и изменение:

alter table
<схема>.<таблица>
alter column <поле>
set default
<значение_по_умолчанию>;

Выборка всех значений по умолчанию:

select
col.table_schema,
col.table_name,
col.column_name,
col.column_default
from
information_schema.columns as col;

Удаление:

alter table <схема>.<таблица>
alter column <поле> drop default;

Изменение типа колонки таблицы

Перенос автоинкрементных полей

В MS SQL Server делаем запрос вида:

SELECT
  'do $$
declare
start_with_val
bigint;
declare
sql_statement
varchar;
begin
start_with_val :=
coalesce((select
max(' + c.[name] + ') from
' + s.[name] + '.' + o.[name] + '),0)+1;
sql_statement :=
''alter table
' + s.[name] + '.' + o.[name] + ' alter ' + c.[name] + '
add generated by
default as identity
(start with ''
||
cast(start_with_val
as varchar)||'');'';
execute
sql_statement;
end;
$$;' AS plsql_statement
--select distinct s.name
FROM
  sys.all_columns c
  INNER JOIN sys.all_objects o ON o.[object_id] = c.[object_id]
  INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]
WHERE
  is_identity <> 0
  AND SCHEMA_NAME(o.[schema_id]) <> 'sys'
  AND o.[type] = 'U';

Пример:

do $$ 
declare  start_with_val bigint; 
declare  sql_statement varchar; 
begin
start_with_val := coalesce((select  max(ID) from  dbo.ExchangeQueue),0)+1;  
sql_statement := 'alter table  dbo.ExchangeQueue alter ID  add generated by  default as identity  (start with '  ||  cast(start_with_val  as varchar)||');';  
EXECUTE sql_statement;  
end; 
$$;

Полученные скрипты применяем на стороне PostgreSQL.

Создание автоинкрементных полей

В MS SQL Server:

ALTER TABLE [схема].[таблица]
ADD
  <IDENTITY-поле> bigint IDENTITY(1, 1) NOT NULL;

В PostgreSQL:

do $$  
DECLARE start_with_val bigint;  
DECLARE sql_statement varchar;  
BEGIN  start_with_val := coalesce((select  max(<IDENTITY-поле>) from  <схема>.<таблица>),0)+1;  
sql_statement := 'alter table <схема>.<таблица> alter <IDENTITY-поле> add generated by  default as identity (start with ' || cast(start_with_val  as varchar)||');';  
EXECUTE sql_statement; 
END; 
$$;

XVII. Создание и изменение представления

XVIII. Построчная обработка строк в наборе

В MS SQL Server:

--объявление переменных @field_1, ...@field_N

DECLARE <курсор>
CURSOR LOCAL FOR
<SELECT>;

OPEN <курсор>;

FETCH NEXT FROM
<курсор> INTO
@field_1, ...@field_N;

WHILE
(@@FETCH_STATUS = 0)
BEGIN
--оперируем значениями переменных @field_1, ...@field_N
...
FETCH NEXT
FROM <курсор> INTO @field_1, ...@field_N;
END

CLOSE <курсор>;
DEALLOCATE <курсор>;

В PostgreSQL:

do $$
declare _val
record;
begin

drop table if
exists _tmp_tbl;

create temp
table _tmp_tbl as
<select>
for _val in
(select field_1, ..., field_n from_tmp_tbl)
loop
--можно обратиться к любому выбранному ранее полю через _val.<поле>. Например, _val.<field_1>
end loop;
end
$$

XIX. Системные информационные функции безопасности

Текущий пользователь

В MS SQL Server используется функция CURRENT_USER().

В PostgreSQL:

1. session_user — под каким пользователем открыта сессия

2. current_user (или просто user) — под каким контекстом (ролью) идёт выполнение (session_user переключается для выполнения — здесь важно, под каким правом делается переключение)

Получение имени экземпляра и IP-адреса сервера СУБД

В MS SQL Server:

Получить информацию об IP-адресе сервера СУБД:

SELECT
  CONNECTIONPROPERTY('
net_transport') AS net_transport
, CONNECTIONPROPERTY('
protocol_type') AS protocol_type
, CONNECTIONPROPERTY('
auth_scheme') AS auth_scheme
, CONNECTIONPROPERTY('
local_net_address') AS local_net_address
, CONNECTIONPROPERTY('
local_tcp_port') AS local_tcp_port
, CONNECTIONPROPERTY('
client_net_address') AS client_net_address;

Получить название экземпляра СУБД:

SELECT @@SERVERNAME;

В PostgreSQL:

Получить IP-адрес сервера СУБД:

do $$
declare
title varchar(100) :=host(inet_server_addr());
begin
raise notice '%',
title;
end; $$;

Получение названия экземпляра СУБД пока не реализовано.

Более подробно про системные информационные функции безопасности:

1. MS SQL Server

2. PostgreSQL

XX. Определение и вызов хранимой процедуры

Определение хранимой процедуры

В MS SQL Server:

CREATE OR ALTER PROCEDURE
[схема].[назание_процедуры]
<переменная_1> <тип_1>[=<значение_по_умолчанию_1>],
...
AS
BEGIN
...
END

В PostgreSQL:

CREATE OR REPLACE PROCEDURE
<схема>.<название_процедуры>
(
[INOUT] <переменная_1> <тип_1>[=<значение_по_умолчанию1>],
...
)
LANGUAGE plpgsql
AS $body$
[<Объявление переменных>]
BEGIN
...
END;
$body$
;

Вызов хранимой процедуры

В MS SQL Server:

EXEC <схема>.<процедура> 
<переменная_1>=<значение_1>, ..., <переменная_OUTPUT> OUT[PUT];

В PostgreSQL:

call <схема>.<процедура> (
  <переменная_1>=<значение_1>, ..., <переменная_OUTPUT>);

XXI. Создание скалярной функции

В MS SQL Server:

CREATE OR ALTER
FUNCTION [схема].[название_функции]
(<параметр_1> <тип_1>[=<значение_по_умолчанию_1>], ...)
RETURNS <возвращаемый_тип> AS
BEGIN
... RETURN ...
END

В PostgreSQL:

CREATE OR REPLACE FUNCTION
<схема>.<название_функции>
(<параметр_1> <тип_1>[=<значение_по_умолчанию_1>], ...)
RETURNS <возвращаемый_тип>
LANGUAGE plpgsql
AS $body$
[<Объявление переменных>]
begin
... return (
select ...
);
end;
$body$
;

XXII. Передача табличного значения (вывод таблицы)

В MS SQL Server:

CREATE OR ALTER
PROCEDURE
[схема].[название_хранимой_процедуры]
<параметр_1> <тип_1>,
...,
<параметр_N> <тип_N>
AS
BEGIN
...
SELECT ...
END

В PostgreSQL:

create or replace function
<схема>.<название_функции>
(<параметр_1> <тип_1>, ..., <параметр_N> <тип_N>)
return table (<поле_1> <тип_1>, ..., <поле_N> <тип_N>)
language 'plpgsql'
as $body$
[<Объявление переменных>]
begin
return query (select ....);
end;
$body$;

XXIII. DML-триггеры

Пример в MS SQL Server:

CREATE TRIGGER [info].[tr_isupoll_question_text_last_update_trigger]
ON [info].[isupoll_question_text]
FOR UPDATE
AS
UPDATE
  info.isupoll_question_text
SET
  last_update_date = GETDATE()
, last_update_user = SUSER_NAME()
FROM
  info.isupoll_question_text ds
  INNER JOIN INSERTED i ON 
  ds.isupoll_question_text_id = i.isupoll_question_text_id;

Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы  info.isupoll_question_text после обновления данных, который для обновляемых строк проставляет текущие дату, время и пользователя соответственно.

DROP TRIGGER IF EXISTS [tr_isupoll_question_text_last_update_trigger] 
on [info].[isupoll_question_text];

Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text

Пример в PostgreSQL:

CREATE OR REPLACE
FUNCTION dbo.update_mod()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
new.last_update_date=now();

new.last_update_user=session_user;

return new;
end;
$function$
;

Здесь создаётся функция dbo.update_mod(), которая заполняет два поля текущими датой, временем и пользователем соответственно.

create trigger tr_isupoll_question_text_last_update_trigger before update 
on info.isupoll_question_text for each row execute function dbo.update_mod();

Здесь создаётся триггер tr_isupoll_question_text_last_update_trigger для таблицы  info.isupoll_question_text до обновления данных, который для каждой строки вызывает выполнение функции dbo.update_mod().

drop trigger if exists tr_isupoll_question_text_last_update_trigger 
on info.isupoll_question_text;

Здесь удаляется триггер tr_isupoll_question_text_last_update_trigger для таблицы info.isupoll_question_text.

Важно! В триггере используйте ключевое слово before, когда хотите нашкодничать в той же таблице, для которой создаётся триггер, и after — для логирования в другую таблицу.

Более подробно про DML-триггеры:

1. MS SQL Server

2. PostgreSQL

И в качестве бонуса кратко рассмотрим сопоставление основных системных представлений и приведём ссылки для мониторинга.

Немного о сопоставлении системных представлений и мониторинге

Сопоставление системных представлений

Системные представления PostgreSQL:
Сборщик статистики

^{Изображение взято с Postgres 13 Observability Updates.}

Системные представления MS SQL Server:

1. SQL Server 2012 System Views Map

2. Системные динамические административные представления

Мониторинг работы СУБД

• MS SQL Server

• PostgreSQL

Заключение

Мы рассмотрели сопоставление типов и основные конструкции синтаксиса MS SQL Server и PostgreSQL, что позволит быстрее адаптировать решения из одной СУБД под другую.

Также мы пробежались по сопоставлению системных представлений и ссылкам на документацию, в том числе о мониторинге, что позволит анализировать производительность реализованного или перенесённого кода.

Благодарность

Спасибо коллегам за ценные комментарии:

• @Kilor

• @vadv

• @evilbloodydemon

• @unfilled

• @XEK

• @Nikkolayy

Источники

• Миграция системы документационного управления «Приоритет» с MS SQL Server на PostgreSQL

• MS SQL Server

• PostgreSQL