PostgreSQL – Finding the Scale of an Unrestricted Numeric Type

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)