Re: array operators - Mailing list pgsql-novice

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
>