Matching bitmasks using bitstrings (instead of ints) in SQL

Question

I found a great resource here ( Comparing two bitmasks in SQL to see if any of the bits match ) for doing searches in a SQL database, where you're storing data with multiple properties using bit masks. In the example, though, all the data is stored as ints and the where clause seems to only work with ints.

Is there an easy way to convert a very similar test case to use full bitstrings instead? So instead of an example like:

with test (id, username, roles)
AS
(
    SELECT 1,'Dave',1
    UNION SELECT 2,'Charlie',3
    UNION SELECT 3,'Susan',5
    UNION SELECT 4,'Nick',2
)
select * from test where (roles & 7) != 0 

instead having something like:

with test (id, username, roles)
AS
(
    SELECT 1,'Dave',B'001'
    UNION SELECT 2,'Charlie',B'011'
    UNION SELECT 3,'Susan',B'101'
    UNION SELECT 4,'Nick',B'110'
)
select * from test where (roles & B'001') != 0 

I can convert back and forth, but it's easier to visualize with the actual bitstrings. For my simple conversion (above) I get an error that the operator doesn't work for bitstrings. Is there another way to set this up that would work?

Answer 1

One way would be to just use a bit string on the right side of the expression, too:

WITH test (id, username, roles) AS (
   VALUES
     (1,'Dave',B'001')
    ,(2,'Charlie',B'011')
    ,(3,'Susan',B'101')
    ,(4,'Nick',B'110')
   )
SELECT *, (roles & B'001') AS intersection
FROM   test
WHERE  (roles & B'001') <> B'000';

Or you can cast an integer 0 to bit(3)

...
WHERE  (roles & B'001') <> 0::bit(3);

You may be interested in this related answer that demonstrates a number of ways to convert between boolean, bit string and integer:
Can I convert a bunch of boolean columns to a single bitmap in PostgreSQL?

Be aware that storing the data as integer can save some space. integer needs 4 bytes for up to 32 bit of information, while - I quote the manual at said location:

A bit string value requires 1 byte for each group of 8 bits, plus 5 or 8 bytes overhead depending on the length of the string [...]