Try this:
1) Add a new column of type DECIMAL(5,2)
2) update the table setting the new column with the value in the old
column.
3) rename the old column to something not used
4) rename the new colum to the name of the old column
5) wait for a later version where you can drop column, or just leave the
data there and remove any constraints on it. Maybe update the values to
NULL if there is alot of data.
scratch=# BEGIN;
BEGIN
scratch=# create table foo (decval varchar, id serial);
CREATE
scratch=# insert into foo values('0.4');
INSERT 153524 1
scratch=# insert into foo values('1.45');
INSERT 153525 1
scratch=# insert into foo values('-4.2');
INSERT 153526 1
scratch=# insert into foo values('55');
INSERT 153527 1
ALTER TABLE foo ADD COLUMN decval_new decimal(5,2);
ALTER
-- Note: You probably want to find a better way to do this cast.
-- I couldn't find one quickly that would do the job except this.
-- The problem being that the conversion is imprecise.
scratch=# update foo set decval_new = decval::text::float::decimal;
UPDATE 4
scratch=# SELECT * from foo;
decval | id | decval_new
--------+----+------------
0.4 | 1 | 0.40
1.45 | 2 | 1.45
-4.2 | 3 | -4.20
55 | 4 | 55.00
(4 rows)
scratch=# ALTER TABLE foo RENAME COLUMN decval TO decval_old; ALTER
TABLE foo RENAME COLUMN decval_new TO decval;
ALTER
ALTER
scratch=# SELECT * from foo;
decval_old | id | decval
------------+----+--------
0.4 | 1 | 0.40
1.45 | 2 | 1.45
-4.2 | 3 | -4.20
55 | 4 | 55.00
(4 rows)
scratch=# END;
COMMIT
On Wed, 2002-07-10 at 18:16, Kevin Brannen wrote:
> As we transition our database, I'm finding the need to change columns
> types. I'm also finding this to be a major PITA.
>
> I have a column that's a something like:
>
> create table stuff
> (
> ...
> rate varchar(7),
> ...
> );
>
> However that doesn't allow math-like functions (e.g. avg), so it's gotta
> be changed. I'd like to do something on the order of:
>
> alter table stuff modify rate DECIMAL(5,2);
>
> but that doesn't work. I've scoured the docs and can't seem to find
> anything other than:
>
> 1. create new table with changed columns,
> 2. copy data over,
> 3. remove old table,
> 4. rename new table to old table
>
> Please tell me there's an easier way! And of course how. :-)
>
> Also, I can't find a "alter table drop column NAME" to remove a column
> either.
>
> TIA,
> Kevin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
