Parameter · Version and Platform Compatibility / Previous PostgreSQL Versions
array_nulls
Enables input of NULL elements in arrays. When turned on, unquoted NULL in an array input value means a null value; otherwise it is taken literally.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
When you write an array as text, like '{1,NULL,3}', the unquoted word NULL normally means a missing (null) element. With array_nulls = off it is taken literally: for a text array it becomes the four-letter string "NULL"; for a numeric array it is an error. This exists for compatibility with PostgreSQL versions before 8.2.
For DBAs and operators
Verified on 18.6: ('{1,NULL}'::text[])[2] IS NULL is true by default and false with the setting off.
It affects only array input parsing (array_in), including COPY of array columns. Output is unaffected: null elements are always printed as NULL, and a real string "NULL" is printed quoted as "NULL", so dump and reload round-trips correctly with the default. The ARRAY[…] constructor is not affected either.
Only consider off when loading data produced by very old software that wrote the literal word NULL as a value. Otherwise leave it on.
How to change it
Any user can change it for their session with SET.