Greetings! Apologies if this is the wrong place to ask this question.
I'm using Postgres (just upgraded from 9.1.1 to 9.1.2 but I don't
think it's significant) for some work that includes maintaining a
bitfield. The most common operations on this field are:
1) Set a bit:
UPDATE tablename SET flags=flags|8 WHERE id=1234
2) Clear a bit:
UPDATE tablename SET flags=flags&~8 WHERE id=1234
However, the second statement fails; the Postgres parser tries to find
a single "&~" operator, instead of using the unary ~ and then the
binary &. Putting a space between the two symbols makes it work fine,
but I was wondering: Is this intentional behavior? It seems odd.
I discovered the problem after porting from MySQL, in which the
statement had worked fine.
The phenomenon can be noted easily in psql with:
postgres=# select 31&~8;
ERROR: operator does not exist: integer &~ integer
postgres=# select 31& ~8;
?column?
----------
23
I could understand weird behavior with user-defined operators, but
these are standard operators. It seems odd that the theoretical
possibility of an &~ operator trumps the existing & and ~ operators.
Again, apologies if I'm raising this in the wrong place; it's probably
not a bug, but it may be that it's a docs issue.
Chris Angelico