On Wed, 26 Mar 2008 18:03:06 -0500,
Seb <spluque@gmail.com> wrote:
> On Wed, 26 Mar 2008 22:46:08 +0000,
> Sam Mason <sam@samason.me.uk> wrote:
[...]
>> You could turn the problem around and make the bool columns into ints
>> (which should be a simple search-and-replace, I hope) and then write
>> something (again hopefully simple) to turn them all back into bools.
>> I.e. lots of:
>> ALTER TABLE my_table ALTER var_bool TYPE bool USING var_bool::bool;
> Yes! Good idea, I think I can collect the names of the tables and
> columns with boolean fields and then use that as you say. Thanks
> everybody.
I found out that the above works only if int4 is used. I had assumed
smallint should have worked, but got this error:
ERROR: cannot cast type smallint to boolean
Someone posted (in an older thread) an alternative to deal with this:
---<---------------cut here---------------start-------------->---
ALTER TABLE my_table
ALTER COLUMN var_bool TYPE boolean
USING CASE WHEN var_bool = 0 THEN FALSE
WHEN var_bool = 1 THEN TRUE
ELSE NULL
END;
---<---------------cut here---------------end---------------->---
--
Seb
