Can I convert a bunch of boolean columns to a single bitmap in PostgreSQL?

Question

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 BoolD were true, I'd want 1001 or 9.

I have something in mind to the effect of:

SELECT
   CASE WHEN BoolD THEN 2^0 ELSE 0 END +
   CASE WHEN BoolC THEN 2^1 ELSE 0 END +
   CASE WHEN BoolB THEN 2^2 ELSE 0 END +
   CASE WHEN BoolA THEN 2^3 ELSE 0 END
FROM MyTable;

But I'm not sure if this is the best approach and seems rather verbose. Is there an easy way to do this?

Erwin Brandstetter · Accepted Answer · 2023-08-30T12:35:59.083

17

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 1 ELSE 0 END
     + (CASE WHEN boolc THEN 1 ELSE 0 END << 1)
     + (CASE WHEN boolb THEN 1 ELSE 0 END << 2)
     + (CASE WHEN boola THEN 1 ELSE 0 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 | (bitwise OR) instead of the + operator.
Individual test runs show basically the same performance for all five methods.

edited Aug 30 '23 at 12:35

answered Jan 29 '12 at 13:36

Erwin Brandstetter

605,456
145
1,078
1,228

1

This approach would work, but from what I can tell, the Npgsql driver will convert a `bit varying` type into an array of booleans, which is not what I want. Though I would need to try this to confirm, as I can't find it documented anywhere. – Mike Christensen Jan 29 '12 at 19:07
2

Just confirmed - Npgsql will convert `bit varying` into a string, that looks something like `"1001"` - So this approach, though probably valid for some scenarios, is not quite was I was looking for. – Mike Christensen Jan 29 '12 at 19:34
2

No, I wasn't *claiming*, but yes, I was indeed thinking of MySQL when offering my suggestion. And because of that, my initial assumption was actually correct. But I wasn't sure, and after Mike's reports that the idea wasn't working, I started thinking that `true` might *possibly* convert as `-1` in PostgreSQL, instead of `1` as in MySQL. It was later proved incorrect, so I eventually was reaffirmed in my initial guess. On the other note, I think your idea might work for Mike if you added conversion of the entire expression to `int`. – Andriy M Jan 30 '12 at 00:13
1

@MikeChristensen: See my amended answer for an integer solution. – Erwin Brandstetter Jan 30 '12 at 12:25
1

@AndriyM: Fair enough, I rephrased my answer accordingly. Also, you were on the right track for a solution that fits Mike's requirements. – Erwin Brandstetter Jan 30 '12 at 12:26
1

Thanks for the detailed response! Looks like there's all sorts of ways to do this, so it's just a matter of personal syntactic preference. – Mike Christensen Jan 30 '12 at 16:23
Very comprehensive! Would have upvoted this again if I could. – Andriy M Jan 30 '12 at 17:00

score 2 · Answer 2 · edited Jan 29 '12 at 09:12

2

Maybe like this:

SELECT
  (CASE WHEN BoolA THEN 1 ELSE 0 END << 0) +
  (CASE WHEN BoolB THEN 1 ELSE 0 END << 1) +
  (CASE WHEN BoolC THEN 1 ELSE 0 END << 2) +
  (CASE WHEN BoolD THEN 1 ELSE 0 END << 3) AS BitMask
FROM MyTable;

where << is the bitwise shift left operator.

edited Jan 29 '12 at 09:12

Mike Christensen

88,082
50
208
326

answered Jan 29 '12 at 08:49

Andriy M

76,112
17
94
154

This results in the error `operator does not exist: boolean << integer` – Mike Christensen Jan 29 '12 at 08:56
If I do `BoolA::int` then the bitshift works, but for some reason it's not behaving like I'm expecting.. – Mike Christensen Jan 29 '12 at 09:01
@MikeChristensen: Oh, I see. I expected booleans were converted to integers implicitly in PostgreSQL. Too presumptuous of me, sorry. Edited my answer, which now doesn't differ from yours too much. – Andriy M Jan 29 '12 at 09:02
@MikeChristensen: Re `BoolA::int` – possibly a boolean is converted to `int` as `-1`, not as `1`. – Andriy M Jan 29 '12 at 09:03
Ah I got it working. It was an order of operation thing, I needed parentheses :) – Mike Christensen Jan 29 '12 at 09:06
@MikeChristensen: I think you should post your own answer and accept it! – Andriy M Jan 29 '12 at 09:08
1

You're probably right, but I'll give you an upvote for the bitshift idea :) I think the only way to make it less verbose is to wrap it in a function. – Mike Christensen Jan 29 '12 at 09:10

score 1 · Answer 3 · answered Jan 29 '12 at 09:14

1

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;

answered Jan 29 '12 at 09:14

Mike Christensen

88,082
50
208
326

I think this is the best solution so far. So you are right, if someone comes up with a better one, that would definitely deserve accepting. – Andriy M Jan 29 '12 at 10:49

Can I convert a bunch of boolean columns to a single bitmap in PostgreSQL?

3 Answers3

Cast bit(n) to integer

Linked