Re: Cast char to number

In response to "Joshua D. Drake" <jd(at)commandprompt(dot)com>:

> On Wed, 2010-02-24 at 20:22 +0000, Richard Huxton wrote:
> > 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.
>
> That won't work in this case. char() can't be cast to int/numeric. Not
> only that it isn't possible to clean up the data in table because char
> automatically pads.
>
> postgres=# alter table foo alter column id type numeric;
> ERROR: column "id" cannot be cast to type "pg_catalog.numeric"
> postgres=#

Remember that what comes after the using clause can be arbitrarily
complex (I have written ALTER TABLE statements with USING CASE ... that
are pages and pages long to fix data consistency problems in the
same step as correcting a poorly chosen column type ;)

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/