0

With one boolean, it is simply 0 and 1, and it's straight-forward to query.

With two booleans, it becomes necessary to specify what each number means, ex:

  • 0 F-F
  • 1 F-T
  • 2 T-F
  • 3 T-T

Here it's easy to query for all entries where the first boolean is T by foo > 1. But what if it wants all rows where the second boolean is T (while still using the database index efficiently and without using OR)?

What if there are six booleans, and it wants to query for all entries where the first and third boolean are T, and the sixth boolean is F? Does there exist any design pattern to store booleans in an integer and allow this kind of querying?

3

You can query a bit array using bitwise operators, and you can store bit arrays in an integer type. Bitwise operators are not an ANSI SQL specified feature, but most RDBMS have implemented them. Some RDBMS have special support for bit arrays.

Querying

You can search for truth by bitwise and-ing with the number that has that bit turned on.

For example:

WHERE col & 2 = 2

Would find all rows where the second boolean in the bit array is true. Here is an illustration of what it's doing:

  01010010  bit array
& 00000010  2
= 00000010  2 -- true

  01011100  bit array
& 00000010  2
= 00000000  0 -- false

Updating

To set a given bit you use the bitwise or operation

For example:

SET col = col | 2

Would update col to set the second bit to true.

Note it may not be a good longterm decision to store a bit array in the database. You will probably be better served by storing several boolean columns because you may have better control over indexing and it will be easier for others to understand what you're querying. See the recommendations in this answer.

Edit: Since you're using Postgres, you may want to look at their bit-string type. You can use the same bitwise operators, but it's a bit easier to read and express using the bit string syntax.

  • Yup I just found out about bit-strings, on first glance it looks like it's less efficient than an integer though, I'm under the impression that it requires at least 5 bytes: 4 to tell how long the bit-string is, and at least 1 byte for the actual information? – dvtan Aug 3 '17 at 2:09
  • @dtgq yes I think that's right. The benefit is that it can have a flexible size (with bit varying) – Samuel Aug 3 '17 at 2:10
  • Also re: using boolean columns, a con of that is there would be verbose conditionals in the query, i.e. deleted==False & hidden==False & disabled==False etc. – dvtan Aug 3 '17 at 2:13
  • 1
    @dtgq: Being explicit in that way is usually far better for understanding what is being done than a bit-condition like col & 14 == 12 – Bart van Ingen Schenau Aug 3 '17 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.