Parameter · Version and Platform Compatibility / Other Platforms and Clients
transform_null_equals
Treats "expr=NULL" as "expr IS NULL". When turned on, expressions of the form expr = NULL (or NULL = expr) are treated as expr IS NULL, that is, they return true if expr evaluates to the null value, and false otherwise. The correct behavior of expr = NULL is to always return null (unknown).
- Default:
off - Type: bool
- Applies: Session (any user)
In plain words
In SQL, x = NULL is never true: comparing anything with "unknown" gives "unknown", so WHERE x = NULL returns no rows. You must write x IS NULL. Some old clients (notably Microsoft Access forms) generated = NULL anyway. With this setting on, PostgreSQL rewrites the literal form expr = NULL into expr IS NULL.
For DBAs and operators
Verified on 18.6: SELECT 1 WHERE NULL = NULL returns 0 rows normally and 1 row with the setting on.
The rewrite is purely syntactic: it happens only when the parser sees the literal keyword NULL on one side of =. It does not apply to x = $1 with a null parameter, x = some_column, IN (NULL), or <> NULL. So it cannot "fix" an application in general, and it makes the same query behave differently depending on how it was written.
Better alternatives: x IS NULL, or x IS NOT DISTINCT FROM $1 when the value may be null. Off by default and should stay off.
How to change it
Any user can change it for their session with SET.