Обсуждение: simple update on boolean
Most honourable members of the list, this is a simple one, but I can't find the solution ( probably a forest/tree problem). update table set bolean_column = set_it_to_its_inverse where fk = some_value; or join me in the dark forest cedric
am Thu, dem 06.12.2007, um 10:25:26 +0100 mailte Cedric Boudin folgendes: > Most honourable members of the list, > > this is a simple one, but I can't find the solution ( probably a > forest/tree problem). > > update table set bolean_column = set_it_to_its_inverse where fk = > some_value; test=# create table forrest (id int, b bool); CREATE TABLE test=*# insert into forrest values (1,'f'::bool); INSERT 0 1 test=*# insert into forrest values (2,'t'::bool); INSERT 0 1 test=*# update forrest set b = case when b then 'f'::bool else 't'::bool end; UPDATE 2 test=*# select * from forrest ; id | b ----+--- 1 | t 2 | f (2 rows) Okay? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thu, 6 Dec 2007, A. Kretschmer wrote: > update table set bolean_column = set_it_to_its_inverse where fk = > some_value; I've used: update table set bolean_column = not boolean_column where fk = some_value; which has worked for me well. Daniel
Cedric Boudin wrote: > > Most honourable members of the list, > > this is a simple one, but I can't find the solution ( > probably a forest/tree problem). > > update table set bolean_column = set_it_to_its_inverse where > fk = some_value; > I am usually a newbie around here, but this is one that I can answer :-) update table set boolean_column = not boolean_column where fk = some_value HTH Frank Millman
Cedric Boudin wrote: > Most honourable members of the list, > > this is a simple one, but I can't find the solution ( probably a > forest/tree problem). > > update table set bolean_column = set_it_to_its_inverse where fk = > some_value; > > or join me in the dark forest > > cedric > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > It was indeed a forest problem. both: set bolean_column= not bolean_column and set bolean_column= case when bolean_column then 'f'::bool else 't'::bool end; do work perfectly. Thks for bringing me the light in the forest. working late isn't that effective !!! cedric
On Thu, 06 Dec 2007 13:38:31 +0100 Cedric Boudin <cedric@dreamgnu.com> wrote: > It was indeed a forest problem. > both: > > set bolean_column= not bolean_column > and > set bolean_column= case when bolean_column then 'f'::bool else > 't'::bool end; > > do work perfectly. What if boolean_column is NULL? btw set bolean_column= not bolean_column works "as expected". template1=# select (not 't'::boolean),(not 'f'::boolean),(not NULL::boolean); ?column? | ?column? | ?column? ----------+----------+---------- f | t | (1 riga) -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo wrote: > On Thu, 06 Dec 2007 13:38:31 +0100 > Cedric Boudin <cedric@dreamgnu.com> wrote: > > >> It was indeed a forest problem. >> both: >> >> set bolean_column= not bolean_column >> and >> set bolean_column= case when bolean_column then 'f'::bool else >> 't'::bool end; >> >> do work perfectly. >> > > What if boolean_column is NULL? > > btw set bolean_column= not bolean_column works "as expected". > > template1=# select (not 't'::boolean),(not 'f'::boolean),(not > NULL::boolean); > > ?column? | ?column? | ?column? > ----------+----------+---------- > f | t | > (1 riga) > If it was null before it has to be null afterwards (IMHO). If you don't want to have null, take care of it somewhere else but not here. cedric
On Thu, 06 Dec 2007 14:53:13 +0100 Cedric Boudin <cedric@dreamgnu.com> wrote: > > What if boolean_column is NULL? > > > > btw set bolean_column= not bolean_column works "as expected". > > > > template1=# select (not 't'::boolean),(not 'f'::boolean),(not > > NULL::boolean); > > > > ?column? | ?column? | ?column? > > ----------+----------+---------- > > f | t | > > (1 riga) > If it was null before it has to be null afterwards (IMHO). > If you don't want to have null, > take care of it somewhere else but not here. That is the "as expected" part. The "case" case, other than being more verbose, do more than what I would expect since all NULL are converted to t. template1=# select case when NULL then 'f'::boolean else 't'::boolean end; case ------ t (1 riga) -- Ivan Sergio Borgonovo http://www.webthatworks.it
You could use a COALESCE instead of a case statement for simple case like this. The below will treat a NULL as false and then when you do not it becomes true. So NULLS will be set to true UPDATE boolean_column SET boolean_column = NOT COALESCE(boolean_column, false) hope that helps, Regina -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ivan Sergio Borgonovo Sent: Thursday, December 06, 2007 10:19 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] simple update on boolean On Thu, 06 Dec 2007 14:53:13 +0100 Cedric Boudin <cedric@dreamgnu.com> wrote: > > What if boolean_column is NULL? > > > > btw set bolean_column= not bolean_column works "as expected". > > > > template1=# select (not 't'::boolean),(not 'f'::boolean),(not > > NULL::boolean); > > > > ?column? | ?column? | ?column? > > ----------+----------+---------- > > f | t | > > (1 riga) > If it was null before it has to be null afterwards (IMHO). > If you don't want to have null, > take care of it somewhere else but not here. That is the "as expected" part. The "case" case, other than being more verbose, do more than what I would expect since all NULL are converted to t. template1=# select case when NULL then 'f'::boolean else 't'::boolean end; case ------ t (1 riga) -- Ivan Sergio Borgonovo http://www.webthatworks.it ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.