PostgreSQL TRIM_SCALE() Function

PostgreSQL TRIM_SCALE() Function

The PostgreSQL TRIM_SCALE() function is used to remove trailing zeroes from the fractional part of a numeric value. This function is essential for formatting numerical data to display only the necessary precision.

Syntax

TRIM_SCALE(number)

The TRIM_SCALE() function has the following component:

  • number: The numeric value from which to remove trailing zeroes.

Example PostgreSQL TRIM_SCALE() Queries

Let's look at some examples of PostgreSQL TRIM_SCALE() function queries:

1. Basic TRIM_SCALE() Example

SELECT TRIM_SCALE(123.45000) AS trimmed_value;

This query removes the trailing zeroes from 123.45000, resulting in 123.45.

2. TRIM_SCALE() with Column Values

SELECT value, TRIM_SCALE(value) AS trimmed_value
FROM numbers;

This query retrieves the value and its trimmed value from the numbers table.

3. TRIM_SCALE() with No Fractional Part

SELECT value, TRIM_SCALE(value) AS trimmed_value
FROM numbers
WHERE value = 100.000;

This query retrieves the value and its trimmed value from the numbers table where the value has no fractional part.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the TRIM_SCALE() function to format numeric values.

Step 1: Creating a Table

This step involves creating a new table named numbers to store numerical data.

CREATE TABLE numbers (
    id SERIAL PRIMARY KEY,
    value NUMERIC
);

In this example, we create a table named numbers with columns for id and value.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the numbers table.

INSERT INTO numbers (value)
VALUES (123.45000),
       (789.000),
       (0.123450),
       (100.000);

Here, we insert data into the numbers table.

Step 3: Using the TRIM_SCALE() Function

This step involves using the TRIM_SCALE() function to format the numeric values from the numbers table.

-- Basic TRIM_SCALE()
SELECT value, TRIM_SCALE(value) AS trimmed_value
FROM numbers;

-- TRIM_SCALE() with No Fractional Part
SELECT value, TRIM_SCALE(value) AS trimmed_value
FROM numbers
WHERE value = 100.000;

These queries demonstrate how to use the TRIM_SCALE() function to format the numeric values from the numbers table, including basic usage and handling values with no fractional part.

Conclusion

The PostgreSQL TRIM_SCALE() function is a fundamental tool for formatting numerical data by removing trailing zeroes from the fractional part. Understanding how to use the TRIM_SCALE() function and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.