Hi,
I have SQL query (inside a migration) that works fine on MySQL:
cursor.execute('UPDATE dojo_finding SET mitigated = CASE WHEN last_reviewed is not NULL THEN last_reviewed ELSE %s END WHERE is_Mitigated = 1 AND mitigated IS NULL', [timezone.now()])
But it fails on Postgres because Postgres converts all unquoted column names to lowercase:
django.db.utils.ProgrammingError: column "is_mitigated" does not exist
LINE 1: ...04T18:58:45.581502+00:00'::timestamptz END WHERE `is_Mitigat...
^
HINT: Perhaps you meant to reference the column "dojo_finding.is_Mitigated".
Quoting the column, and changing 1 to True, makes the query work on Postgres:
cursor.execute('UPDATE dojo_finding SET mitigated = CASE WHEN last_reviewed is not NULL THEN last_reviewed ELSE %s END WHERE "is_Mitigated" = True AND mitigated IS NULL', [timezone.now()])
But this query fails on MySQL:
django.db.utils.OperationalError: (1292, "Truncated incorrect DOUBLE value: 'is_Mitigated'")
I tried backquotes, `is_Mitigated`. This works in MySQL, but not in Postgres.
Is there a way to quote the column name which works both in MySQL and Postgres?
Valentijn