Postgres and bitwise operator

Posted on 2009-02-16
Last Modified: 2012-05-06
I am trying to figure out how to check a column in my database to see if a bit is on or off. I have a bigint column that actually right now only represents 8 values or bits. What I need to do is check to see if the 5th value or 5th bit in this column is on or off. I have written one select statement that will give me everything up to the 5th bit but if the 6,7,and 8th bits are on with the 5th it ignores the count on those values. Here is my query:

select * from sometable where bigintcolumn >> 5=1;

Like I stated above this works fine until I have the 5th bit on with a bit from position 6 or greater on also. It will ignore these values. I guess I may be truncating everything past the 5th bit position in my query above.
Question by:phil435
    LVL 32

    Assisted Solution

    by:Daniel Wilson
    So you need to isolate that 5th bit ...
    Consulting this ... ...
    what about

    select * from sometable where bigintcolumn & (2 << 4)=1;

    Open in new window

    LVL 2

    Author Comment

    Ok with a little modification that works depending on if you start your bit position from 0 or 1 :) You need to say

    select * from sometable where bigintcolumn & (2 << 4)=32 or
    select * from sometable where bigintcolumn & (1 << 5)=32
    Also couldnt  use =1 since that bits value was 32
    You are starting from 0000 0010 and moving to 0010 0000

    Also found that this works:

    select * from sometable where 32 & bigintcolumn = 32

    Thanks for the help
    LVL 32

    Expert Comment

    by:Daniel Wilson
    Glad to help.  Now that you post the correction I see where I wasn't quite right ... should have seen it the first time!
    LVL 2

    Accepted Solution

    I will award the points to you but do you know how I set the correction as an assisted solution. I dont want any of the points just to highlight the correction.
    LVL 32

    Expert Comment

    by:Daniel Wilson
    On your comment, click Accept and Assign Points.  that will mark yours as the solution but permit you to mark mine as an assist.


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: ( This requires some add-o…
    Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now