Re: casting from integer to boolean

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