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:

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:

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:

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:

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

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:

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.