PostgreSQL – TRUNC Function

In PostgreSQL, the TRUNC() function is used to truncate a numeric value to a specified number of decimal places or to remove the decimal part completely. Unlike ROUND(), which rounds the number, TRUNC() simply truncates or cuts off the decimal part without any rounding.

The basic syntax of the TRUNC() function is as below:

TRUNC(numeric_expression [, decimal_places])

where numeric_expression: The numeric value or expression that you want to truncate.

and decimal_places (optional): The number of decimal places to which you want to truncate the numeric expression. If omitted, the decimal part will be removed, and the number will become a whole number.

Here are some examples of using the TRUNC() function:

1. Truncating to a specified number of decimal places:

SELECT TRUNC(3.1459, 2) AS truncated_value;

This query will return 3.14, truncating 3.1459 to two decimal places.

2. Truncating to a whole number:

SELECT TRUNC(5.7) AS truncated_value;

This query will return 5, removing the decimal part and keeping the number as a whole number.

3. Truncating a negative number:

SELECT TRUNC(-8.9) AS truncated_value;

This query will return -8, removing the decimal part and keeping the number as a whole number.

4. Using expressions as input:

SELECT TRUNC(10 / 3.0, 2) AS truncated_value;

This query will return 3.33, truncating the result of 10 / 3.0 to two decimal places.

The TRUNC() function is useful when you need to keep the integer part of a number but discard the decimal portion.

TRUNC() function returns the result as a numeric data type. If you need the result in a different data type, you can explicitly cast it to the desired type using the CAST() or :: operator. For example:

SELECT TRUNC(3.1459, 2)::numeric(10, 1) AS truncated_numeric;

This query will return 3.1 as a numeric data type with a precision of 10 and one decimal place.