EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Trunc()
 

PostgreSQL Trunc()

Aanchal Singh
Article byAanchal Singh
EDUCBA
Reviewed byRavi Rathore

Updated May 12, 2023

PostgreSQL Trunc()

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

How Trunc() Function Works in PostgreSQL

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);

PostgreSQL Trunc()-1.1

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);

Output-1.2

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);

PostgreSQL Trunc()-1.3

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);

Output-1.4

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.

Output-1.5

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.

  1. PostgreSQL NULLIF
  2. PostgreSQL COALESCE
  3. Boolean PostgreSQl
  4. Examples of PostgreSQL round

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW