array_nulls
A backwards compatibility parameter determining interpretation of NULL values as array elements
array_nulls
is a backwards compatibility configuration parameter determing whether unquoted NULL
values are recognized when specifying a string containing array elements.
array_nulls
was added in PostgreSQL 8.2.
Default value
The default value for array_nulls
is: on
.
Usage
From PostgreSQL 8.2, array elements can be specified as NULL
values, e.g.:
postgres=# SELECT '{"foo",NULL,"bar"}'::TEXT[]; text ---------------- {foo,NULL,bar} (1 row)
In PostgreSQL 8.1 and earlier, NULL
literals contained within the string were parsed as string literals, e.g.:
postgres=# SELECT '{"foo",NULL,"bar"}'::TEXT[]; text ------------------ {foo,"NULL",bar} (1 row)
array_nulls
enables the pre-PostgreSQL 8.2 behaviour, in the event this is needed for compatibility with older applications.
Note that this has no effect on NULL
values specified with the ARRAY[]
constructor.
Change history
- PostgreSQL 8.2
- added (commit cecb6075)
Examples
Example demonstrating the effect of array_nulls
:
postgres=# CREATE TABLE foo (val text[]); CREATE TABLE postgres=# SHOW array_nulls; array_nulls ------------- on (1 row) postgres=# INSERT INTO foo VALUES('{"foo",NULL,"bar"}'); INSERT 0 1 postgres=# SET array_nulls TO off; SET postgres=# INSERT INTO foo VALUES('{"foo",NULL,"bar"}'); INSERT 0 1 postgres=# SELECT val[2] IS NULL FROM foo; ?column? ---------- t f (2 rows)
References
- PostgreSQL documentation: array_nulls