Re: array operators

I'll answer my own question - should have read around more!

The 'any(select ...' syntax is already used to compare row elements:

> postgis_test=# select * from array_check where (node = any(select
> node from array_check where node > 0));
> node | sons
> ------+------
> 1 |
> 2 |
> 3 |
> 4 |
> (4 rows)

... and this includes the possibility of checking whether an array is
the same as a selection of arrays from several rows...

> postgis_test=# select * from array_check where (sons = any(select
> sons from array_check where node < 0));
> node | sons
> ------+--------
> -2 | {-3,3}
> -1 | {-2,4}
> -3 | {1,2}
> (3 rows)

My original query was following this syntax and attempting to check
whether 1 = '{1,2}', which is an:

>> ERROR: operator does not exist: integer = integer[]

The solution seems to be to cast the single array returned by the
inner select explicitly as an array rather than it being a single row
for a single column returned by a subquery (that happens to contain
an array).

> postgis_test=# select * from array_check where (node = any(cast
> ((select sons from array_check where node =-2) as integer[])));
> node | sons
> ------+-------
> 3 |
> -3 | {1,2}

If this is garbage, please tell me!

Cheers,
David

On 28 Oct 2005, at 14:33, David Orme wrote:

> Hi,
>
> Can someone explain where I've gone wrong?
>
> Given the table array_check:
>
>
>> postgis_test=# select * from array_check;
>> node | sons
>> ------+--------
>> 1 |
>> 2 |
>> 4 |
>> 3 |
>> -3 | {1,2}
>> -2 | {-3,3}
>> -1 | {-2,4}
>> (7 rows)
>>
>
> and that this query returns an array value...
>
>>
>> postgis_test=# select sons from array_check where node = -3;
>> sons
>> -------
>> {1,2}
>> (1 row)
>>
>
> and that this works...
>
>
>> postgis_test=# select node = any('{1,2}') as son_rows from
>> array_check ;
>> son_rows
>> -----------
>> t
>> t
>> f
>> f
>> f
>> f
>> f
>> (7 rows)
>>
>
> .. why does this not throw an error?
>
>
>> postgis_test=# select node = any(select sons from array_check
>> where node = -3) as son_rows from array_check ;
>> ERROR: operator does not exist: integer = integer[]
>>
>
> Cheers,
> David
>
>
>> postgis_test=# select version();
>>
>> version
>> ---------------------------------------------------------------------
>> ---------------------------------------------------------------------
>> -------------
>> PostgreSQL 8.0.4 on powerpc-apple-darwin8.2.0, compiled by GCC
>> powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 20041026 (Apple
>> Computer, Inc. build 4061)
>> (1 row)
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>