Page Comparison

...

PostgreSQL supports the JSONB data type with pretty good performance.TBC

In the migration, if using SQL, to add the column:

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:

json_ext = FallbackJSONField(db_column="JsonExt", blank=True, null=True)

or set the Model to extend core_models.ExtendableModel

The huge advantage of PostgreSQL with JSONB is that the individual fields can be indexed:

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:

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.

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.

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

HashBytes('MD5', ‘string’)  -- SQL Server
MD5(‘string’)  -- PostgreSQL

MM/DD/YYYY format

CONVERT(nvarchar(10), getdate(), 103)  -- SQL Server
TO_CHAR(now(), ‘DD-MM-YYYY’)  -- PostgreSQL (actually, standard SQL)

ISNULL

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