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=# Joshua D. Drake > > -- > Richard Huxton > Archonet Ltd > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
