Reducing the number of columns with bit

After digging through a table consisting of dozens of boolean values I thought it might be helpful to more folks out there to know about bitfields in PostgreSQL.

Basically “bit” can be used just as a normal char data type. The idea behind bit is to have a fixed set of bits, which can be accessed at will. Here is an example:

test=# SELECT '0110'::bit(4);
 bit
------
 0110
(1 row)

You can use zeros and ones to represent bits in a PostgreSQL bitfield. The nice thing about it is that you can easily cast those bitfields to integer:

test=# SELECT '0110'::bit(4)::int4;
int4
------ 
    6
(1 row)

In our case we would have reduced 4 boolean columns to just one single column. Just imagine the benefit if you have to deal with hundreds of values. Clearly – normalizing boolean columns is not too attractive either.

Converting integer back to bit

The previous example has shown how a bitfield can be turned into integer. Let us try to convert stuff back now:

test=# SELECT 6::bit(4);
  bit
------
 0110
(1 row)

In this case we have turned a number into a bitfield. All it takes here is a simple cast.

Setting and getting bits

What happens if you want to update a column containing a bitfield? To do so, PostgreSQL offers a function called set_bit. It takes a bitfield, a position as well as the desired new value of the n-th bit you want to change.

Setting a bit works as follows:

test=# SELECT set_bit('0110'::bit(4), 3, 1);
 set_bit
---------
    0111
(1 row)

If you want to fetch a certain bit from your field, you can call get_bit:

test=# SELECT get_bit('0110'::bit(4), 2);
 get_bit
---------
      1
(1 row)

In this example we have extracted the third bit from the bitfield.

Bitshifting

Shifting bits around is a pretty common operation. Therefore it can also be done in PostgreSQL. Here are two examples – one with variable bitfields (the bit counterpart to varbit) and one with a fixed bitfield:

test=# SELECT '0110'::bit(10) << 1;
 ?column?
----------
     1100
(1 row)

test=# SELECT '0110'::varbit(10) << 1;
  ?column?
------------
 1100000000
(1 row)

The nice thing about bitfields is that their size is virtually unlimited. You want a thousand bits or maybe ten thousand? No problem – this is all possible and easy to do.

For more posts on data types and their use in PostgreSQL, check out our data type blog spot.
———-
We welcome your feedback on our brand new Facebook page: www.fb.com/cybertec.postgresql