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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
