Re: Bitmask trickiness

> 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.

What's not entirely clear to me is whether you only want to find colours that have BOTH Yellow and Orange set and nothing else, or colours that have EITHER Yellow and Orange set and nothing else.

The first case has been answered by Stephen (use a straight 'equals'). The other case is a bit more complicated.

That 11 matches using "& 10" is because you filtered out all the other bits in your comparison by anding them with '0', while they /are/ relevant: they aren't allowed to be '1' after all. You probably need to look at the inverted versions of these numbers to get what you need.

My bit-foo is a bit rusty, but this looks like what you need (I used bit-strings for my own convenience):

development=> select (~ '01010'::bit(5)) | '01010'::bit(5) = '11111'::bit(5);
?column?
----------
t
(1 row)

development=> select (~ '01011'::bit(5)) | '01010'::bit(5) = '11111'::bit(5);
?column?
----------
f
(1 row)

development=> select (~ '01110'::bit(5)) | '01010'::bit(5) = '11111'::bit(5);
?column?
----------
f
(1 row)

development=> select (~ '11010'::bit(5)) | '01010'::bit(5) = '11111'::bit(5);
?column?
----------
f
(1 row)

development=> select (~ '00010'::bit(5)) | '01010'::bit(5) = '11111'::bit(5);
?column?
----------
t
(1 row)

development=> select (~ '01000'::bit(5)) | '01010'::bit(5) = '11111'::bit(5);
?column?
----------
t
(1 row)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4c49503f286213027486771!