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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
