CODEWITHSUNDEEP
amazon-s3amazon-redshift
dkelly205
dkelly205

Reputation: 21

AWS - Redshift Decimal issue

I am sending data from Amazon EventBridge to Redshift via an S3 Bucket.

I have an issue with decimal values that are sent.

Here is an example of my jsonpath file stored on s3.

{
    "jsonpaths": [
        "$.id",
        "$detail.TotalPaid"
   ] 
}

The column type on redshift is Decimal(12,2)

Data is saved correctly if I run an SQL insert -

insert into payments values(1, 10.15);

However if the same information is passed from EventBus then the TotalPaid column is saved as 10.14.

The above only seems to be an issue when sending a value with 2 decimal places. If 10.1, for example, is sent then data in the column is saved correctly as 10.10

I had tried setting the column to a VARCHAR type previously but an incorrect value was saved in the database. I was going to try using a Float type instead but looking at the AWS documentation it does say to use a DECIMAL for monetary amounts instead of floating-point types.

Does anyone know how to resolve this issue?

Upvotes: 1

Views: 1233

Answers (2)

dkelly205
dkelly205

Reputation: 21

Issue resolved. I have to add the roundec conversion parameter to the copy command on the AWS delivery stream - https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html#copy-roundec

Upvotes: 1

Bill Weiner
Bill Weiner

Reputation: 11092

Your INSERT statement looks off. Are you trying to insert the values 1 and 10.15 into the 2 columns of table payments?

If so you need to format it like - INSERT INTO payments VALUES(1, 10.15).

If not can you explain exactly what you are trying to do and the DDL of the table.

Upvotes: 0

Related Questions