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

How do i extract a certain bit from a bigint column

From
Mats Sjöberg
Date:

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

Best regards, Mats

 

Attachment

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

From
Mike Rylander
Date:
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


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

From
Tom Lane
Date:
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


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

From
Bruno Wolff III
Date:
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;