Updated May 12, 2023
Definition of PostgreSQL Trunc()
PostgreSQL’s trunc() function is used to truncate the decimal places to a certain precision. It can also return a number truncated to the whole number if no precision is defined. The trunc() function is a mathematical function present in PostgreSQL. This function helps in manipulating numbers as required. You can modify the number of decimal places as required by the project and use them. Let us have a look at the function and its different uses.
Syntax:
The following syntax illustrates the syntax of the PostgreSQL trunc() function.
Trunc (number [, precision])Parameters:
- Number: In this case, the number is the one that is supposed to be truncated.
- Precision: This argument is an optional argument. The integer value represents the number of decimal places to truncate.
- If this precision number is a positive integer, the trunc() function will truncate those number of digits that are present at the right of the decimal point.
- If the precision number is a negative integer, the trunc() function will truncate those numbers of digits that are present at the left of the decimal point.
- As specified earlier, the precision argument is an optional argument. If you do not specify a value, the default value of 0 is taken, which truncates the number to its whole number.
- The result of this function is the same numeric data type as the first argument. It returns a numeric value irrespective of the number of arguments being specified.
How Trunc() Function Works in PostgreSQL?
Let us have a look at how the trunc() function works.
Consider the above example. The number which we have taken into consideration is 2.465. Here there is also the precision argument present which is 1. You need to provide a precision argument when using the trunc() function. It finds the number 1, which is the precision argument. It then takes both arguments and applies the trunc() on this number. Once you perform the truncation, it will keep only one decimal number to the right of the decimal point. That is, the number is 2.465; it will take the number on the right of the decimal point, which means it will take the decimal 4. The output hence here, will be 2.4. You can use the truncated value in further calculations. You can use the trunc() function in any query that needs to remove the decimal point from a number and retrieve the whole number.
Examples to Implement TRUNC() in PostgreSQL
We now know the trunc() function and how it works. To understand better, let us have a look at some examples.
1. Trunc() function taking default value when precision is not specified. This example shows how the trunc() function works when no precision argument is specified.
select trunc(125.135);
Another example here will eliminate your doubt that if the number has the digits after the decimal point as 99, will the number be rounded off. Let us take the number: 125.999 and apply the trunc() function to it.
select trunc(125.999);
As you can see from the result, the number is not rounded off. It simply truncates the numbers after the decimal point.
2. In this example, we will observe the behavior of the trunc() function when a positive number is used as the precision value in the argument. We have taken the number 123.456 with a precision of 2. The PostgreSQL statement for this will be as follows:
SELECT trunc(123.456,2);
The value here is 123.45. The next digits are truncated to 0.
3. In this example, we will observe how the trunc() function behaves when a negative number is used as the precision value in the argument.
We have taken a number 150.45 with a precision of -2. The PostgreSQL statement for this will be as follows:
SELECT trunc(150.45, -2);
Using a negative precision with the function will operate on the left side of the decimal point. It will take the numbers at the left of the decimal point. In the above example, it will take 50 and truncate it. The right side of the decimal point as it is working as void.
4. In this example, we will attempt to truncate the numbers returned by a query.
SELECT
C.CUSTOMER_KEY ,
TRUNC(AVG(AMOUNT),2)
FROM
ODS_ABP.BL1_RC_RATES C
INNER JOIN APP_MAESTRO.SBSCRBRDIM S
ON C.CUSTOMER_KEY=S.CUSTOMER_KEY
GROUP BY
C.CUSTOMER_KEY
ORDER BY C.CUSTOMER_KEY;The above query considers two tables. It takes the amount of value from the BL1_RC_RATES table. This column takes decimal values, and we truncate them to 2 decimal places. We are also taking the average of this field. The query then takes this averaged and truncated value along with the customer key. The query does inner join between these two given tables. We also include a GROUP BY clause because we use the aggregation function average, which requires it. We are also using the order by clause and ordering the data based on CUSTOMER_KEY. The output of the above query is as below.
You can see that the query selects CUSTOMER_KEY along with the amount. The amount has its average taken; after that, we have the trunc() function come into the picture. The trunc() function truncates the number of values up to 2 places and gives the required output as above. You can also use the trunc() function with queries that perform other operations.
Conclusion
The trunc() function is a very useful mathematical function. You can easily manipulate your mathematical data by making use of this function. You can remove the decimal points at your convenience. It can be upto certain points or completely. The telecom, banking, and retail industry widely use this function when the data is related to them. It helps in manipulating numbers as per the user needs and also helps in making a decimal number into a whole number easily.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL Trunc()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
