Re: Storing and querying boolean fields

"Richard Brown" <rbrown(at)openratings(dot)com> writes:
> - We are using PostgreSQL 7.3.4, and am locked into this version. I would
> upgrade if I could, but the decision is not mine.

They won't even let you update to 7.3.something-reasonably-current ?

Resign. Go find a job with a boss whose IQ is above room temperature.
You want to be out of there before the finger-pointing starts, which
it certainly will when one of the many fixed-since-7.3.4 bugs eats
your database.

> I need to add many boolean flags to my database that need to be retrieved by
> primary key, as well as be searched upon with decent speed. By "many", I
> mean about 30 booleans total. The added bonus is that some of these values
> need to be searched in an OR manner, while others need to be searched as an
> AND. For example, I will need to do a search that looks something like:
> select * from table where A && B && C && (D || E) && F

As for the problem at hand, the contrib/intarray module would probably
fill the bill nicely, though I dunno how far I'd trust either it or the
GIST index code back in 7.3. You'd represent the flags as an array of
the numbers of the flags that are ON, and search it with the @@
operator:
int[] @@ query_int - returns TRUE if array satisfies query (like '1&(2|3)')
This on-disk representation might be a bit larger than you were hoping
for, but it'd not break if you suddenly found you needed 33 flags not 32...

regards, tom lane