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

Re: Cast char to number

From: Richard Huxton <dev(at)archonet(dot)com>
To: jd(at)commandprompt(dot)com
Cc: 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:36:39
Message-ID: 4B858DD7.9080507@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
On 24/02/10 20:27, Joshua D. Drake 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"

Well if it's actually "char(10)" or somesuch you need to do a little 
more I grant you (though not much). I was assuming varchar myself.

richardh=> CREATE TABLE intastext (i char(10));
CREATE TABLE
richardh=> INSERT INTO intastext (i) VALUES ('1'), 
('02'),('3.0'),('3.5'),('X');
INSERT 0 5
richardh=> SELECT * FROM intastext ;
      i
------------
  1
  02
  3.0
  3.5
  X
(5 rows)

richardh=> ALTER TABLE intastext ALTER COLUMN i TYPE integer USING 
i::text::numeric::integer;
ERROR:  invalid input syntax for type numeric: "X"
richardh=> DELETE FROM intastext WHERE i = 'X';
DELETE 1
richardh=> ALTER TABLE intastext ALTER COLUMN i TYPE integer USING 
i::text::numeric::integer;
ALTER TABLE
richardh=> SELECT * FROM intastext ;
  i
---
  1
  2
  3
  4
(4 rows)

Of course "USING" can have any expression to convert the type.

richardh=> CREATE FUNCTION my_map(char(10)) RETURNS integer AS $$ SELECT 
CASE WHEN $1>='0' AND $1<='9' THEN $1::numeric::integer ELSE -999 END; 
$$ LANGUAGE SQL;
CREATE FUNCTION
richardh=> ALTER TABLE intastext ALTER COLUMN i TYPE integer USING 
my_map(i);ALTER TABLE
richardh=> SELECT * FROM intastext ;
   i
------
     1
     2
     3
     4
  -999
(5 rows)

-- 
   Richard Huxton
   Archonet Ltd

In response to

pgsql-general by date

Next:From: JeffDate: 2010-02-24 20:36:53
Subject: Re: Curious plperl behavior
Previous:From: Scott MarloweDate: 2010-02-24 20:35:52
Subject: Re: Cast char to number

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