Re: Comparing bit in an integer field

2009/11/16 A. Kretschmer <andreas.kretschmer@schollglas.com>:
> In response to Amitabh Kant :
>> Hello
>>
>> I need to compare the bit values of a integer field in my table. For example, I
>> have a table called "t1" with just one field "a1" having following values:
>>
>> a1
>> ==
>> 0
>> 12
>> 8
>> 0
>> 1
>> 10
>> 7
>> 19
>>
>> I am trying to fetch all records where the 3rd binary bit is 1, which from the
>> above example should be 12 (00001100) and 7 (00000111). If I convert the values
>> to binary and store it as string, I can easily compare them using substr, but
>> would rather like to retain the integer field. I have tried using the get_bit
>> function, but it seems my understanding of the function is not correct.
>
> test=# select * from t1;
>  a1
> ----
>  0
>  12
>  8
>  0
>  1
>  10
>  7
>  19
> (8 rows)
>
> test=*# select a1 from t1 where (a1>>2)::bit = B'1';
>  a1
> ----
>  12
>  7
> (2 rows)
>

Ah, bit-shifting.  Told you there'd be a cleaner way ;)