On Wed, Feb 24, 2010 at 1:27 PM, Joshua D. Drake <jd@xxxxxxxxxxxxxxxxx> 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" > postgres=# The example given works fine for me: smarlowe=# create table abc (c char(10)); CREATE TABLE smarlowe=# insert into abc values ('0010'),('90'),('66'); INSERT 0 3 smarlowe=# alter table abc alter column c type numeric using c::numeric; ALTER TABLE -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
