binary - Counting the same positional bits in postgresql bitmasks

Question

I am trying to count each same position bit of multiple bitmasks in postgresql, here is an example of the problem:

Suppose i have three bitmasks (in binary) like:

011011011100110
100011010100101
110110101010101

Now what I want to do is to get the total count of bits in each separate column, considering the above masks as three rows and multiple columns.

e.g The first column have count 2, the second one have count 2, the third one have count of 1 and so on...

In actual i have total of 30 bits in each bitmasks in my database. I want to do it in PostgreSQL. I am open for further explanation of the problem if needed.

Answer 1

You could do it by using the get_bit functoin and a couple of joins:

SELECT sum(bit) FILTER (WHERE i = 0)  AS count_0,
       sum(bit) FILTER (WHERE i = 1)  AS count_1,
       ...
       sum(bit) FILTER (WHERE i = 29) AS count_29
FROM bits
   CROSS JOIN generate_series(0, 29) AS i
   CROSS JOIN LATERAL get_bit(b, i) AS bit;

The column with the bit string is b in my example.