Re: Cast char to number

On 24/02/10 20:06, Raymond O'Donnell wrote:
> However, to address your immediate problem, you could try something like
> this:
>
> (i) Create a new column of type numeric or integer as appropriate.
> (ii) update your_table set new_column = CAST(trim(both ' 0' from
> old_column) as numeric)
> (iii) Drop the old column, as well as any constraints depending on it.

Or, in any recent version of PG you can do this via ALTER TABLE
http://www.postgresql.org/docs/8.4/static/sql-altertable.html

ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;

You might want to clean up the values before doing this.

--
Richard Huxton
Archonet Ltd