Numeric field overflow exception

Question

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)

Answer 1

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));