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

On Sun, Nov 05, 2006 at 08:23:59PM +0100, Guillaume Lelarge wrote:

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.

This is because double-quotes remove the "specialness" of the word
null, double-quotes is what you'd need if you had a column called
"NULL". That's why it's complaining about unknown columns.

Note that the constructs:

ARRAY['a',"NULL",'c']

and

'{a,"NULL",c}'

are *completely* different. The first is a special array constructor
and all its parameters are normal SQL expressions, so you can reference
columns and use NULL directly without quotes. The latter is the string
value of the array, which is specially decoded. Thats why the latter
treats the double quotes differently.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout a �crit :

On Sun, Nov 05, 2006 at 08:23:59PM +0100, Guillaume Lelarge wrote:

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.

This is because double-quotes remove the "specialness" of the word
null, double-quotes is what you'd need if you had a column called
"NULL". That's why it's complaining about unknown columns.

Note that the constructs:

ARRAY['a',"NULL",'c']

and

'{a,"NULL",c}'

are *completely* different. The first is a special array constructor
and all its parameters are normal SQL expressions, so you can reference
columns and use NULL directly without quotes. The latter is the string
value of the array, which is specially decoded. Thats why the latter
treats the double quotes differently.

OK, thanks to both of you for your answer. I did not even think of the
'{ items }' construct. You're right. It works that way and your
explanation makes sense.

Thanks again, Andrew and Martijn. It will help me to write this document
on PostgreSQL 8.2.

--
Guillaume.
<!-- http://abs.traduc.org/
http://lfs.traduc.org/
http://traduc.postgresqlfr.org/ -->

On Sun, Nov 05, 2006 at 09:53:08PM +0100, Martijn van Oosterhout wrote:

Note that the constructs:

ARRAY['a',"NULL",'c']

and

'{a,"NULL",c}'

are *completely* different. The first is a special array constructor
and all its parameters are normal SQL expressions, so you can reference
columns and use NULL directly without quotes. The latter is the string
value of the array, which is specially decoded. Thats why the latter
treats the double quotes differently.

This could probably be expanded on in the docs... mentioning the
difference in the NULLs section would be a good start. IE:

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. Note that if you use the ARRAY construct you should just use
a bareword NULL instead.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Monday 06 November 2006 20:16, Jim C. Nasby wrote:

On Sun, Nov 05, 2006 at 09:53:08PM +0100, Martijn van Oosterhout wrote:

Note that the constructs:

ARRAY['a',"NULL",'c']

and

'{a,"NULL",c}'

are *completely* different. The first is a special array constructor
and all its parameters are normal SQL expressions, so you can reference
columns and use NULL directly without quotes. The latter is the string
value of the array, which is specially decoded. Thats why the latter
treats the double quotes differently.

This could probably be expanded on in the docs... mentioning the
difference in the NULLs section would be a good start. IE:

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. Note that if you use the ARRAY construct you should just use
a bareword NULL instead.

This is not terribly clearer :-) Hopefully we can put examples along with it,
since that helps out tremendously.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL