Introduction to the PostgreSQL TRIM_SCALE() function

TheTRIM_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 theTRIM_SCALE() function:

TRIM_SCALE(numeric_value)

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

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

It returnsNULL if thenumeric_value isNULL.

PostgreSQL TRIM_SCALE() function examples

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

1) Basic TRIM_SCALE() function example

The following example uses theTRIM_SCALE() function to reduce the trailing zeroes of the number123.45000:

SELECT TRIM_SCALE(123.45000);

Output:

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

In this example, theTRIM_SCALE() function removes the trailing zeroes from the123.45000, resulting in123.45.

2) Using the TRIM_SCALE() function with table data

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

First,create a table calledproducts 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 theproducts 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 theTRIM_SCALE() function:

UPDATE productsSET 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 theTRIM_SCALE() function to reduce the scale of a number scale by removing trailing zeroes.

Last updated onFebruary 18, 2024

On this page

• Introduction to the PostgreSQL TRIM_SCALE() function
• PostgreSQL TRIM_SCALE() function examples
• Summary