Re: Cast char to number

On Wed, 2010-02-24 at 13:35 -0700, Scott Marlowe wrote:

> >> 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=#
>
> The example given works fine for me:
>
> smarlowe=# create table abc (c char(10));
> CREATE TABLE
> smarlowe=# insert into abc values ('0010'),('90'),('66');
> INSERT 0 3
> smarlowe=# alter table abc alter column c type numeric using c::numeric;
> ALTER TABLE
>

Well that is interesting. I would have thought it would have failed
because of the padding...

Joshua D. Drake

--
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.