EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL Trunc()

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL Trunc()

PostgreSQL Trunc()

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 that 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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 that 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 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 this is nor specified, then it takes the default value is 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.

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. 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 the trunc() function works when no precision argument is specified.

Popular Course in this category
PostgreSQL Course (2 Courses, 1 Project)2 Online Courses | 1 Hands-on Project | 7+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (2,948 ratings)
Course Price

View Course

Related Courses

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 a 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 see how the trunc() function works when a positive number is sent in the argument as a 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);

PostgreSQL Trunc()-1.3

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

Output-1.4

When the function is taken with a negative precision, it works on the decimal point’s left side. 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 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. 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.

Output-1.5

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 that 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 the 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 the 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 –

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

PostgreSQL Course (2 Courses, 1 Project)

2 Online Courses

1 Hands-on Project

7+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

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

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

Special Offer - PostgreSQL Course (2 Courses, 1 Project) Learn More