Bit masking in Postgres

Question

I have this query

SELECT * FROM "functions" WHERE (models_mask & 1 > 0)

and the I get the following error:

PGError: ERROR: operator does not exist: character varying & integer
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The models_mask is an integer in the database. How can I fix this.

Thank you!

Answer 1

Check out the docs on bit operators for Pg.

Essentially & only works on two like types (usually bit or int), so model_mask will have to be CASTed from varchar to something reasonable like bit or int:

models_mask::int & 1 -or- models_mask::int::bit & b'1'

You can find out what types an operator works with using \doS in psql

pg_catalog | &    | bigint                      | bigint                      | bigint                      | bitwise and
pg_catalog | &    | bit                         | bit                         | bit                         | bitwise and
pg_catalog | &    | inet                        | inet                        | inet                        | bitwise and
pg_catalog | &    | integer                     | integer                     | integer                     | bitwise and
pg_catalog | &    | smallint                    | smallint                    | smallint                    | bitwise and

Here is a quick example for more information

# SELECT 11 & 15 AS int, b'1011' & b'1111' AS bin INTO foo;
SELECT

# \d foo
      Table "public.foo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 int    | integer | 
 bin    | "bit"   | 

# SELECT * FROM foo;
 int | bin  
-----+------
  11 | 1011