Thread: Change column type to numeric

Forgive me if this question has an obvious answer, I'm sorta new to posgresql.

I have a table that's already populated with quite a bit of records.
I'd like to alter a column called "amount" from character varying to
numeric, so I don't have to re-load all of my data sets.

I've already dropped the column default.  When attempting to change
the column type, I get:

test=# alter table foo alter column amount type numeric(10,2) USING
cast(amount AS numeric);
ERROR:  invalid input syntax for type numeric: ""

I'm assuming that it's trying to cast a blank value as numeric and
failing.  Does anyone know of an easy way to work around this?

Thanks!

--Jake

On Sun, Feb 10, 2008 at 11:37:45AM -0700, Jake Franklin wrote:
> test=# alter table foo alter column amount type numeric(10,2) USING
> cast(amount AS numeric);
> ERROR:  invalid input syntax for type numeric: ""
>
> I'm assuming that it's trying to cast a blank value as numeric and
> failing.  Does anyone know of an easy way to work around this?

You could convert the empty strings to NULL:

USING cast(nullif(amount, '') AS numeric)

--
Michael Fuhr