1

We have an existing column(type- double precision) in our postgres table and we want to convert the data type of that column to numeric, we've tried the below approaches but all of them had truncation/data loss on the last decimal positions.

  1. directly converting to numeric
  2. converting to numeric with precision and scale
  3. converting to text and then to numeric
  4. converting to text only

The data loss I mentioned looks like this for eg: if we have a value 23.291400909423828, then after altering the column datatype that value is converted to 23.2914009094238 resulting in loss of the last 2 decimal places.

note: This is happening only if the value has more than 13 decimals(values right to the decimal point)

Jagadeesh
  • 119
  • 2
  • 7
  • Initially we faced this problem - https://stackoverflow.com/questions/68806339/psycopg2-is-not-reading-fetching-the-exact-float-value-with-full-decimals-from-p?noredirect=1#comment121683972_68806339 and then we realized that changing the db column datatype to numeric would convert values to Decimal in Python which holds better precision compared to float in Python. – Jagadeesh Aug 27 '21 at 11:40
  • https://www.postgresql.org/docs/current/datatype-numeric.html -->> You should `SET extra_float_digits=3;` – wildplasser Aug 27 '21 at 12:43
  • Your problem seems to be caused by python or the psycopg middleware. This cannot be solved by postgres. – wildplasser Aug 27 '21 at 13:27
  • @wildplasser read the comments in the previous question and you will see that is not true: `show extra_float_digits ; 3, select 21.291400909423828::float8::numeric; 21.2914009094238` – Adrian Klaver Aug 27 '21 at 15:22
  • I'm not seeing 3) failing: `show extra_float_digits ; 1 ,select 21.291400909423828::float8::text::numeric; 21.291400909423828` – Adrian Klaver Aug 27 '21 at 15:24

1 Answers1

3

One way to possibly do this:

show extra_float_digits ;
 extra_float_digits 
--------------------
 3

create table float_numeric(number_fld float8);
insert into float_numeric values (21.291400909423828), (23.291400909422436);
select * from float_numeric ;
     number_fld     
--------------------
 21.291400909423828
 23.291400909422435

alter table float_numeric alter COLUMN number_fld type numeric using number_fld::text::numeric;

\d float_numeric 
             Table "public.float_numeric"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 number_fld | numeric |           |          | 

select * from float_numeric ;
     number_fld     
--------------------
 21.291400909423828
 23.291400909422435
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28