Understanding the Difference Between SCALE(), MIN_SCALE() and TRIM_SCALE() in PostgreSQL

Posted on by Ian

Anyone who looks at PostgreSQL’s list of mathematical functions will notice that there are some functions that have “scale” in their name. In particular, scale(), min_scale(), and trim_scale().

While these functions all have a similar name, they each have a different purpose. That said, they’re all related to the scale of the given number. The scale is the number of digits to the right of the decimal point in a number. For example, the number 1.95 has a scale of 2, and 1.958 has a scale of 3.

Purpose of Each Function

The following table outlines the purpose of each function:

scale()Returns the scale of its argument.
min_scale()Returns the minimum scale needed to represent the supplied value precisely. This removes any trailing zeros from its calculation.
trim_scale()Removes any trailing zeros from the scale.

So each function has its own distinct purpose, completely independent of the others.

Example

Here’s an example that compares these functions side by side:

SELECT 
    scale(1.75000),
    min_scale(1.75000),
    trim_scale(1.75000);

Result:

 scale | min_scale | trim_scale 
-------+-----------+------------
     5 |         2 |       1.75

So here’s what they did:

  • scale() returned the total number of digits in the fractional part, regardless of trailing zeros.
  • min_scale() returned the minimum scale needed to represent the supplied value precisely. This resulted in the scale being reduced by three, due to the last three digits being zero.
  • trim_scale() returned the value after removing the trailing zeros from the scale.

Here’s another example:

SELECT 
    scale(1.75110),
    min_scale(1.75110),
    trim_scale(1.75110);

Result:

 scale | min_scale | trim_scale 
-------+-----------+------------
     5 |         4 |     1.7511

No Trailing Zeros

If there are no trailing zeros, then scale() and min_scale() return the same result, and trim_scale() has nothing to trim:

SELECT 
    scale(1.75112),
    min_scale(1.75112),
    trim_scale(1.75112);

Result:

 scale | min_scale | trim_scale 
-------+-----------+------------
     5 |         5 |    1.75112