I have a table that has a column storing some coefficients which require high precision. The type of the column is set to float4 and I need to change it to numeric.

But after altering the table, it changes the values I previously had stored in that column.

Eg: 0.0199999996 becomes 0.0200.

The command I used:

ALTER TABLE IF EXISTS table_name ALTER COLUMN column_name TYPE numeric(16, 15) USING column_name::numeric;

Any ideas on how I could change the column type but keep the previous values? I am using postgresql 10.4


The Postgres manual (quoted below) does a good job of explaining some of the rounding issues you might have using floating-point types.

With that in mind, and if we simply accept that the least significant digits of any floating-point type are merely approximate, you have a couple of options for conversion including all the approximated low-precision digits. I've used float4/float8 to keep consistency with your question but we could equally use real/float.:

select '0.0199999996'::float4;
| float4 |
| :----- |
| 0.02   |
select '0.0199999996'::float4::numeric;
| numeric |
| ------: |
|    0.02 |
select '0.0199999996'::float4::float8::numeric;
|            numeric |
| -----------------: |
| 0.0199999995529652 |
select '0.0199999996'::float4::text::numeric;
| numeric |
| ------: |
|    0.02 |
set extra_float_digits = 3;
select '0.0199999996'::float4::text::numeric;
|      numeric |
| -----------: |
| 0.0199999996 |

db<>fiddle here

8.1.3. Floating-Point Types

The data types real and double precision are inexact, variable-precision numeric types. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.

Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:

  • If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

  • If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.

  • Comparing two floating-point values for equality might not always work as expected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.