2

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.

2
  • So what is the user supposed to do with their value 123.45678? It can't be stored as is so either they don't insert it (but what would that mean -- I'm really curious as to your usecase) or they would have to round/truncate it themselves (in which case you're just creating more work for the user). Dec 22, 2020 at 8:38
  • 2
    This column stores a monetary amount, so I can't round. If the user wants to store more than four decimal places, either they just can't use my app or they'll have to round. I'm happy to round for them, but I need at least some kind of ability to detect this scenario so I could warn them that their transaction is going to lose precision.
    – TechnoSam
    Dec 22, 2020 at 22:42

3 Answers 3

11

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.

2
  • If you think there's some sort of malicious downvoting going on, you can always send a request to the CM team via the help center. dba.stackexchange.com/contact
    – jcolebrand
    Dec 22, 2020 at 21:17
  • 1
    Thank you! This is interesting. Is there any performance downside to not fixing the precision and scale ahead of time? If not, this seems like a workable solution.
    – TechnoSam
    Dec 22, 2020 at 22:50
3

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.

2
  • 3
    Also, why on earth are you inserting character literals into a numeric column: INSERT INTO ttable (tcol) VALUES ('123.45678')?
    – mustaccio
    Dec 22, 2020 at 1:19
  • 1
    I did that because I was coming from Python, where you create decimal literals from strings, not floats. I tried it in the fiddle and it worked, so I didn't stop to investigate more because I figured it communicated the point either way.
    – TechnoSam
    Dec 22, 2020 at 22:43
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.