arrays and subselects

From: Dave Hollenbeck <dave(at)fremenworks(dot)org>
To: pgsql-general(at)hub(dot)org
Cc: dave(at)moby5(dot)fremenworks(dot)org
Subject: arrays and subselects
Date: 2000-09-20 15:14:21
Message-ID: 200009201514.JAA00698@moby5.fremenworks.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry to repeat myself, but I've gotten a couple improperly formed
digests which my mail reader couldn't decipher, and I'm afraid I
missed a response.

Am I supposed to be able to use the result of selecting an array as
the subquery for an 'in' clause? It seems very natural, and would
be mighty convenient.

For example:

create table testme1 (
key1 int2,
data1 int2[3]
);

create table testme2 (
key2 int2,
data2 int2
);

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 greatly appreciate any tips, pointers, or insight.

Thanks,
Dave

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Buddy Lee Haystack 2000-09-20 15:20:14 Re: pqReadData() -- backend closed the channel unexpectedly
Previous Message Martin Gainty 2000-09-20 15:00:17 Re: Building Windows fat clients