CODEWITHSUNDEEP
mysqlamazon-redshift
Dean
Dean

Reputation: 9128

How do I avoid a numeric data overflow

I have a query that ran fine in our old MySQL database where there is a check:

WHERE column_x + column_y > 524288000

Now that we've migrated to Redshift, the query fails:

ERROR: Numeric data overflow (addition)
  Detail: 
  -----------------------------------------------
  error:  Numeric data overflow (addition)
  code:      1058
  context:   
  query:     915381
  location:  numeric.hpp:112
  process:   query4_s0_22 [pid=6421]
  ----------------------------------------------- [SQL State=XX000]

Both columns are of type bigint. When running the query without adding the columns (just checking against one column or the other) the query executes fine. I'm assuming there are rows where column_x + column_y is greater than the max size of bigint.

What's the workaround here?

Upvotes: 2

Views: 15594

Answers (3)

Tomasz Tybulewicz
Tomasz Tybulewicz

Reputation: 8657

Simply change addition into subtraction:

WHERE column_x > 524288000 - column_y

Upvotes: 2

Andrew Yasinsky
Andrew Yasinsky

Reputation: 185

multiple by 1/x :) before comparing (divide will make it fail on 0):

WHERE column_x * (1/X) + column_y* (1/X) > 524288000* (1/X)

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

WHERE column_x::numeric(20) + column_y::numeric(20) > 524288000

Upvotes: 2

Related Questions