Re: Cast char to number

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=#

Joshua D. Drake

>
> --
> Richard Huxton
> Archonet Ltd
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.