Re: xpath() subquery for empty array

From: Roy Walter <walt(at)brookhouse(dot)co(dot)uk>
To: Scott Bailey <artacus(at)comcast(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: xpath() subquery for empty array
Date: 2009-07-12 20:49:15
Message-ID: 4A5A4C4B.5030004@brookhouse.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Scott Bailey wrote:
> 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(at)samason(dot)me(dot)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.
Thanks, that's great, I'm delighted that there's a solution. Where
exactly does that fit in terms of my original query, i.e.:

SELECT x
FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml)
AS x FROM docs) AS y WHERE x <> [test for empty array?????????]

-- Roy

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brent Wood 2009-07-12 20:52:49 Re: indexes on float8 vs integer
Previous Message Scott Bailey 2009-07-12 20:14:15 Re: xpath() subquery for empty array