Truncate the value of column in PostgreSQL (TRUNC() Function)

The TRUNC() function in PostgreSQL is used to truncate a numeric value to a specified number of decimal places, effectively cutting off digits after a certain decimal point without rounding. In order to truncate the value of column in PostgreSQL we use TRUNC() function. Let’s see how to

• Truncate the value in PostgreSQL – TRUNC()
• Truncate the value to decimal places in PostgreSQL
• Create the column which truncates the value of the column

With an example for each

Syntax:

• numeric_expression: The number you want to truncate.
• decimal_places (optional): The number of decimal places to truncate to. If omitted, the number is truncated to zero decimal places.

Get TRUNC() in PostgreSQL:

Example 1: Truncate to the Nearest Integer

TRUNC() function in PostgreSQL gets the truncated value to nearest integer

 

SELECT TRUNC(5.733) AS truncated_value;

So the truncated value will be

Example 2: Truncate to a Specified Number of Decimal Places

To truncate the number 5.69677 to two decimal places:

 

SELECT TRUNC(5.69677,2) AS truncated_value;

So the truncate value will be for two decimal places

Example 3: Truncate Negative Numbers

To truncate the number -23.456 to the nearest integer:

 

SELECT TRUNC(-23.456) AS truncated_number;

So the Truncated value will be

Example 4: Truncate Negative Numbers to 2 decimal place

To Truncate the number -23.456 to 2 decimal places:

 

SELECT TRUNC(-23.456,2) AS truncated_number;

So the truncated value will be for two decimal places

Get truncated value of column in PostgreSQL table:

We use table states

 

SELECT *,TRUNC(hindex_score) as Truncate_hindex  from STATES

We have created a column and stored truncated value of hindex_score

So the resultant table will be

Truncate the column to decimal places in PostgreSQL table:

Truncate the column to two decimal places as shown below

 

SELECT *,TRUNC(hindex_score,2) as Truncate_hindex  from STATES

We have created a column and stored the truncated value of hindex_score to two decimal places

So the resultant table will be