How to toggle a boolean in postgres in one query

Question

I'm trying to update a row in a postgres table. I want to toggle a boolean field.

Instead of first checking what the value is and updating the field with the opposite, I was wondering if there was a single query to update the field.

I found a solution for MySQL, but its not working for postgres:

 UPDATE table SET boolean_field = !boolean_field WHERE id = :id

 Error: Operator does not exist. ! boolean

Is there an similar syntax in postgres?

Answer 1

Use NOT:

UPDATE table SET boolean_field = NOT boolean_field WHERE id = :id

When the old value equals FALSE then it turns into TRUE and visa versa. A NULL field won't flip, there is nothing to flip to.

Complete example:

CREATE TABLE test(id serial, boolean_field boolean);

INSERT INTO test(boolean_field) 
VALUES(null),(false), (true) 
RETURNING *;

AND run the test:

UPDATE test
SET boolean_field = NOT boolean_field 
RETURNING *;