Re: Cast char to number

On Wed, Feb 24, 2010 at 1:27 PM, Joshua D. Drake <jd@commandprompt.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