Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Total number of "1s" in a Postgres bitmask

Tags:

postgresql

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

like image 411
Yehuda Katz Avatar asked Dec 15 '09 21:12

Yehuda Katz


1 Answers

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 ''.

like image 64
Gijs Kant Avatar answered Oct 04 '22 02:10

Gijs Kant