PostgreSQL TRIM_SCALE() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL TRIM_SCALE() function to reduce the value’s scale by removing trailing zeroes.

Introduction to the PostgreSQL TRIM_SCALE() function

The TRIM_SCALE() function allows you to reduce the scale of a number by removing trailing zeroes.

Note that the scale of a number is a number of fractional decimal digits.

Here’s the syntax of the TRIM_SCALE() function:

TRIM_SCALE(numeric_value)

In this syntax, the numeric_value is a value that you want to trim the scale.

The TRIM_SCALE() function returns a numeric value with the numeric type after removing trailing zeroes.

It returns NULL if the numeric_value is NULL.

PostgreSQL TRIM_SCALE() function examples

Let’s take some examples of using the TRIM_SCALE() function.

1) Basic TRIM_SCALE() function example

The following example uses the TRIM_SCALE() function to reduce the trailing zeroes of the number 123.45000:

SELECT TRIM_SCALE(123.45000);

Output:

trim_scale
------------
     123.45
(1 row)

In this example, the TRIM_SCALE() function removes the trailing zeroes from the 123.45000, resulting in 123.45.

2) Using the TRIM_SCALE() function with table data

We’ll show you an example of using the TRIM_SCALE() function to standardize the numeric values in a table.

First, create a table called products to store product data:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC NOT NULL
);

Second, insert some rows into the products table:

INSERT INTO products (name, price)
VALUES
    ('Smartphone', 699.9900),
    ('Laptop', 1299.99),
    ('Headphones', 149.5000),
    ('Tablet', 449.00),
    ('Smartwatch', 299.00),
    ('Wireless Speaker', 79.9900)
RETURNING *;

Output:

id |       name       |  price
----+------------------+----------
  1 | Smartphone       | 699.9900
  2 | Laptop           |  1299.99
  3 | Headphones       | 149.5000
  4 | Tablet           |   449.00
  5 | Smartwatch       |   299.00
  6 | Wireless Speaker |  79.9900
(6 rows)

Third, update the prices to remove trailing zeroes using the TRIM_SCALE() function:

UPDATE products
SET price = TRIM_SCALE(price)
RETURNING *;

Output:

id |       name       |  price
----+------------------+---------
  1 | Smartphone       |  699.99
  2 | Laptop           | 1299.99
  3 | Headphones       |   149.5
  4 | Tablet           |     449
  5 | Smartwatch       |     299
  6 | Wireless Speaker |   79.99
(6 rows)

Summary

Use the TRIM_SCALE() function to reduce the scale of a number scale by removing trailing zeroes.