Re: ALTER TABLE ( smallinto -> boolean ) ... - Mailing list pgsql-hackers

On Mon, 2005-08-29 at 20:15 -0300, Marc G. Fournier wrote:
> I have a table with several 'smallint' fields that I'd like to convert to 
> booleean ... the data in each is either 0 or 1, and:
> 
> # ALTER TABLE table ALTER COLUMN field1 type boolean;
> ERROR:  column "field1" cannot be cast to type "pg_catalog.bool"
> 
> Should this not work?  If not, is there a way to do it so that it will, 
> without having to reload the whole table?

development=# select '0'::smallint::boolean;
ERROR:  cannot cast type smallint to boolean

You were casting an unknown to boolean.

Anyway, USING is what you're looking for:

ALTER TABLE tableALTER COLUMN field1 TYPE boolean    USING CASE WHEN field1 = 0 THEN FALSE        WHEN field1 = 1 THEN
TRUE       ELSE NULL        END;
 
--