Reducing the number of columns

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 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.

———-
We welcome your feedback on our brand new Facebook page: www.fb.com/cybertec.postgresql