PostgreSQL: Numeric field overflow on decimal / numeric Data Type - Why does it gives this Error

user_3935035 0
Viewed 562

I create a table like this

CREATE TABLE foo (
    id serial CONSTRAINT id_pk PRIMARY KEY,
    bar varchar(256),
    test decimal(5,5)
);

I then want to populate the test column

 INSERT INTO foo (test) VALUES (12345.12345)

This gives me

Numeric field overflow Detail: A field with precision 5, scale 5must round to an absolute value less than 1.

I do not understand this. I though if I set a column to decimal(5,5), I must have five digits to the left of the comma and five digits to the right of the comma. I have that with the number 12345.12345.

Why does it give me this error?

sqldatatypes postgresql sql
1 Answers

You misunderstood how specifying a decimal works (and it is a bit confusing I agree).

Quote from the manual

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. The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point

So the first number defines the total number of digits, the second one defines the number of decimals.

If you want 5 digits before the decimal point and five after it, you need to use decimal(10,5)

user_3303150
Related