Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 2

up vote 9 down vote accepted

This will do:

SET show = NOT show

TRUE values become FALSE

FALSE become TRUE and

UNKNOWN (NULL) stay UNKNOWN

share|improve this answer
    
Not "maybe", definitely ;) –  a_horse_with_no_name Mar 29 '12 at 6:36
1  
@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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.