[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Postgres and bitwise operator

Posted on 2009-02-16
Medium Priority
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
  • 3
  • 2
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 2000 total points
ID: 23651061
So you need to isolate that 5th bit ...
Consulting this ... http://www.postgresql.org/docs/8.0/interactive/functions-math.html ...
what about

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

Open in new window


Author Comment

ID: 23651684
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
ID: 23652192
Glad to help.  Now that you post the correction I see where I wasn't quite right ... should have seen it the first time!

Accepted Solution

phil435 earned 0 total points
ID: 23654161
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
ID: 23654303
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

834 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