How to work with PostgreSQL

General recommendations

1. Avoid using the CREATE OR REPLACE command for creating triggers, views, and functions. Instead, use the DROP … IF EXISTS construction first (you may also use the CASCADE command if needed), then use CREATE OR REPLACE.
2. Use the “public” scheme instead of the “dbo” scheme.
3. Note that system names are case-sensitive. Remember to wrap the names of tables, columns and other elements in quotes ("") .
4. Instead of the MS SQL's BIT type, use the Postgres’s BOOL type. The WHERE "boolColumn" = true construction is redundant for matching the value of a BOOL type field. Using WHERE "boolColumn" or WHERE NOT "boolColumn” will suffice.
5. Postgres allows using a shortened form of explicit conversion, ::TEXT.
6. String matching is case-sensitive in Postgres. To perform case-insensitive matching, use the iLIKE keyword. Note that this type of matching is significantly slower than the UPPER+LIKE combination. Additionally, the UPPER+LIKE combination has less strict index applicability rules compared to iLIKE.
7. If there is no implicit type casting, you can create one using the CREATE CAST command. More information is available in the PostgreSQL documentation.
8. Postgres lacks a built-in NESTLEVEL function for recursive procedures. Instead, you should use a special parameter in the procedure for storing the current recursion depth.
9. Use the NAME type instead of the SYSNAME type.
10. Avoid using empty INSTEAD triggers and create rules instead, for instance:

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

11. Implicit type casting from the INT type to the BOOL type does not work for the UPDATE command in Postgres even with the corresponding CAST operator. You must cast an INT value to the BOOL type explicitly.
12. The methods for formatting string literals are described in detail in the PostgreSQL documentation:
    • quote_ident,
    • quote_literal,
    • format.
      
13. Use the following construction instead of @@ROWCOUNT:
    

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

    
    
14. Instead of using the following MS SQL construction:
    

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

    
    use the following PostgreSQL construction
    

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

    
    The response field will have a BOOL value.
    

Data type matching.

Table 1. – Table for Creatio, MS SQL and PostgreSQL data type matching

Data type in Creatio object desginer	Data type in MS SQL	Data type in 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

Binding an SQL scenario to a package

If you have SQL scripts (including MS SQL-specific ones) bound to a package, create a new script for PostreSQL that would implement the same features in the PostgreSQL syntax. To do this, add a new PostgreSQL script on the [SQL scenarios] tab (fig. 1).

Fig. 1. – Binding a PostgreSQL scenario to a package

Compare MS SQL and PostgreSQL scripting examples

Views

1. An example SQL script for creating a view and triggers for adding, modifying, and deleting records in the target table.
2. An example SQL script for illustrating the usage of a rule instead of a trigger in PostgreSQL.

Stored procedures and functions

1. An example SQL script for creating a stored procedure that uses cycles, cursors, and temporary tables.
2. An example recursive stored procedure that returns a table and uses PERFORM.
3. An example stored procedure that uses exception handling and custom script execution.
4. An example function.

PostgreSQL script development documentation:

1. Official documentation by the vendor.