Thread: Consecutive operators not recognized without whitespace

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

Chris Angelico <rosuav@gmail.com> writes:
> 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?

Yes.  Postgres allows multi-character operator names, so you need a
space in general.  There are exceptions for certain single-character
operator names that are defined in the SQL spec (eg, a+-b is required
by spec to parse as two operators), but & and ~ are not in that list.
You can find the details in the fine manual, probably somewhere in
the "Syntax" chapter.

            regards, tom lane