Re: Inconsistent behavior on Array & Is Null?

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