Thread: Reg: Changing Column type

"sandhya" <sandhyar@amiindia.co.in> writes:
> When i am tring to change the column type from character/Varchar to =
> "int" i am getting error like,
> ERROR:  column "fname" cannot be cast to type "int4"

If this is a pre-8.0 PG version, try casting to text and then int4.

If 8.0 or later, it should work as is --- would you show us exactly
what you did?

            regards, tom lane

On Tue, Nov 15, 2005 at 17:22:16 +0530,
  sandhya <sandhyar@amiindia.co.in> wrote:
> Hi,
>
> When i am tring to change the column type from character/Varchar to "int" i am getting error like,
>
> ERROR:  column "fname" cannot be cast to type "int4"
>
> I can able to change the column type to "int " if the old datatype is double etc.
> Even a "int" datatype also i can able to conver to "char"
> and why not viceversa....Please tell me where the problem is.
> Suggest me what to do to convert a "char" datatype to "int".

One way to do this is to add a new int column. Use a query to insert data into
the new column using to_number allow with whatever you need to handle strings
that aren't numbers.
Then delete the old column.
Then rename the new column to be the original name.

In at least 8.1 there is a USING clause which allows you to specify a
conversion function which lets you do the above in one step.
I don't know when that feature was added though.