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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2010-02-24 20:36:39 | Re: Cast char to number |
Previous Message | Bill Moran | 2010-02-24 20:34:05 | Re: Cast char to number |