flipping boolean value in postgresql

Question

I have a Boolean type field in my table (PostgreSQL 9.0)

Is there a way to write a query that update this field to the opposite value without knowing what is currently there?

if it's True then it will be updated to False.

if it's False then it will be updated to True.

Basically I'm asking if there is a mechanism that allows treating Boolean type same as 1 bit. For example in C if x can be {1,0} you can simply write x=!x and if it was 0 it will be 1, if it was 1 it will be 0. No need for IF statment etc...

Kirill Slatin · Accepted Answer · 2015-08-16 18:05:47Z

8

Why not to use operator NOT?

Update tableName
set c = NOT c 
where ...

http://www.postgresql.org/docs/current/static/functions-logical.html

Example in SqlFiddle

edited Aug 16, 2015 at 18:05

answered Aug 16, 2015 at 5:50

Kirill Slatin

6,1823 gold badges21 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erwin Brandstetter Over a year ago

It's a syntax error to table-qualify target columns in the SET clause. Must be: SET c = NOT tablename.c

Kirill Slatin Over a year ago

@John, yes there was a syntax error in initial version of my answer, which was pointed out in comments. set tableName.c = is not legal way. Even when from clause is present it is only possible to reference fields of table declared in update clause for changing values. So both works set c = not tableName.c and set c = not c. You can see sqlfiddle for a working solution and play with it to see options

Collectives™ on Stack Overflow

flipping boolean value in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related