Re: NULL in arrays

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