Re: xpath() subquery for empty array

Sam Mason wrote:
> On Sun, Jul 12, 2009 at 06:41:57PM +0100, Roy Walter wrote:
>> Scott Bailey wrote:
>>> Roy Walter wrote:
>>>> How do I test for an empty array in postgres?
>>> WHERE x != array[]::xml[]
>>>
>>>
>> Thanks Scott but that throws up a syntax error (at the closing bracket
>> of array[]):
>>
>>    ERROR:  syntax error at or near "]"
>>    LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[]
>
> Even if that syntax was correct it wouldn't work, xml values don't have
> an equality operator defined for them.  I've normally tested the array
> size to figure out when they're empty, something like:
>
>   array_upper($1,1) > 0
>
> However, I've just noticed that this returns NULL rather than zero as
> I was expecting for an empty array (i.e. the literal '{}'). It also
> doesn't seem to do useful things if you're using unusual bounds on your
> array.
>
> Bah, the semantics of arrays in PG always seem over-complicated to me!

This worked on both 8.3 and 8.4

SELECT *
FROM (
   VALUES( '{}'::_xml),('{<root/>}'::_xml)
) sub
WHERE array_upper(column1, 1) > 0Sam Mason <sam@samason.me.uk>


And as Sam noted, array_upper needs to return null if the array is empty
because 0 and -1 can be valid indexes for arrays in postgres.