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
