Re: NULL in arrays

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.