Can I Convert A Bunch Of Boolean Columns To A Single Bitmap In Postgresql?

I'd like to convert a query such as: SELECT BoolA, BoolB, BoolC, BoolD FROM MyTable; Into a bitmask, where the bits are defined by the values above. For example, if BoolA and Bool

Solution 1:

For a bitmask, the type bitstring would be the better choice. Could look like this then:

SELECT BoolD::int::bit
    || BoolC::int::bit
    || BoolB::int::bit
    || BoolA::int::bit
FROM tbl;

TRUE converts to 1, FALSE to 0. You can simply concatenate bits to a bitstring.

---

Cast bit(n) to integer

It seems you need an integer as result - there is a simple & fast way:

SELECT (BoolD::int::bit
     || BoolC::int::bit
     || BoolB::int::bit
     || BoolA::int::bit)::bit(4)::int
FROM tbl;

Be sure to read the fine print in the chapter "Bit String Functions and Operators" of the manual.

---

I came up with two more ideas and put together a quick test / reference with 10k rows to sum it all up.

Test setup:

CREATE TEMP TABLE t (boola bool, boolb bool, boolc bool, boold bool);
INSERT INTO t
SELECT random()::int::bool
     , random()::int::bool
     , random()::int::bool
     , random()::int::bool
FROM   generate_series(1,10000);

Demo:

SELECT  CASE WHEN boold THEN 1ELSE0 END
     + (CASE WHEN boolc THEN 1ELSE0 END << 1)
     + (CASE WHEN boolb THEN 1ELSE0 END << 2)
     + (CASE WHEN boola THEN 1ELSE0 END << 3) AS andriy

     ,  boold::int
     + (boolc::int << 1)
     + (boolb::int << 2)
     + (boola::int << 3) AS mike

     , (boola::int::bit
     || boolb::int::bit
     || boolc::int::bit
     || boold::int::bit)::bit(4)::int AS erwin1

     ,  boold::int
     | (boolc::int << 1)
     | (boolb::int << 2)
     | (boola::int << 3) AS erwin2

     , (((
       boola::int << 1)
     | boolb::int << 1)
     | boolc::int << 1)
     | boold::int        AS erwin3
FROM   t
LIMIT  15

You could also use a bitwise OR | instead of the + operator. Individual test runs show basically the same performance for all five methods.

Solution 2:

Maybe like this:

SELECT
  (CASEWHEN BoolA THEN1ELSE0END << 0) +
  (CASEWHEN BoolB THEN1ELSE0END << 1) +
  (CASEWHEN BoolC THEN1ELSE0END << 2) +
  (CASEWHEN BoolD THEN1ELSE0END << 3) AS BitMask
FROM MyTable;

where << is the bitwise shift left operator.

Solution 3:

I came up with this approach as well. It's the most concise I could find short of writing a custom function. I'll accept this answer unless anyone has anything more clever.

SELECT
  (BoolD::int<<0) +
  (BoolC::int<<1) +
  (BoolB::int<<2) +
  (BoolA::int<<3)
from MyTable;