Search code examples
sqldatatypesgreenplum

Numeric field overflow exception

How I should rewrite my insert statement ?

CREATE TABLE test_table ( 
    rate decimal(16,8)
);

INSERT INTO test_table VALUES (round(3884.90000000 / 0.00003696, 8));

Exception:

ERROR: numeric field overflow
SQL state: 22003
Detail: A field with precision 16, scale 8 must round to an absolute value less than 10^8. Rounded overflowing value: 105110930.73593074

Database: Greenplum Database 4.3.8.0 build 1 (based on PostgreSQL 8.2.15)

Solution

  • You should use decimal(17,8)

    CREATE TABLE test_table
( 
    rate decimal(17,8)
);

    Use decimal in below format 

    decimal(precision, scale)

    1) The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point

    2) The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point

    Since the result of your insert statement is 105110930.73593074, Total number of digits is 17 and after decimal it has 8 so you should use decimal(17,8)

    Select (round(3884.90000000 / 0.00003696, 8));