Skip site navigation (1) Skip section navigation (2)

Re: Cast char to number

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: jd(at)commandprompt(dot)com
Cc: Richard Huxton <dev(at)archonet(dot)com>, rod(at)iol(dot)ie, Christine Penner <christine(at)ingenioussoftware(dot)com>, Bill Moran <wmoran(at)potentialtech(dot)com>, Postgres-General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Cast char to number
Date: 2010-02-24 20:35:52
Message-ID: dcc563d11002241235x363052afm8b22fe9433c3cc36@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, Feb 24, 2010 at 1:27 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> 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=#

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

In response to

Responses

pgsql-general by date

Next:From: Richard HuxtonDate: 2010-02-24 20:36:39
Subject: Re: Cast char to number
Previous:From: Bill MoranDate: 2010-02-24 20:34:05
Subject: Re: Cast char to number

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group