PostgreSQL provides a mathematical function that gives us the minimum number of decimal places required in order to express/represent a specific number accurately and precisely. This function is named as the min_scale() function. The following section of this blog will explain the use and working of the min_scale() function through proper examples and use cases.

How to Use min_scale() Function in PostgreSQL?

The min_scale() function in PostgreSQL returns the minimum precision or the minimum number of decimal places the specified number possesses to represent itself accurately. The basic syntax of the min_scale() function is:

min_scale(num)

● The number is provided to the function as an argument.

● This number should be of Numeric data type.

● The function will always return an integer data type value, representing the minimum number of decimal places used to accurately express the number.

If the number provided is an integer, the output will be 0. If that number has a fractional part, the output will be the number of digits after the point without the trailing zeros.

The below examples will more clearly illustrate the working and usage of the min_scale() function.

Example 1: Understanding the min_scale() Function

Consider the following query to see how the min_scale() function works.

SELECT
  min_scale(0) AS "min precision:0",
  min_scale(5.0) AS "min precision:5.0",
  min_scale(20.48) AS "min precision:20.48",
  min_scale(-11.5006) AS "min precision:-11.5006",
  min_scale(17.799*2) AS "min precision";

The query will return the minimum precision/ minimum number of decimal places that are necessary to express the accurate number. The output for the above query is:

We can see that the integer values are returned for the respective values. The simple rule is that:

● The min_scale() function simply gives the minimum number of decimal places after the decimal points except the trailing zeros.

● If the number is a floating point number(lines 12-15), it will follow the same rule.

● If the given numeric value is an integer(0 in the above query), the min_scale() function will give 0 as there is no decimal place after the decimal point.

The outputs in the above image follow the same rule.

Example 2: Using the min_scale() Function on Table’s Data

Consider the following “circles” table.

We can find the minimum precision of the “circumference“ column by implementing the min_scale() function on it. The query for this is written as:

SELECT *, min_scale(circumference) AS "min precision in   circumf"
 FROM circles;

This query will return the number of decimal places that are necessary to express the respective circumference completely and accurately like this:

To demonstrate the above table, consider the circumference on the first row. After counting, we will be able to observe that the 6.28318530717959 has 14 decimal places after the decimal point (and no trailing zero) so the min_scale() function has returned 14.

Let’s say, the number was 6.283185307179590000, the min_scale() function would still have returned 14 because no matter how many trailing zeros are there, they won’t count. This is how the min_scale() function works in PostgreSQL.

Conclusion

The min_scale() function in Postgres returns the minimum precision or minimum number of decimal places that are necessary to accurately/completely express the number. The min_scale() function gets the numeric value as input and gives an integer. We have assessed the basic workings of the min_scale() function along with their implementation in examples.