This is what I did. set all blank columns to '0' because they were causing errors. alter table T alter column a type integer using a::integer That worked perfectly. Thanks everyone for the help. Christine At 12:46 PM 24/02/2010, Scott Marlowe wrote:
On Wed, Feb 24, 2010 at 1:43 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx> writes: >> On Wed, 2010-02-24 at 20:22 +0000, Richard Huxton wrote: >>> ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer; > >> 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" > > That just indicates that there isn't an *implicit* coercion from char to > numeric. With a USING clause you can specify an arbitrary conversion. > > I agree with the recommendation to test it out before actually doing > the table change though. Maybe look at the results of > > select id, id::numeric from your_table > > to see if it looks sane for all the different data formats in the > column. And if the database is mostly sitting idle (i.e. no other users) you can always just do begin; alter table yada; test how it went and then commit or rollback. For the OP: It's a bad idea to do that kind of stuff in production cause you'll put a lock on the table others will have to wait for. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
