Postgres masking bit

Question

I have this request

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

and I get the following error:

PGError: ERROR: operator does not exist: character changes and integer
TIP: the statement does not match the specified name and type of arguments. You may need to add explicit types.

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

Thanks!

Answer 1

Check out the docs for bit operators for Pg.

Essentially, & only works on two types (usually a bit or int), so model_mask should be CAST ed from varchar for something reasonable, like a bit or int:

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

You can find out what types the 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