How to prevent PostgreSQL from automatically rounding numeric types?

Question

I have a simple schema and query for a MWE.

Schema:

CREATE TABLE ttable (
  tcol Numeric(19,4)
)

Query:

INSERT INTO ttable (tcol) VALUES ('123.45678');
SELECT * FROM ttable;

Fiddle

Result:

123.4568

In this case, I have lost precision. I entered five decimal places, but Postgres automatically rounded down to four. I want this to be an error, or at least a warning of some kind that I can detect, so that I can tell the user about the loss of precision.

How can I make this happen?

Edit:

This question is obviously not a duplicate of this question. In that question, the user is using a client application which is rounding value below the stored precision. My question is about Postgres itself rounding the data to fit, not the client displaying. This would be obvious to anyone who looked further than the title of both questions.

score 11 · Accepted Answer · edited Dec 22 '20 at 10:02

I want this to be an error

You could use a check constraint that validates the scale of the value, rather than the definition of the data type:

CREATE TABLE ttable 
(
  tcol numeric,
  constraint check_tcol_scale check (scale(tcol) <= 4)
);

Note also that the column itself needs to be redefined as just numeric, rather than numeric(19,4).

Then the following:

insert into ttable values (123.45678);

will result in

ERROR: new row for relation "ttable" violates check constraint "check_tcol_scale"
   Detail: Failing row contains (123.45678)

But

insert into ttable values (123.4567);

will succeed.

Check out this fiddle to see this solution in action.

score 3 · Answer 2 · edited Dec 22 '20 at 19:41

This is documented behaviour:

numeric columns with a declared scale will coerce input values to that scale

Postgres does what it is designed to do and there is no reason to raise an error or a warning.

I think you may be confusing formatting and internal representation of data. For example, you shouldn't care how Postgres (or any other DBMS) stores timestamps internally, and your application formats them as it sees fit. If you're concerned how decimals are shown to the user, store it as it makes sense (e.g. numeric without the explicitly specified precision has a pretty wide range of acceptable values) and format it in the user interface. I can't really imagine a use case where a user supplies 123.45678 and you find it helpful to reject the input with "Gah! too many digits!".

Alternately, you can, of course, suggest a patch, or deal with it in your application, which should be validating user input anyway.

score 1 · Answer 3 · answered Dec 21 '20 at 15:49

you defined your tcol as 18.4 with 4 digits, that is what you get when you enter a 5 digit number, choose your decimal with the max precision that you need.

Schema (PostgreSQL v12)

CREATE TABLE ttable (
  tcol Numeric(19,4),
  tcol2 Numeric(19,5)
)

Query #1

INSERT INTO ttable  
VALUES ('123.45678','123.45678');

There are no results to be displayed.

Query #2

SELECT * FROM ttable;

tcol	tcol2
123.4568	123.45678

Query #3

SELECT  tcol2 ::decimal(18,4) FROM ttable;

tcol2
123.4568

View on DB Fiddle

How to prevent PostgreSQL from automatically rounding numeric types?

3 Answers3