Re: Cast char to number

This is what I did.

set all blank columns to '0' because they were causing errors.
alter table T alter column a type integer using a::integer

That worked perfectly.

Thanks everyone for the help.

Christine

At 12:46 PM 24/02/2010, Scott Marlowe wrote:
>On Wed, Feb 24, 2010 at 1:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> >> On Wed, 2010-02-24 at 20:22 +0000, Richard Huxton wrote:
> >>> ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;
> >
> >> 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"
> >
> > That just indicates that there isn't an *implicit* coercion from char to
> > numeric. With a USING clause you can specify an arbitrary conversion.
> >
> > I agree with the recommendation to test it out before actually doing
> > the table change though. Maybe look at the results of
> >
> > select id, id::numeric from your_table
> >
> > to see if it looks sane for all the different data formats in the
> > column.
>
>And if the database is mostly sitting idle (i.e. no other users) you
>can always just do
>
>begin;
>alter table yada;
>
>test how it went and then commit or rollback.
>
>For the OP: It's a bad idea to do that kind of stuff in production
>cause you'll put a lock on the table others will have to wait for.
>
>--
>Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general