Re: NULL in arrays

Guillaume Lelarge wrote:
> Hi all,
>
> I tried to work with NULL items in an array but there are two things I
> don't understand. (I just did a cvs update, make, make install... so
> sources are current.)
>
> On http://momjian.us/main/writings/pgsql/sgml/arrays.html, you can read :
>   To set an element of an array constant to NULL, write NULL  for the
>   element value. (Any upper- or lower-case variant of NULL will do.) If
>   you want an actual string value "NULL", you must put double quotes
>   around it.
>
> amarok=# SELECT ARRAY['a',NULL,'c'];
>    array
> ------------
>  {a,NULL,c}
> (1 row)
>
> Setting a NULL value seems to work.
>
> amarok=# SELECT ARRAY['a',"NULL",'c'];
> ERROR:  column "NULL" does not exist
> LINE 1: SELECT ARRAY['a',"NULL",'c'];
>                          ^
>
> Using double quotes throws an error.
>
> amarok=# SELECT ARRAY['a','NULL','c'];
>     array
> --------------
>  {a,"NULL",c}
> (1 row)
>
> Using single quotes seems to work. Is it a documentation mistake ? Or
> did I miss something ?
>
> There's another thing I don't understand. What is the use for
> array_nulls configuration variable ?
>
> amarok=# SET array_nulls TO off;
> SET
> amarok=# SELECT ARRAY['a','NULL','c'];
>     array
> --------------
>  {a,"NULL",c}
> (1 row)
>
> amarok=# SELECT ARRAY['a',"NULL",'c'];
> ERROR:  column "NULL" does not exist
> LINE 1: SELECT ARRAY['a',"NULL",'c'];
>                          ^
> amarok=# SELECT ARRAY['a',NULL,'c'];
>    array
> ------------
>  {a,NULL,c}
> (1 row)
>
> Disabling it doesn't seem to have any effect at all in the way
> PostgreSQL treats NULL values. I thought SELECT ARRAY['a',NULL,'c']
> would behave like SELECT ARRAY['a','NULL','c'] with array_nulls
> disabled. Did I misunderstand something ?
>
> I would be really interested in any information or documentation you can
> give me on these matters. Thanks.
>
>   


Doesn't this mean to use double quotes in an array literal? e.g.:

pl_regression=# SELECT '{a,"NULL",c}'::text[];    text    
--------------{a,"NULL",c}
(1 row)


cheers

andrew