Creatio development guide
PDF
This documentation is valid for Creatio version 7.15.0. We recommend using the newest version of Creatio documentation.

How to work with PostgreSQL

Glossary Item Box

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:
  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

NOTE

To view the examples, download the files by clicking the links below.

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.
© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?