Re: alter column datatype with cast

On Thu, Dec 29, 2005 at 09:46:10PM +0100, Klein Balzs wrote:
> I had to change the datatype of a column from text to integer. The column
> contained integers (obviously stored as text).
>
> When I tried to change the datatype of the column I got an error message
> saying that the column can not be cast to integer:
>
> Operation : ALTER TABLE "public"."subjectgroupcondition" ALTER COLUMN
> "param1" TYPE INTEGER
> Result : "ERROR: column "param1" cannot be cast to type
> "pg_catalog.int4""

Use the USING clause:

ALTER TABLE subjectgroupcondition
ALTER COLUMN param1 TYPE integer USING param1::integer;

> However when I created an other integer column in the table and updated it
> from the text column there was no problem casting the data:
> Operation : UPDATE public.subjectgroupcondition SET param2 = cast(param1 as
> integer);
> Result : "OK."
>
> Since pg knows that it should cast the data and it can cast it I think I
> should have been able to change the datatype in the first instance. Maybe
> this behaviour has a good reason but I don't know what it is.

Some casts can be done implicitly and some not. For more information
see the CREATE CAST and Type Conversion documentation:

http://www.postgresql.org/docs/8.1/interactive/sql-createcast.html
http://www.postgresql.org/docs/8.1/interactive/typeconv.html

--
Michael Fuhr