Re: Bitmask trickiness

On Fri, Jul 23, 2010 at 6:17 PM, Alban Hertroys
<dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
>> 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,

Hehe. Mine too, it would seem!

>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

Thanks Alban. Steve, too, has joined in again above: the bit-wise OR,
together with an equality test on the stored value, would seem to be
what's called for.

Appreciate the contribution.

I think we can all go home now!! :-)

Regards
HJR