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: (view raw, whole thread or download thread mbox)
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
>> 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));
smarlowe=# insert into abc values ('0010'),('90'),('66');
smarlowe=# alter table abc alter column c type numeric using c::numeric;

In response to


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-2017 The PostgreSQL Global Development Group