Skip site navigation (1) Skip section navigation (2)

Re: WHERE clause with 40+ conditions

From: Daniel Staal <DStaal(at)usa(dot)net>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: WHERE clause with 40+ conditions
Date: 2012-05-07 15:14:17
Message-ID: a8c3c1c2ae056b638776bdee7368df35@mail.magehandbook.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 2012-05-04 14:31, DrYSG wrote:
> My users are presented with a list of checkbox for 4 different 
> attributes
> (e.g. type of image, country, etc.)
>
> For type of image they can choose JPG, PNG, JP2000, etc. or turn off 
> the
> check box.
>
> My naive implementation would have a WHERE clause with over 40 
> different
> tests (type='JPG' OR type='PNG' OR country='USA' ...)
>
> There has to be an more efficient way to do this (and gives better 
> hints to
> the PLANNER).

Besides the bitmask already mentioned, you can also do something like 
this:

(type IN ( 'JPG', 'PNG' ) OR country='USA')

Which might simplify things for you a bit. See:
http://www.postgresql.org/docs/9.1/interactive/functions-comparisons.html

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

In response to

pgsql-novice by date

Next:From: Caio BoiteuxDate: 2012-05-07 16:28:23
Subject: problem with streaming replication
Previous:From: Daniel StaalDate: 2012-05-07 15:06:11
Subject: Re: WHERE clause with 40+ conditions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group