Page Comparison

...

MS SQL uses SELECT [ClaimID] from [tblClaims]format while PostgreSQL uses SELECT "ClaimId" from "tblClaims" When the columns/tables are created in all lowercase, the SQL is always case insensitive. But when they were created with mixed case, MS SQL remains insensitive while PostgreSQL requires the specific case to be used, between double quotes.

MS SQL tolerates the PostgreSQL spelling, so all raw SQL should be specified with the PostgreSQL format for compatibility.

This forced the code to use proper casing, especially in LegacyId vs LegacyID.

...

ISNULL(column, ‘default’)  -- SQL Server
NULLIF(column, ‘default’)  -- PostgreSQL, 2 elements
COALESCE(item1, item2, item3…)  -- PostgreSQL, first null of any number of elements

Hierarchical queries/CTE

To query tree-like data like the Location table, one can use Common Table Expressions (CTE). Luckily, the syntax is exactly the same in Postgres an MS SQL, except that Postgres requires “WITH RECURSIVE” instead of a plain “WITH”.

Here is an example:

WITH {"" if settings.MSSQL else "RECURSIVE"} locations AS (SELECT "LocationId", "ParentLocationId"
                   FROM "tblLocations"
                   WHERE "ValidityTo" IS NULL
                     AND ("LocationId" = %(location_id)s OR
                          CASE WHEN %(location_id)s IS NULL THEN coalesce("ParentLocationId", 0) ELSE 0 END =
                          coalesce(%(location_id)s, 0))
                   UNION ALL
                   SELECT l."LocationId", l."ParentLocationId"
                   FROM "tblLocations" l
                            INNER JOIN locations ON locations."LocationId" = l."ParentLocationId"
                   WHERE l."ValidityTo" IS NULL)
SELECT ...