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

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)

Categories

Arrays, Backwards compatibility, GUC configuration item