Thread: bit/integer operations in postgres

Does anyone know how I can solve this problem in Postgres:

I am creating a table of user permissions.  I want to represent each
permission as a bit location.  Each new permission I add to my system
would be assigned to the next available bit (or column if you will)
That way I can simply turn on a particular bit location for a
particular user and then AND their permission level with a particular
permission bit.
Just as an example:
Access to financial data is the first bit and would be represented by
1.
Access to the admin section would be the 2nd bit, represented by 01.
Access to the reporting section would be the 3rd bit, represented by
001.

Thus a user with access to the admin section but nothing else would
have 010
and a user with access to everything would have 111.


All possible permissions are stored in one table and a users permission
level is stored in the user table, but both fields are integer data
types.

HERE IS WHAT I WANT TO DO:
How do I setup a postgres constraint that will not allow an insert on
the permission table with an integer value that has more than one bit
set.  In other words you could insert 1, 2, 4, 8, 16, 32, etc. because
there is only a single bit on in all of those integers, but not insert
3, 5, 6, 7, 9, 15, 19, etc. because they have multiple???

On Thu, Dec 22, 2005 at 05:04:30PM -0800, littlebutty wrote:
> HERE IS WHAT I WANT TO DO:
> How do I setup a postgres constraint that will not allow an insert on
> the permission table with an integer value that has more than one bit
> set.  In other words you could insert 1, 2, 4, 8, 16, 32, etc. because
> there is only a single bit on in all of those integers, but not insert
> 3, 5, 6, 7, 9, 15, 19, etc. because they have multiple???

Well, you could use the properties of two-complement arithmentic to do
it:

CHECK(value = (value & ((~value)+1)) )

Or perhaps you should look into the bit datatype with get_bit abd
set_bit.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.