3

By laziness and convenience I integrated quite a lot of data from a partner in an unrestricted numeric type in my PostgreSQL database.

However it now seems that depending of different shipments from this partner the scale of the numeric varies from zero to over 20 decimal places (or scale) which don't make a lot of sense and probably consume quite a lot of storage for no good reason. So I would like to restrict my numeric field to a reasonable scale.

However because my partner don't provide any recommendations I would like to identify the occurrence of each scale in my data-set to potentially identify a reasonable middle ground between 0 and 20 (zero obviously not being an option and given the 17 Million row-count, aggregated sums ARE going to be impacted by my decision).

It will also be needed during following rounding to avoid actually increase the scale of values that were shipped with a scale of zero.


Long story short, what the nicest way to compute that for a specific number stored as an arbitrary numeric?

Best I could come up to is that, but is there a more elegant way to do that without converting to text?

SELECT
  my_numeric,
  COALESCE(
    char_length(                            -- Finding size of string extracted by a...
      substring(my_numeric::text,'\.(\d*)') -- regexp to return all digits right from '.'
    ),                                      -- but if scale is 0 substring return NULL                               
  0                                         -- so I handled this inside a COALESCE
  ) AS my_numeric_scale
FROM
(VALUES
(0.1::numeric),
(0.12),(0.123),
(0.1234),
(0.12345),
(0.123456),
(0.000001),
(0.100000)
) foo (my_numeric)
0

1 Answer 1

6

is there a more elegant way to do that

Yes, use the scale() function which was introduced in 9.6:

SELECT my_numeric,
       scale(my_numeric)
FROM (
 VALUES
    (0.1::numeric),
    (0.12),(0.123),
    (0.1234),
    (0.12345),
    (0.123456),
    (0.000001),
    (0.100000)
) foo (my_numeric)

Online example: https://rextester.com/MVJQR23795

2
  • At first I felt dumb because I promise I've parse this manual page quite a few time. Then I realize mistake... this function was apparently introduced in Postgresql 9.6 so I was fooled by my search engine and looked at an old version. Maybe you could edit your answer to point out this work for >9.6. Thanks a lot!
    – MarHoff
    Jul 4, 2019 at 12:31
  • 1
    @MarHoff: that's another good reason to not use a search engine to find the manual, but jump to the manual of your Postgres version directly. Just bookmark postgresql.org/docs/current/static/index.html
    – user1822
    Jul 4, 2019 at 12:34

Your Answer

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

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