PostgreSQL how to use bitwise operator |=

Question

I want to execute the following update query at PostgreSQL db but it does not work and gives syntax error.

Query as follows

update wl_user set role_bitmask= role_bitmask|=1 where email='faisal@gmail.com'

Problem seems to be with |= operator, does anybody have idea how to use |= operator in PostgreSQL?

Following is the error.

[Err] ERROR:  operator does not exist: integer |= integer
LINE 1: ...pdate wl_user set role_bitmask=role_bitmask|=1 where ...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

score 3 · Accepted Answer · edited May 23 '17 at 10:28

Instead of role_bitmask= role_bitmask|=1 you must use role_bitmask=role_bitmask|1 which also makes some sense if you think what |= does and what | does.

To clarify: PostgreSQL has a lot of operators and allow you to define new operators. But in your case this does not matter for two reasons:

The manual for UPDATE says about the syntax (abbreviated): UPDATE ... table ... SET column = { expression | DEFAULT }. This means, that the = is mandatory and not any of the normal operators. Therefore there is no |= and no &= .
Among all those extensible operators there is no assignment operator. Assignment is handled in a special way for each case. Have a look at this question for some hints.

Regarding your question: An expression like role_bitmask= role_bitmask|=1 make eyebrows raise in every language :-)

Summary: You have to use the long form ... SET column = colum | bitmask.

PostgreSQL how to use bitwise operator |=

1 Answers1