How MIN_SCALE() Works in PostgreSQL

In PostgreSQL, min_scale() is a mathematical function that returns the minimum scale needed to represent the supplied value precisely.

The “scale” is the number of fractional decimal digits. For example, a value of 1.9500 has a minimum scale of 2, whereas 1.957 has a minimum scale of 3.

We pass the value to the function as a numeric argument, and it returns the result as an integer.

Example

Here’s an example of how it works:

SELECT min_scale(7.97);

Result:

Here are some more examples:

SELECT 
    min_scale(8) AS "8",
    min_scale(8.00) AS "8.00",
    min_scale(8.10) AS "8.10",
    min_scale(8.11) AS "8.11",
    min_scale(8.123456789) AS "8.123456789",
    min_scale(8.00000000000000001) AS "8.00000000000000001";

Result:

 8 | 8.00 | 8.10 | 8.11 | 8.123456789 | 8.00000000000000001 
---+------+------+------+-------------+---------------------
 0 |    0 |    1 |    2 |           9 |                  17

So we can see that min_scale() does not simply return the number of digits after the decimal place. Rather, it returns the number of digits after the decimal place that are required to represent the value precisely. Therefore, any trailing zeros are not included when calculating the minimum scale.

Therefore, we can get a different result depending on whether we use the min_scale() function or the scale() function:

SELECT 
    min_scale(8.00) AS "min_scale",
    scale(8.10) AS "scale";

Result:

 min_scale | scale 
-----------+-------
         0 |     2

Given the function accepts a numeric argument, the scale can be quite significant:

SELECT min_scale(8.11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111);

Result:

In fact, the numeric type in PostgreSQL can have up to 16383 digits after the decimal point (and up to 131072 digits before the decimal point).

Expressions

We can include expressions, such as this.

SELECT min_scale(15.123 * 1.75);

Result:

Passing a Negative Value

We can pass a negative value to the function:

SELECT min_scale(-2.34);

Result:

Passing Zero

We can pass zero:

SELECT min_scale(0);

Result:

Passing Null

Passing null results in null:

SELECT min_scale(null);

Result:

null

Passing the Wrong Argument Type

Passing the wrong argument type results in an error:

SELECT min_scale('cat');

Result:

ERROR:  invalid input syntax for type numeric: "cat"
LINE 1: SELECT min_scale('cat');
                         ^