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

Question

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?

Because decimal precision is not what you think it is. The first number is the total number of decimal digits, not the number of digits before the separator. You want decimal(10, 5). — Jeroen Mostert
– Jeroen Mostert, Commented Jul 9, 2021 at 7:08
Try with DECIMAL(10,5). Parameter on left side of comma is total length, parameter on right side I number of decimals. — Marko Ivkovic
– Marko Ivkovic, Commented Jul 9, 2021 at 7:09
The psql tag refers to the command line tool and is irrelevant here. Please do not add it — user330315
– user330315, Commented Jul 9, 2021 at 7:15

user330315 · Accepted Answer · 2021-07-09 07:09:34Z

11

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)

answered Jul 9, 2021 at 7:09

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@Stophface . . . Just to emphasize: This is how SQL defines scale and precision, so it works like this in all databases, not just Postgres.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related