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?
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 *;
UPDATE table_name
SET bool_col = NOT(COALESCE(bool_col, FALSE))
WHERE ...
