Is there a way to get the total number of 1's in a Postgres "bit string" type?
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 ''
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With