Bitmask trickiness
От | Howard Rogers |
---|---|
Тема | Bitmask trickiness |
Дата | |
Msg-id | AANLkTilFCsMR_0NTYc_2fXbslM_j8xOqw_WTw9aTiVA0@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Bitmask trickiness
Re: Bitmask trickiness Re: Bitmask trickiness Re: Bitmask trickiness |
Список | 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
В списке pgsql-general по дате отправления: