Unload command added more decimal places to float4 field

0

I have a table with a float4 field, and one of the values is 120.12 When I unload the data to a file in S3, and look at the file in a text editor, the related field now has the value 120.120003 I.e. it has made the value more precise by adding 4 more decimal places, and then (randomly) making the last decimal place a non-zero value. This is happening for many database records that I am unloading/exporting.

Why is this happening, and what can I do to prevent it? Is it because I'm using float4 and it has "6 significant digits of precision"? Is redshift just adding in extra precision because I used float4? If so, should I be using DECIMAL(8,2) instead? I have used float4 previously without this issue when exporting/unloading.

An example of the create table query:

CREATE TABLE exampleTableName (
  id       int identity(1,1),
  accId    varchar(16) encode zstd,
  amount   float4,

  primary key(id)
)
distkey(accId)
interleaved sortkey(accId);

The unload command:

UNLOAD ('YYY')
TO 's3://bucket/prefixFile_'
IAM_ROLE 'XXX'
HEADER
ADDQUOTES
PARALLEL OFF
MAXFILESIZE AS 5gb
DELIMITER AS ',' 
GZIP;
PPPP
已提問 5 年前檢視次數 541 次
1 個回答
0
已接受的答案

Yes, this happens when you use FLOAT for the decimal data because it cannot store values of arbitrary precision. https://en.wikipedia.org/wiki/IEEE_754

I would generally always recommend using the DECIMAL datatype unless you have an existing application that has an unchangeable requirement for FLOAT, e.g., calculation expects FLOAT and output cannot change.

Additionally, by using DECIMAL you are able to use our new AZ64 compression encoding which will reduce the amount of storage needed and improve your query performance. https://aws.amazon.com/about-aws/whats-new/2019/10/amazon-redshift-introduces-az64-a-new-compression-encoding-for-optimized-storage-and-high-query-performance/

已回答 5 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南