> On 21 Feb 2015, at 12:03, Arup Rakshit <[email protected]> wrote:
>
> On Saturday, February 21, 2015 12:42:03 PM Alban Hertroys wrote:
>>
>>> On 21 Feb 2015, at 9:34, Arup Rakshit <[email protected]> wrote:
>>>
>>> Select * from Emp
>>> where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 =
>>> val14and attr2 = val15 and attr3 = val16);
>>>
>>> Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my
>>> query as :
>>>
>>> Select * from Emp
>>> where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14
>>> and attr2 = val15 and attr3 = val16) or
>>> (attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2
>>> and attr3 = y3);
>>>
>>> So for each new set I need to add one more `or` conditions. It seems wired.
>>>
>>> Any better way to get it done ?
>>
>> If the number of attributes to compare is always the same, you can write:
>>
>> select * from Emp where (attr1, attr2, attr3) in ((val11, val12, val13),
>> (val14, val15, val16), (x1, x2, x3), (y1, y2, y3));
>>
>> Is that the sort of thing you're after?
>>
>> Alban Hertroys
>
> Thanks for the reply, Can the comparisons be done using Array ? I tried, but
> got some syntax error.
Why would you want that?
> psql (9.2.7)
> Type "help" for help.
>
> app_development=# select * from pets where ARRAY[id, animals] IN
> '{{1,2,3},{4,5,6},{7,8,9}}';
> ERROR: syntax error at or near "'{{1,2,3},{4,5,6},{7,8,9}}'"
> LINE 1: select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,...
> ^
> app_development=# select * from pets where ARRAY[id, animals] IN '{{6, 1},
> {5, 1}}';
> ERROR: syntax error at or near "'{{6, 1}, {5, 1}}'"
> LINE 1: select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5,…
At the very least you will need to put braces around the set in IN.
But even then, you'll have to write the query such that ALL elements in your
left-hand array are being matched to ALL elements in any of the right-hand
arrays. Is that possible? Probably, but at this point I feel like I should
remind you of what you put in your signature.
Something like this is closer to what you need:
select * from pets where ARRAY[id, animals] = ANY('{6, 1}', '{5, 1}');
I'm sure that won't match anything though, as the types are all wrong.
> Debugging is twice as hard as writing the code in the first place. Therefore,
> if you write the code as cleverly as possible, you are, by definition, not
> smart enough to debug it.
>
> --Brian Kernighan
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
