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.

Regards.


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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFTk8zIB7bNG8LQkwRAsG2AJ0SZ+yGIyCqdDGtVmrWHH zPG61QTACcD9bS
tqq2ZgTlsyZ/PUA+0/TpunQ=
=Ik8V
-----END PGP SIGNATURE-----

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/ -->

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq