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?

Indigoid

Answer 1

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

Chauvinism

@YehudaKatz: the discussion in the link is interesting, but it's about "first bit set" and "last bit set", which does not answer the question at hand. How can this can be the right answer? – Korwin

there is nbits_set function too – Gloomy

Answer 2

# 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)

Monazite

I like the string replace & length method. +1 – Shinbone

Answer 3

I know, this is already an old topic, but I found a cool answer here: https://mcmap.net/q/241945/-counting-the-number-of-occurrences-of-a-substring-within-a-string-in-postgresql

So adapted it would be:

=# select length(regexp_replace((B'1010111101')::text, '[^1]', '', 'g'));
 length
--------
      7
(1 row)

Planchette

Answer 4

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

Jemie

Answer 5

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.

Shinbone

total number of bits flipped on. Casting to a strings seems epically non-performant. I suppose I could write a function to handle the series of bits (I don't know enough PL/SQL to know whether I can easily loop over bitsize and extract the result) – Indigoid

Hmmm... it looks like bitmasks are already strings internally, so maybe that solution isn't as bad as it looks. – Indigoid

Answer 6

You have a simple way using plpgsql here.

Hedgerow