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.
