Special features of working with PostgreSQL

Level: intermediate

General recommendations

• Create triggers, views, and functions using the DROP … IF EXISTS expression (if needed, you can use the CASCADE command), then the CREATE OR REPLACE expression. Do not use the CREATE OR REPLACE command alone.

• Use "public" instead of the "dbo" schema.

• Note that system names are case-sensitive. Use quotes ("") for names of tables, columns, and other entities.

• Use the BOOL type instead of the BIT type used in Microsoft SQL. You do not have to use the WHERE "boolColumn" = true expression to match the value of a BOOL type field. You can use the WHERE "boolColumn" or WHERE NOT "boolColumn" expression instead.

• You can use the short form of the ::TEXT explicit conversion.

• String matching in PostgreSQL is case-sensitive. You can use the iLIKE keyword to execute a case-insensitive matching. Note that matching that uses this keyword is slower than matching that uses the UPPER+LIKE expression. The UPPER+LIKE expression has less strict index applicability rules than iLIKE.

• You can use the CREATE CAST command to cast types if source code has no implicit type casting. Learn more about the type casting in the official PostgreSQL documentation.

• Create a special procedure parameter to store the current recursion level because PostgreSQL recursive procedures do not have a built-in NESTLEVEL function.

• Use the NAME type in PostgreSQL instead of the SYSNAME type used in Microsoft SQL.

• Create rules instead of empty INSTEAD triggers. For example:

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

• Convert INT type to BOOL type explicitly. Implicit conversion of INT type to BOOL type does not work in PostgreSQL if the corresponding CAST statement is present and the UPDATE command is executed.

• Use allowed ways to format string literals. Learn more about string literals in the official PostgreSQL documentation (quote_ident, quote_literal, format).

• Use the following expression

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

  instead of @@ROWCOUNT.

• Use the following expression

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

  instead of the following Microsoft SQL expression

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

  The response field will have a BOOL type.

Data type matching

Creatio, Microsoft SQL, and PostgreSQL data type matching

Type value in the Creatio object Designer	Data type
	Microsoft 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

---

See also

Data access through ORM

Access data directly

---

Resources

Official PostgreSQL documentation (cast types)

Official PostgreSQL documentation (quote_ident)

Official PostgreSQL documentation (quote_literal)

Official PostgreSQL documentation (format)