Re: Change column type to numeric

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