Thread: How do i extract a certain bit from a bigint column

On Wednesday 12 May 2004 07:05 am, Mats Sjöberg wrote:
> Hello everyone
> In a table i have a column status of type bigint.
> I need to create a view of the table including all rows with bit 4 set
> (value 8).
> At the same time i need to exclude excludig all rows with bit 2 set.
>
> What is the syntax to extract those bits?
> I have tested get_bit(string, offset) but this requires a string, and not a
> bigint.
> Is there a function to

The easiest way is to test for a bit using bitwise and:

SELECT * FROM table WHERE (status & 2::BIGINT) <> 0 AND (status & 8::BIGINT) =
0;

-miker

Mats Sjöberg <mats.sjoberg@cybernetics.se> writes:
> What is the syntax to extract those bits?

At least in 7.4, you can do it the same way you'd do it in C:

regression=# select ((47::bigint) >> 3) & 1;?column?
----------       1
(1 row)
I'm not sure when the bigint >> and & operators got added, but \do would
tell you quickly enough if they're in your version.

Mind you that this is not going to be an especially fast solution, since
these are not indexable operators.  You might be better advised to
rethink your data representation.
        regards, tom lane

On Wed, May 12, 2004 at 08:17:39 -0400, Mike Rylander <miker@purplefrog.com> wrote:
> 
> The easiest way is to test for a bit using bitwise and:
> 
> SELECT * FROM table WHERE (status & 2::BIGINT) <> 0 AND (status & 8::BIGINT) = 
> 0;

The following will probably be a bit faster:
SELECT * FROM table WHERE (status & 10::BIGINT) = 8;