PostgreSQL trim_scale() Function

The floating point numbers retain their original decimal places so that it is expressed accurately and precisely. When working with these floating point numbers, we usually encounter cases where we have numerous trailing zeros after the decimal point. The number can be accurately expressed if we remove the trailing zeros. There is a function named trim_scale() that is used to trim all the trailing zeros after the decimal point and return the number.

In this article, we will learn to use the trim_scale() function in our queries with the help of different examples/use cases.

PostgreSQL trim_scale() Function

The Postgres trim_scale() function trims the trailing zeros after the decimal point. The syntax for the trim_scale() function looks like this:

trim_scale(num)

● The trim_scale() function takes in a numeric value.

● The returned type of the trim_scale() function is also a numeric data type.

The function returns the provided number after removing all the trailing zeros from it.

We’ll move towards some examples for more clarity.

Example 1: Understanding the trim_scale() Function

We will execute the following query to see how the trim_scale() function works with different numbers.

SELECT
  trim_scale(24.7300) AS "trim_scale: 24.7300",
  trim_scale(89.0078) AS "trim_scale: 89.0078",
  trim_scale(-11.5060) AS "trim_scale: -11.5060",
  trim_scale(0.00400) AS "trim_scale: 0.00400",
  trim_scale(18470000) AS "trim_scale: 18470000";

The above query evaluates different cases of the trim_scale() function. Let’s first consider the output of the query.

In the above output illustration:

● In the case of the number “24.7300”, the trim_scale() function has trimmed all the trailing zeros, after the decimal point, from the number.

● For “89.0078”, the function retrieved the same number because there was no trailing zero in it.

● The trim_scale() function returned “-11.506” when the number “-11.5060” was provided. We can notice that the last zero has been removed.

● When the number “0.00400” is provided to the trim_scale() function, it returns “0.004”, as the two trailing zeros have been removed from the number.

● Lastly, if the number does not contain a decimal point, the number will remain the same no matter how many trailing zeros it contains, as illustrated in the case of “18470000”.

So this is the basic function of the trim_scale() function in PostgreSQL. We can also implement the trim_scale() function on the table data to clean values in the table. Let’s see this particular use case.

Example 2: Using the trim_scale() Function on Table Data

We’ll implement the trim_scale() function on the “circle” table. The table contains the data for the circumference of each circle. The table looks like this:

Now let’s implement the trim_scale() function on the circumference column of the above table. We can write the following query for this:

SELECT *, trim_scale(circumference) AS "trimmed_circumference"
FROM circle;

The above-written query returns the following output.

In the above output, we can see that the trim_scale() function returns the provided number after removing the extra trailing zeros after the decimal point.

This is how the trim_scale() function does.

Conclusion

The trim_scale() function trims the extra trailing zeros from a number and then returns the number. The trim_scale() function takes in a value of a numeric data type and returns the number after trimming zeros from the decimal part of the given numeric value. In this article, we have learned how to use the trim_scale() function along with practical examples.

PostgreSQL trim_scale() Function