Re: casting from integer to boolean

On Wed, 26 Mar 2008 18:03:06 -0500,
Seb <spluque(at)gmail(dot)com> wrote:

> On Wed, 26 Mar 2008 22:46:08 +0000,
> Sam Mason <sam(at)samason(dot)me(dot)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