Total number of "1s" in a Postgres bitmask

Question

Is there a way to get the total number of 1's in a Postgres "bit string" type?

Answer 1

The one / first bit? Or the total number of bits flipped on? The former: bit mask (& 1) the bit. The latter: A nasty query, like:

SELECT (myBit & 1 + myBit >> 1 & 1 + myBit >> 2 & 1) AS bitCount FROM myBitTable;

I suppose, you could also cast to a string and count the 1's in PL/SQL.

Answer 2

You have a simple way using plpgsql here.

Answer 3

# select length(replace(x::text, '0', '')) from ( values ('1010111101'::bit varying) ) as something(x);
 length
--------
      7
(1 row)

And approach without string conversion:

# select count(*) from ( select x, generate_series(1, length(x)) as i from ( values ('1010111101'::bit varying) ) as something(x) ) as q where substring(x, i, 1) = B'1';
 count
-------
     7
(1 row)

Answer 4

If you need it to be really efficient, here's a discussion: Efficiently determining the number of bits set in the contents of, a VARBIT field