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