Re: Inconsistent behavior on Array & Is Null? - Mailing list pgsql-hackers

Josh Berkus wrote:
> I'm noticing some inconsistent behavior regarding empty arrays and IS NULL 
> status.    For example:

> net_test=# select array_upper('{}'::INT[], 1) IS NULL;
>  ?column?
> ----------
>  t
> (1 row)

This is correct. There are no dimensions to an empty array by 
definition. The only other way to handle this would be an ERROR. I 
followed the lead of (the pre-existing function) array_dims() when 
creating array_upper() and array_lower().

> net_test=# select '{}'::INT[] IS NULL;
>  ?column?
> ----------
>  f
> (1 row)

This is also correct, and completely orthogonal to the first example. 
There is a difference between an empty array and NULL, just like there 
is between an empty string and NULL.

> I feel that this is confusing; an empty array should be considered NULL 
> everywhere or nowhere.

As I said above, that makes no more sense than saying '' == NULL

> For that matter, the new array declaration syntax does not support
> empty arrays:
> net_test=# select ARRAY[ ]::INT[];
> ERROR:  syntax error at or near "]" at character 15

This is a known issue, and will not be easily fixed. We discussed it at 
some length last June/July. See especially:

http://archives.postgresql.org/pgsql-hackers/2003-06/msg01174.php
http://archives.postgresql.org/pgsql-hackers/2003-06/msg01195.php
http://archives.postgresql.org/pgsql-hackers/2003-06/msg01196.php
http://archives.postgresql.org/pgsql-hackers/2003-06/msg01298.php

Joe