Re: Cast char to number

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