amazon s3 - AWS - Redshift Decimal issue

Question

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?

question from:https://stackoverflow.com/questions/65832623/aws-redshift-decimal-issue

Answer 1

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.