simple update on boolean

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.