Syntax

The following illustrates the syntax of the PostgreSQLTRUNC() function:

TRUNC(number [,precision])

Arguments

TheTRUNC() function accepts two arguments.

1)number

Thenumber argument is a numeric value to be truncated

2)precision

Theprecision argument is an integer that indicates the number of decimal places.

If theprecision argument is a positive integer, theTRUNC() function truncates digits to the right of the decimal point.

In case theprecision is a negative integer, theTRUNC() function replaces digits to the left of the decimal point.

The precision argument is optional. If you don’t specify it, it defaults to zero (0). In other words, thenumber is truncated to a whole number.

Return value

The PostgreSQLTRUNC() function returns the samenumeric data type as the first argument if the second argument is not specified. Otherwise, the function returns a numeric value if both arguments are used.

Examples

1) Truncate to a whole number example

The following example uses theTRUNC() function to truncate a number to an integer:

SELECT    TRUNC(10.6);

The result is:

10

2) Truncate to the specified decimal place

The following statement truncates a number to 2 decimal places:

SELECT    TRUNC(        1.234,        2    );

Here is the result:

1.23

3) Truncate numbers with a negative second argument example

Consider the following example:

SELECT    TRUNC(150.45,-2)

The second argument is -2, therefore, theTRUNC() function replaced the digits to the left of the decimal point that resulting in:

100

4) Truncate numbers returned by a query

See the followingfilm,film_category, andcategory tables in thesample database:

The following statement calculates the average rental rate by film category:

SELECT    NAME,    TRUNC(AVG( rental_rate ),2)FROM    filmINNER JOIN film_category        USING(film_id)INNER JOIN category        USING(category_id)GROUP BY    NAMEORDER BY NAME;

In this example, we used theTRUNC() function to truncate the average rentals to two decimal places.

The following picture illustrates the result:

In this tutorial, you have learned how to use the PostgreSQLTRUNC() function to truncate numbers.

Last updated onFebruary 16, 2024

On this page

• Syntax
• Arguments
• Return value
• Examples