CODEWITHSUNDEEP
databasepostgresqlboolean
stUrb
stUrb

Reputation: 6842

How to toggle a boolean in postgres in one query

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?

Upvotes: 24

Views: 27860

Answers (2)

Jono
Jono

Reputation: 61

UPDATE table_name 
  SET bool_col = NOT(COALESCE(bool_col, FALSE)) 
WHERE ...

Upvotes: 6

Frank Heikens
Frank Heikens

Reputation: 127406

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 *;

Upvotes: 44

Related Questions