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;
--