Select integer as bit & state table using postgresql

Question

How can I select an integer as a table of bit# & state? For example if my int was 13 I would like to see this output.

bit# | state
 0   |   1
 1   |   0
 2   |   1
 3   |   1
 4   |   0
 5   |   0
 ..  |   ..

Answer 1

Operations on bits are straightforward in Postgres with Bit String Types. You can easily convert an integer to a bit string, e.g.:

select 13::bit(8);
   bit
----------
 00001101
(1 row)

You need to cast the bit string value to text array and unnest it to get the results in the form of a table:

select 8- ord as bit, state
from unnest(string_to_array(13::bit(8)::text, null)) with ordinality as u(state, ord)
order by 1

The required number of bits must be set a priori. Replace 8 with the given number in the above query. In Postgres 14+ you can use string_to_table() instead of unnest(string_to_array()).

Test it in db<>fiddle.