array_nulls — PostgreSQL configuration parameter

This controls whether the array input parser recognizes unquoted NULL as specifying a null array element.

At a glance

What it does

This controls whether the array input parser recognizes unquoted NULL as specifying a null array element. By default, this is on, allowing array values containing null values to be entered. However, PostgreSQL versions before 8.2 did not support null values in arrays, and therefore would treat NULL as specifying a normal array element with the string value “NULL”. For backward compatibility with applications that require the old behavior, this variable can be turned off.

Note that it is possible to create array values containing null values even when this variable is off.

(Description quoted from the official PostgreSQL documentation.)

How to apply a change

Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.

Inspect the current value and source with SHOW array_nulls; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'array_nulls';.

Tuning guidance

This controls backward-compatibility behaviour, not performance. Keep it at the modern default unless a specific legacy application depends on the older behaviour; turning compatibility flags on to paper over application bugs stores up problems for a future upgrade. Treat any non-default value as technical debt to remove.

Reference

PostgreSQL documentation — array_nulls.