Thread: NULL in arrays
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 stringvalue "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/ -->
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/
> From each according to his ability. To each according to his ability to litigate.
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