How to work with PostgreSQL
Glossary Item Box
General recommendations
- 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.
- Use the “public” scheme instead of the “dbo” scheme.
- Note that system names are case-sensitive. Remember to wrap the names of tables, columns and other elements in quotes ("") .
- 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.
- Postgres allows using a shortened form of explicit conversion, ::TEXT.
- 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.
- If there is no implicit type casting, you can create one using the CREATE CAST command. More information is available in the PostgreSQL documentation.
- 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.
- Use the NAME type instead of the SYSNAME type.
- Avoid using empty INSTEAD triggers and create rules instead, for instance:
CREATE RULE US_VwAdministrativeObjects AS ON UPDATE TO "VwAdministrativeObjects" DO INSTEAD NOTHING;
- 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.
- The methods for formatting string literals are described in detail in the PostgreSQL documentation:
- Use the following construction instead of @@ROWCOUNT:
DECLARE rowsCount BIGINT = 0; GET DIAGNOSTICS rowsCount = row_count;
- 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
- An example SQL script for creating a view and triggers for adding, modifying, and deleting records in the target table.
- An example SQL script for illustrating the usage of a rule instead of a trigger in PostgreSQL.
Stored procedures and functions
- An example SQL script for creating a stored procedure that uses cycles, cursors, and temporary tables.
- An example recursive stored procedure that returns a table and uses PERFORM.
- An example stored procedure that uses exception handling and custom script execution.
- An example function.