Search Postgresql Archives

Re: Query optimization to select rows instead of too many or conditions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Saturday, February 21, 2015 12:42:03 PM Alban Hertroys wrote:
> 
> > On 21 Feb 2015, at 9:34, Arup Rakshit <aruprakshit@xxxxxxxxxxxxxx> 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. 

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,...
                                                       ^
app_development=#


-- 
================
Regards,
Arup Rakshit
================
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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux