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

Based on the discussion here, the above mentioned thread Efficiently determining the number of bits set in the contents of, a VARBIT field, and the Bit Twiddling Hacks page, I published a PostgreSQL extension: pg_bitcount. If you install that extension (see instructions there), you can count the number of bits set in a bitstring using:

# Register the extension in PostgreSQL
create extension pg_bitcount;

# Use the pg_bitcount function
select public.pg_bitcount(127::bit(8));
select public.pg_bitcount(B'101010101');

I compared a number of different algorithms for performance and using a table lookup seems to be the fastest. They are all much faster than converting to text and replacing '0' with ''.