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

From: Rod Taylor <pg(at)rbt(dot)ca>
To: "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: ALTER TABLE ( smallinto -> boolean ) ...
Date: 2005-08-29 23:33:31
Message-ID: 1125358411.13034.103.camel@home
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2005-08-29 23:49:39 Re: ALTER TABLE ( smallinto -> boolean ) ...
Previous Message David Fetter 2005-08-29 23:24:45 Re: ALTER TABLE ( smallinto -> boolean ) ...

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2005-08-29 23:49:39 Re: ALTER TABLE ( smallinto -> boolean ) ...
Previous Message David Fetter 2005-08-29 23:24:45 Re: ALTER TABLE ( smallinto -> boolean ) ...