Redshift: Numeric data overflow (result precision)

0
votes

Hi I have a Redshift DB and am trying to insert data from the table "scans" / column "net_rate" (data type numeric) into another table "visits_by_scan" / column "scan_cost" (data type numeric). The query I am using is the below:

insert into visits_by_scan (scan_cost)
select sum(cast(s.net_rate as decimal(30,4))) 
from scans s

When I try to run this query I get the following numeric overflow error message:

enter image description here

How to insert the data without any errors? Any help is appreciated.

1
sqlcastingamazon-redshiftprecision
Well, have you tried decimal(31, 4) ? - Gordon Linoff
Yeah, same issue unfortunately... - ERR
what's the value of sum(s.scan_cost) without casting? - eshirvana
@eshirvana The value is "725978152.0180". - ERR
@eshirvana It is numeric. They are both numeric: "scan_cost" and "net_rate". - ERR

1 Answers

0
votes
  1. try to check the what is the type of the column you are going to insert
  2. use the type in the value you insert.

According to my understanding, the error means that the type of value that you want to insert is different with the type of value in the column.

For example, you cannot inset 1.3333 into a column with only one-digit value like 1.3.