Definition of PostgreSQL Trunc()
The trunc() function in PostgreSQL is used to truncate the decimal places to a certain precision. It can also return a number which is truncated to the whole number if there is no precision defined. The trunc() function is a mathematical function present in PostgreSQL. This function helps in manipulating numbers as required. The decimals can be modified to decimal places as needed by the project and make use. Let us have a look at the function and its different uses.
Syntax:
The following syntax illustrates the syntax of PostgreSQL trunc() function.
Trunc (number [, precision])
Parameters:
- Number: Here the number signifies the number which is supposed to be truncated.
- Precision: This argument is an optional argument. It signifies an integer value which will let us know the number of decimal places that are to be truncated.
- If this precision number is a positive integer, the trunc() function will truncate those number of digits which 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 this is nor specified, then it takes the default value as 0 which makes the number to be truncated 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. When the trunc() function is used the compiler looks for the precision argument. It finds the number 1 which is the precision argument. It then takes both arguments and applies the trunc() on this number. Once this is done it takes only 1 decimal number from 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. This truncated value can be further used. The trunc() function is useful in all queries where the decimal point needs to be truncated of the number is needed as a whole number.
Examples to Implement TRUNC() in PostgreSQL
We now know what the trunc() function is 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. In this example, we will see how trunc() function works when there is no precision argument specified.
select trunc(125.135);
Another example here will eliminate your doubt that if the number is having the digits after the decimal point as 99 will the number be rounded off. Let us take a number: 125.999 and apply the trunc() function to it.
select trunc(125.999);
As you can see the result, the number is not rounded off. It simply truncates the numbers after the decimal point.
2. In this example, we will see how the trunc() function works when a positive number is sent in the argument as precision value. We have taken a number 123.456 with the 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 to digits are truncated to 0.
3. In this example, we will see how the trunc() function works when a negative number is sent in the argument as precision value.
We have taken a number 150.45 with the precision of -2. The PostgreSQL statement for this will be as follows:
SELECT trunc(150.45, -2);
When the function is taken with a negative precision it works upon the left side of the decimal point. It will take the numbers which are 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 try to truncate the numbers which are being 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 takes two tables into consideration. It takes the amount of value from the BL1_RC_RATES table. This column takes decimal values and we truncate it 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. There is also group by clause taken as we are using an aggregation function average which will need a group by clause. We are also using the order by clause and ordering the data on the basis of CUSTOMER_KEY. The output of the above query is as below.
You can see that CUSTOMER_KEY is selected along with the amount. The amount has its average taken and 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. Hence the trunc() function can also be used with queries which also 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. The decimal points can be removed at your convenience. It can be upto certain points or completely. This function is widely used when the data is related to telecom, banking, and retail industry. It helps in manipulating numbers as per the user needs and also helps in making a decimal number to a whole number easily.
Recommended Articles
This is a guide to PostgreSQL Trunc(). Here we also discuss the definition and how trunc() function works in PostgreSQL Trunc() along with different examples and code implementation. You may also have a look at the following articles to learn more –
