Postgres Pro
Downloads
Home   >   mailing lists

Thread: Empty array IS NULL?

Empty array IS NULL?

From
Markus Bertheau
Date:
Hi,

is the empty array representable in PostgreSQL, and is it
distinguishable from NULL?

oocms=# SELECT ARRAY[1, 2];array
-------{1,2}
(1 запись)
oocms=# SELECT '{1, 2}'::INT[];int4
-------{1,2}
(1 запись)
oocms=# SELECT ARRAY[];
ERROR:  syntax error at or near "]" at character 14
oocms=# SELECT '{}'::INT[];int4
------{}
(1 запись)
oocms=# SELECT ARRAY(SELECT 1 UNION SELECT 2);?column?
----------{1,2}
(1 запись)
oocms=# SELECT ARRAY(SELECT 1 WHERE FALSE);?column?
----------
(1 запись)

--
Markus Bertheau <twanger@bluetwanger.de>

Re: Empty array IS NULL?

From
Joe Conway
Date:
Markus Bertheau wrote:
> is the empty array representable in PostgreSQL, and is it
> distinguishable from NULL?

Yes, and yes.

regression=# select '{}'::int[]; int4
------ {}
(1 row)

regression=# select NULL::int[]; int4
------

(1 row)

Since NULL array elements are not currently supported, attempting to 
construct an array with a NULL element results in NULL, not an empty array.

Joe

Re: Empty array IS NULL?

From
Markus Bertheau
Date:
В Пнд, 28.06.2004, в 18:26, Joe Conway пишет:
> Markus Bertheau wrote:
> > is the empty array representable in PostgreSQL, and is it
> > distinguishable from NULL?
>
> Yes, and yes.
>
> Since NULL array elements are not currently supported, attempting to
> construct an array with a NULL element results in NULL, not an empty array.

Thanks.

How do I specify an empty array with the standard syntax?

--
Markus Bertheau <twanger@bluetwanger.de>

Re: Empty array IS NULL?

From
Joe Conway
Date:
Markus Bertheau wrote:
> How do I specify an empty array with the standard syntax?
> 

Actually, up until the 7.4 release, the array literal syntax was the 
*only* syntax (i.e. '{...}'). The newer array constructor expression 
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there 
are some non-trivial technical difficulties to be solved. Unfortunately 
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.

Joe

Re: Empty array IS NULL?

From
Joe Conway
Date:
Markus Bertheau wrote:
> How do I specify an empty array with the standard syntax?

Actually, up until the 7.4 release, the array literal syntax was the 
*only* syntax (i.e. '{...}'). The newer array constructor expression 
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there 
are some non-trivial technical difficulties to be solved. Unfortunately 
that won't change in the upcoming 7.5 either :( -- perhaps for 7.6.

Joe

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

Re: Empty array IS NULL?

From
Greg Stark
Date:
Markus Bertheau <twanger@bluetwanger.de> writes:

> oocms=# SELECT ARRAY(SELECT 1 WHERE FALSE);
>  ?column?
> ----------
>   

This one seems strange to me. Shouldn't it result in an empty array?


-- 
greg