When I was writing SQL today, I found that there is a table in PG that has an area field set to a string type, so the sum function cannot be used in statistics, so I need to convert the string into a double type and then add it.
select sum(to_number(mj,9999.99)) as amountmj
from table group by area
Among them, 9999.99 means that no matter whether the value of the mj field or the value of amountmj cannot exceed 9999.99, and two decimal places are retained.
Supplement: Postgresql scientific notation method to convert floating point or string
Scientific Counting Method to Float Point
select '2.93985E-6'::double precision
2.93985e-06
The display is still in the form of scientific counting, and there is no problem in participating in the calculation
Scientific notation method to string
select '2.93985E-6'::decimal::text
0.00000293985
Scientific notation method to floating point-control accuracy
-- The control accuracy is 6 bits
select ROUND('2.93985E-6'::decimal, 6)
0.000003
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.