...
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.
...
PostgreSQL supports the JSONB data type with pretty good performance.
In the migration, if using SQL, to add the column:
Code Block | ||
---|---|---|
| ||
migrations.RunSQL('ALTER TABLE [tblClaim] ADD [JsonExt] TEXT'
if "sql_server" in settings.DB_ENGINE else
'ALTER TABLE "tblClaim" ADD "JsonExt" jsonb') |
In the ORM, either specify the field like:
Code Block |
---|
json_ext = FallbackJSONField(db_column="JsonExt", blank=True, null=True) |
or set the Model to extend core_models.
TBCExtendableModel
The huge advantage of PostgreSQL with JSONB is that the individual fields can be indexed:
Code Block |
---|
create index idx_example on "tblClaim" (("JsonExt" ->> 'foo')) |
For some other operations like checking whether a certain key exists anywhere in the JSON, it is also possible to use GIN indexes. Please see documentation if the need arises.
To query the data, one can simply use the same format as when traversing table joins:
Code Block |
---|
Claim.objects.filter(json_ext__AI__evaluation="pass") |
In plain SQL, the corresponding operator is -> or ->>. The → gets the subelement JSON object while ->> gets the value of the field.
Code Block |
---|
select jsonExt -> 'AI' ->> 'evaluation' for tblClaims; |
jsonExt -> ‘AI' -> ‘evaluation
would be a JSONB object like {"evaluation": "pass"}
while ->> 'evaluation'
would return a string “pass”.
Sequences and auto-increment
...
SQL Server had a peculiar way of adding months to dates that required a specific component for Django. Obviously, that is supported by default on PostgreSQL but in raw queries, this is a different to consider.
Code Block | ||
---|---|---|
| ||
DateAdd(m, months, date) -- SQL Server
date + months * INTERVAL ‘1 month’ -- PostgreSQL |
Django migrations
Most migrations in Django work transparently between databases. However, we had a few SQL Server specific migrations: clustered indexes, creation of missing role rights…
...
One migration had to be split because SQL queries affecting the columns created in the same migration (therefore transaction) were not possible.
MD5
Code Block |
---|
HashBytes('MD5', ‘string’) -- SQL Server
MD5(‘string’) -- PostgreSQL |
MM/DD/YYYY format
Code Block | ||
---|---|---|
| ||
CONVERT(nvarchar(10), getdate(), 103) -- SQL Server
TO_CHAR(now(), ‘DD-MM-YYYY’) -- PostgreSQL (actually, standard SQL) |
ISNULL
Code Block | ||
---|---|---|
| ||
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:
Code Block | ||
---|---|---|
| ||
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 ... |