Incorrect value for float8 when loaded with COPY (gpcopy and gprestore)

Products

• VMware Tanzu Greenplum

Issue/Introduction

During COPY, the float value is changed.  

Steps: 

1. Create a table with float8 data type. 

2. Insert value: -74848942736333776000 

3. Create another table with float8 column 

4. Using COPY statement, load data from first table to second table 5. Verify the result, the value loaded will be -74848942736333780000.

 

Environment

Product Version: 5.21

Resolution

The issue is with the GUC, extra_float_digits.

Please review the following Documentation link: 

extra_float_digits

     Excerpt from documentation:

"extra_float_digits - Adjusts the number of digits displayed for floating-point values, including float4, float8, and geometric data types. The parameter value is added to the standard number of digits. The value can be set as high as 3, to include partially-significant digits; this is especially useful for dumping float data that needs to be restored exactly. Or it can be set negative to suppress unwanted digits."

Test:

alter database testdb set extra_float_digits = 3;
\c testdb
show extra_float_digits;

truncate table rontestfloat;
insert into rontestfloat values (1, -9223372036854776000);
select * from rontestfloat;
copy rontestfloat to '/tmp/rontestfloat_3.txt'; 

alter database testdb set extra_float_digits = 2;
\c testdb
show extra_float_digits;

truncate table rontestfloat;
insert into rontestfloat values (1, -9223372036854776000);
select * from rontestfloat;
copy rontestfloat to '/tmp/rontestfloat_2.txt';

alter database testdb set extra_float_digits = 1;
\c testdb
show extra_float_digits;

truncate table rontestfloat;
insert into rontestfloat values (1, -9223372036854776000);
select * from rontestfloat;
copy rontestfloat to '/tmp/rontestfloat_1.txt';

alter database testdb set extra_float_digits = 0;
\c testdb
show extra_float_digits;

truncate table rontestfloat;
insert into rontestfloat values (1, -9223372036854776000);
select * from rontestfloat;  
copy rontestfloat to '/tmp/rontestfloat_0.txt';

 File output:

/tmp/rontestfloat_0.txt:1 -9.22337203685478e+18
/tmp/rontestfloat_1.txt:1 -9.223372036854776e+18
/tmp/rontestfloat_2.txt:1 -9.2233720368547758e+18
/tmp/rontestfloat_3.txt:1 -9.22337203685477581e+18