Re: arrays and subselects

On Wed, 20 Sep 2000, Dave Hollenbeck wrote:

> insert into testme1 values(1,'{1,2,3}');
> insert into testme1 values(2,'{4,5,6}');
> insert into testme2 values(10,5);
>
> select key2 from testme2 where data2 in (select data1 from testme1
> where key1 = 2);
>
> Generates the following error
>
> ERROR: Unable to identify an operator '=' for types 'int2' and '_int2'
> You will have to retype this query using an explicit cast
>
> Although I could name the array elements individually, this would be
> most useful when the length of the array is variable.

I'd suggest looking at the array package in contrib which includes
functions/operators for element-in-array. Then you can use a simple
join or exists to do the query (also avoiding potential ugliness in using
in anyway).

The other option of course to make an int2=_int2 operator which is the
element-in-array, which would probably let you use the above syntax.
However, I'm not sure that's a good default. This is a problem with
arrays, are they data or are they a short form for rows of that value
type?