Особенности работы с PostgreSQL

Общие рекомендации

1. Не рекомендуется использовать для создания триггеров, представлений, функций команду CREATE OR REPLACE. Вместо этого используйте конструкцию DROP … IF EXISTS (при необходимости можно использовать команду CASCADE), а затем CREATE OR REPLACE.
2. Вместо схемы "dbo" используйте "public".
3. Учитывайте регистрозависимость системных имен, используйте кавычки ("") для имен таблиц, колонок и т.д..
4. Вместо типа BIT в MS SQL используйте в Postgres тип BOOL. Для проверки значения поля типа BOOL необязательно использовать конструкцию WHERE "boolColumn" = true, достаточно WHERE "boolColumn" или WHERE NOT "boolColumn".
5. В Postgres можно использовать сокращенный вид явного преобразования ::TEXT.
6. В Postgres при сравнении строк учитывается регистр. Для выполнения регистронезависимого сравнения можно использовать ключевое слово iLIKE. Однако учитывайте, что сравнение при этом значительно более медленное, чем при использовании комбинации UPPER+LIKE. Кроме того у комбинации UPPER+LIKE менее строгие правила применимости индексов, чем у iLIKE.
7. Если нет какого-либо неявного приведения типов, то его можно создать с помощью команды CREATE CAST. Подробнее об этом читайте в документации PostgreSQL.
8. В Postgres нет встроенной функции NESTLEVEL в рекурсивных процедурах. Для хранения текущего уровня рекурсии следует создавать специальный параметр процедуры.
9. Вместо типа SYSNAME используйте тип NAME.
10. Вместо пустых INSTEAD-триггеров создавайте правила, например:

    CREATE RULE RU_VwAdministrativeObjects AS
    ON UPDATE TO "VwAdministrativeObjects"
    DO INSTEAD NOTHING;
    

11. При выполнении команды UPDATE в Postgres не работает неявное преобразование типа INT в тип BOOL, даже при наличии соответствующего оператора CAST. Следует явно привести INT -значение к типу BOOL.
12. Способы форматирования строковых литералов подробно описаны на сайте документации PostgreSQL:
    • quote_ident;
    • quote_literal;
    • format.
      
13. Вместо @@ROWCOUNT используйте следующую конструкцию:
    

    DECLARE rowsCount BIGINT = 0;
    GET DIAGNOSTICS rowsCount = row_count;
    

    
    
14. Вместо конструкции в MS SQL
    

    (CASE WHEN EXISTS (
      SELECT 1
      FROM [SysSSPEntitySchemaAccessList]
      WHERE [SysSSPEntitySchemaAccessList].[EntitySchemaUId] = [BaseSchemas].[UId]
      )
    THEN 1 ELSE 0 END) AS [IsInSSPEntitySchemaAccessList]
    

    
    в PostgreSql следует использовать конструкцию
    

    EXISTS (
      SELECT 1
      FROM "SysSSPEntitySchemaAccessList"
      WHERE "EntitySchemaUId" = BaseSchema."UId"
     ) "IsInSSPEntitySchemaAccessList"
    

    
    Полученное в результате запроса поле будет иметь тип BOOL.
    

Соответствие типов данных

Табл. 1. — Таблица соответствия типов данных Creatio, MS SQL и PostgreSQL

Тип данных в дизайнере объекта Creatio	Тип данных в MS SQL	Тип данных в PostgreSQL
BLOB	VARBINARY	BYTEA
Boolean	BIT	BOOLEAN
Color	NVARCHAR	CHARACTER VARYING
CRC	NVARCHAR	CHARACTER VARYING
Currency	DECIMAL	NUMERIC
Date	DATE	DATE
Date/Time	DATETIME2	TIMESTAMP WITHOUT TIME ZONE
Decimal (0.00000001)	DECIMAL	NUMERIC
Decimal (0.0001)	DECIMAL	NUMERIC
Decimal (0.001)	DECIMAL	NUMERIC
Decimal (0.01)	DECIMAL	NUMERIC
Decimal (0.1)	DECIMAL	NUMERIC
Encrypted string	NVARCHAR	CHARACTER VARYING
File	VARBINARY	BYTEA
Image	VARBINARY	BYTEA
Image Link	UNIQUEIDENTIFIER	UUID
Integer	INTEGER	INTEGER
Lookup	UNIQUEIDENTIFIER	UUID
Text (250 characters)	NVARCHAR(250)	CHARACTER VARYING
Text (50 characters)	NVARCHAR(50)	CHARACTER VARYING
Text (500 characters)	NVARCHAR(500)	CHARACTER VARYING
Time	TIME	TIME WITHOUT TIME ZONE
Unique identifier	UNIQUEIDENTIFIER	UUID
Unlimited length text	NVARCHAR(MAX)	TEXT

Привязка SQL-сценария к пакету

Если в пакете привязаны SQL-сценарии, например, для MS SQL, то для работы с Postgres создайте скрипт, который выполняет те же функции, но использует синтаксис PostgreSQL. Для этого на вкладке [SQL-сценарии] добавьте скрипт с типом СУБД PostgreSql (рис. 1).

Рис. 1. — Привязка SQL-сценария PostgreSQL к пакету

Примеры сравнительных скриптов для MS SQL и PostgreSQL

Представления

1. Пример SQL-скрипта, создающего представление и тригеры для добавления, изменения и удаления записей из целевой таблицы.
2. Пример SQL-скрипта, иллюстрирующего использование правила вместо триггера в PostgreSQL.

Хранимые процедуры и функции

1. Пример SQL-скрипта, создающего хранимую процедуру, использующую циклы, курсоры и временные таблицы.
2. Пример рекурсивной хранимой процедуры, которая возвращает таблицу и в которой используется PERFORM.
3. Пример хранимой процедуры, в которой используется обработка исключений и выполнение кастомного скрипта.
4. Пример функции.

Документация по разработке скриптов PostgreSql:

1. Официальная документация вендора СУБД.
2. Курс от Postgres Pro “Разработка серверной части приложений PostgreSQL 9.6. Базовый курс”.