0

As per recommendations, I always use the numeric type for any column supposed to hold a sum of money.

When inserting such values (or updating them), I always use high precision math functions in my application (PHP "BC Math").

This results in the fields getting values such as:

-1494.744565020000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

I view them later in pgAdmin 4 and it's very annoying to see the long series of pointless zeroes, when it should be just:

-1494.74456502

There is no meaning with storing/representing all those zeroes like that. I'm not fully sure if this is PG or pgAdmin's doing, actually.

Either way, I want to avoid it. How do I prevent numeric values from being stored with pointless zeroes?

(Of course, I don't want less precision -- I'm just talking about zeroes that literally have no meaning and thus should be automatically cut off from the right until they hit a non-zero digit.)

Is PG for some reason storing these numeric numbers like this? If so, why?

Improve this question
5
  • 2
    This has nothing to do with how data are stored in a database; the client does the formatting.
    – mustaccio
    Commented Dec 12, 2020 at 20:38
  • @mustaccio So this is just an extremely odd choice by pgAdmin 4?
    – user14756437
    Commented Dec 12, 2020 at 21:24
  • If you chose a different precision and scale for your columns the result might have been different. Without that, numeric has the range of "up to 131072 digits before the decimal point; up to 16383 digits after the decimal point", so any client is within its rights to display all of them.
    – mustaccio
    Commented Dec 12, 2020 at 23:44
  • Mathematically speaking these trailing zeros aren't pointless, without the zeros it might as well be a rounded number with non zero digits afterwards
    – JMD Coalesce
    Commented Dec 13, 2020 at 14:15
  • @JMDCoalesce Mathematically speaking the digits are pointless. user14756437, does it help if you set the precision and scale on the column, like numeric(13,2)?
    – Laurenz Albe
    Commented Dec 14, 2020 at 9:19

1 Answer 1

Reset to default
0

As mustaccio pointed out, this is more of an issue of the displaying of the value on the client side. Your number is originally generated with a lot of unneeded 0s in PHP. The column that stores this data in database has a fixed amount of precision it must adhere to (per its schema definition), so all values are stored in that column with the same precision (and technically normalized with the same amount of 0s). This isn't apparent though until recalled back into the client application. It is the client side that is responsible for formatting.

If you really wanted to avoid this, you could store your results in a VARCHAR column instead and cast your results to a string then use some sort of function in PHP to remove the trailing 0s like RTRIM() or REGEX.

This isn't necessarily recommended from a Normalization standpoint for performance and maintainability reasons, but if you don't need to manipulate these results in SQL or do any aggregation on it after it's stored then it's probably ok.

Improve this answer

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.