From: | Howard Rogers <hjr(at)diznix(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Bitmask trickiness |
Date: | 2010-07-21 03:59:00 |
Message-ID: | AANLkTilFCsMR_0NTYc_2fXbslM_j8xOqw_WTw9aTiVA0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Suppose 1=Red, 2=Yellow, 4=Green and 8=Orange.
Now suppose the following data structures and rows exist:
create table coloursample (recid integer, colour integer, descript varchar);
insert into coloursample values (1,2,'Yellow only');
insert into coloursample values (2,10,'Yellow and Orange');
insert into coloursample values (3,11,'Red, Yellow and Orange');
insert into coloursample values (4,12,'Green and Orange');
insert into coloursample values (5,13,'Red, Green and Orange');
Selecting things which have some yellow in them somewhere is easy:
select * from coloursample where colour & 2>0;
It's also easy to find records which have either some yellow or some
orange (or both) in them:
select * from coloursample where colour & 10>0;
But how do I find records which are ONLY yellow and orange, and
exclude records which have some other colour mixed in, in one simple
query without a lot of 'not this, not that' additions, and without
using multiple separate AND tests to nail it down?
I thought to do
select * from coloursample where colour & 10 = 10;
...but that's not right, because it finds the third record is a match.
(The question comes about because I'm trying to test for up to 15
attributes per record. One record has a bitmask value of 21205, say.
That should mean the '1' bit is set (because 21205 =
1+4+16+64+128+512+4096+16384), but when I do the above queries for
21205 & 4098 (which is 4096 + 2, and therefore should not be finding
records with the '1' bit set), the record is being returned because
the 4096 bit is being detected (correctly) as 'on'. I want the query
to only return records where both bits are true, but I don't want to
have to test the records 15 times to find out!).
I suspect the answer is really simple... but I'm having writer's block
today! All help appreciated.
Regards
HJR
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2010-07-21 04:38:55 | Re: what do i need to know about array index? |
Previous Message | Craig Ringer | 2010-07-21 02:52:31 | Re: Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock |