PostgreSQL TRUNC() Function

PostgreSQL TRUNC() Function

The PostgreSQL TRUNC() function is used to truncate a numeric value to a specified number of decimal places. This function is essential for formatting numerical data by discarding the fractional part beyond the specified precision.

Syntax

TRUNC(number, decimal_places)

The TRUNC() function has the following components:

  • number: The numeric value to be truncated.
  • decimal_places: The number of decimal places to truncate to (optional).

Example PostgreSQL TRUNC() Queries

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

1. Basic TRUNC() Example

SELECT TRUNC(4.567) AS truncated_value;

This query truncates 4.567 to zero decimal places, resulting in 4.

2. TRUNC() with Specified Decimal Places

SELECT TRUNC(4.567, 2) AS truncated_value;

This query truncates 4.567 to two decimal places, resulting in 4.56.

3. TRUNC() with Column Values

SELECT value, TRUNC(value, 1) AS truncated_value
FROM numbers;

This query retrieves the value and its truncated value to one decimal place from the numbers table.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the TRUNC() function to format numerical 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 (1.234),
       (2.345),
       (3.456),
       (4.567);

Here, we insert data into the numbers table.

Step 3: Using the TRUNC() Function

This step involves using the TRUNC() function to format the numerical values from the numbers table.

-- Basic TRUNC()
SELECT value, TRUNC(value) AS truncated_value
FROM numbers;

-- TRUNC() with Specified Decimal Places
SELECT value, TRUNC(value, 2) AS truncated_value
FROM numbers;

These queries demonstrate how to use the TRUNC() function to format the numerical values from the numbers table, including basic usage and truncating to specified decimal places.

Conclusion

The PostgreSQL TRUNC() function is a fundamental tool for formatting numerical data by truncating values to the desired precision. Understanding how to use the TRUNC() function and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.