transform_null_equals

Definition PG 18 manual

When 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 SQL-spec-compliant behavior of expr = NULL is to always return null (unknown). Therefore this parameter defaults to off.

However, filtered forms in Microsoft Access generate queries that appear to use expr = NULL to test for null values, so if you use that interface to access the database you might want to turn this option on. Since expressions of the form expr = NULL always return the null value (using the SQL standard interpretation), they are not very useful and do not appear often in normal applications so this option does little harm in practice. But new users are frequently confused about the semantics of expressions involving null values, so this option is off by default.

Note that this option only affects the exact form = NULL, not other comparison operators or other expressions that are computationally equivalent to some expression involving the equals operator (such as IN). Thus, this option is not a general fix for bad programming.

Refer to Section 9.2 for related information.

How it works

Treats "expr=NULL" as "expr IS NULL". It can be changed at session scope, so different sessions may observe different behavior.

The parser rewrites expr = NULL to expr IS NULL for broken legacy clients. SQL's normal three-valued semantics make equality with NULL yield unknown, so enabling this can hide application defects and does not rewrite other NULL comparisons.

Monitor and change transform_null_equals together with array_nulls, backslash_quote, escape_string_warning. Validate on the relevant server role and real workload, then use its user context to choose session change, reload, or restart; a historical boot default is not the current effective value.

Operational considerations

Keeping a compatibility switch permanently instead of fixing the client.

Testing in one session and deploying globally to unrelated applications.

Confusing parsing compatibility with data or security compatibility.

Forgetting to remove an override after the upgrade migration is complete.

Workload guidance

OLAP: Regression-test ETL, generated SQL, and old drivers, where parsing/quoting assumptions hide. Performance is rarely a reason to change this switch.

OLTP: Keep the modern default and repair legacy clients/SQL that depend on transform_null_equals. Test migration at session scope first; do not make a compatibility switch permanent cluster policy.

SMALL: Keep the default without a legacy requirement. If temporarily enabled, record owner, affected connections, and a removal date.