datatype conversion in postgresql 7.4.1

[supersucker]

hi everybody,

i got the following problem:

i have a database running on a postgresqlserver version 7.4.1, in the database is a table with 4 columns containing
float-numbers. what i want to do now is, to convert the type of those columns from float to numeric, because
i want to limit the positions after the decimal point to 2.

what works fine in v. 8:

either

alter table fragment alter column x type numeric(15,2)

or

update fragment set x = round(x,2)

but both wont work with version 7.4.1!

when i look into the documentation it seems to say that in 7.4.1 round() can only round to a given number of positions after the decimal point when used with a numeric type, but not with floats!

and if i try something like:

alter table fragment alter column x type numeric(15,2)

i get an syntax error, so this seems to be no feature in 7.4.1 (didnt find anything like that in the docs too....)

what can i do to solve this? there must be some way in postgresql 7.4.1, if there is no way to do that, i have to
build up the whole database from scratch........there must be a way to convert float to numeric values in 7.4.1 ,
but i dont have a single clue...

thx for any help in advance

 

[rycamor]

Yes, 8.0 has greatly expanded your options for ALTER TABLE, which are not available for 7.4.

But, Postgresql has a very complete set of functions to format data of any type:

http://www.postgresql.org/docs/7.4/interactive/functions-formatting.html

Also, PostgreSQL has a very convenient postfix notation for casting, using the '::' operator:

SELECT 12.100023::numeric(4,2);

produces output of 12.10.

Using this, you can use SELECT INTO to create a temporary table which duplicates your original table but casts that column as numeric. Then, inside a transaction, drop the original table and use simple SELECT INTO to recreate the original table with the new column definition. (Then, of course, you will need to recreate any constraints)

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html