Re: ALTER TABLE ( smallinto -> boolean ) ...

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 table
ALTER COLUMN field1 TYPE boolean
USING CASE WHEN field1 = 0 THEN FALSE
WHEN field1 = 1 THEN TRUE
ELSE NULL
END;
--