How do I avoid excessive zeroes for numeric fields?

Posted on

Question :

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?

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *