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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff | 2010-02-24 20:36:53 | Re: Curious plperl behavior |
Previous Message | Scott Marlowe | 2010-02-24 20:35:52 | Re: Cast char to number |