Take the 2-minute tour ×

There is a field show which is a boolean type in postgesql.

I want to write a sql to update that table, to toggle the value of show. If it's true, it becomes false, if it's false, it becomes true.

Is it possible?

share|improve this question
That's a stackoverflow question, not DBA.stackexchange. –  David Aldridge Jul 1 '13 at 14:27
add comment

2 Answers

up vote 9 down vote accepted

This will do:

SET show = NOT show

TRUE values become FALSE

FALSE become TRUE and


share|improve this answer
Not "maybe", definitely ;) –  a_horse_with_no_name Mar 29 '12 at 6:36
@a_horse_with_no_name: Yeah, but I had to write something to cover the 30 characters limitation. Since we already had TRUE and FALSE, a variation on UNKNOWN was appropriate :) –  ypercube Mar 29 '12 at 6:38
This is awesome, elegant, and exactly what I wanted. Ultra glad this isn't only possible MySQL. –  sudowned Sep 3 '12 at 4:15
add comment

The following will turn FALSE or NULL to TRUE, and TRUE to FALSE:

UPDATE tablename SET fieldname = NOT COALESCE( fieldname, 'f' ) WHERE keyvalue = ?

share|improve this answer
add comment

Your Answer


