In databases, we often need to perform mathematical calculations on stored data.
For example, a university may need to:
PostgreSQL provides built-in mathematical functions to perform these calculations directly in SQL queries.
Mathematical functions are built-in PostgreSQL functions that perform calculations on numeric values.
For example:
SELECT ROUND(85.756, 2);
Output:
85.76
Here, ROUND() is a mathematical function that rounds a number to the specified number of decimal places.
We will use a table named student_results to understand mathematical functions.
CREATE TABLE student_results (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
marks NUMERIC(5,2),
fee NUMERIC(10,2)
);
Insert sample records:
INSERT INTO student_results
VALUES
(1, 'Ali', 85.75, 12500.50),
(2, 'Sara', 92.40, 15000.75),
(3, 'Ahmed', 67.25, 10000.00),
(4, 'Hina', 78.60, 13500.25),
(5, 'Usman', 55.50, 9000.50);
Display the records:
SELECT * FROM student_results;
The ROUND() function rounds a number to the nearest integer or a specified number of decimal places.
Syntax:
ROUND(number, decimal_places)
Examples:
SELECT ROUND(85.756, 2);
Output:
85.76
Round to one decimal place:
SELECT ROUND(85.756, 1);
Output:
85.8
Round to the nearest whole number:
SELECT ROUND(85.756);
Output:
86
Practical example:
Display student marks rounded to one decimal place.
SELECT
student_name,
ROUND(marks, 1) AS rounded_marks
FROM student_results;
The CEIL() function rounds a number upward to the nearest integer.
PostgreSQL also supports CEILING(), which works in the same way.
Examples:
SELECT CEIL(4.2);
Output:
5
Another example:
SELECT CEILING(7.1);
Output:
8
Remember:
CEIL(4.2) returns 5.CEIL(-4.2) returns -4.Practical example:
SELECT
student_name,
CEIL(marks) AS rounded_marks
FROM student_results;
The FLOOR() function rounds a number downward to the nearest integer.
Examples:
SELECT FLOOR(4.9);
Output:
4
Another example:
SELECT FLOOR(8.7);
Output:
8
Remember:
FLOOR(4.9) returns 4.FLOOR(-4.2) returns -5.Practical example:
SELECT
student_name,
FLOOR(marks) AS rounded_marks
FROM student_results;
The TRUNC() function removes decimal digits without rounding the number.
Syntax:
TRUNC(number, decimal_places)
Example:
SELECT TRUNC(85.759, 2);
Output:
85.75
Compare ROUND() and TRUNC():
SELECT ROUND(85.759, 2);
Output: 85.76
SELECT TRUNC(85.759, 2);
Output: 85.75
Practical example:
SELECT
student_name,
TRUNC(marks, 1) AS truncated_marks
FROM student_results;
The ABS() function returns the absolute value of a number.
An absolute value is always zero or positive.
Examples:
SELECT ABS(-25);
Output:
25
SELECT ABS(25);
Output:
25
Practical example:
Calculate the absolute difference between two students’ marks.
SELECT ABS(85.75 - 67.25) AS marks_difference;
Output:
18.50
The POWER() function raises a number to a specified exponent.
Syntax:
POWER(base, exponent)
Example:
SELECT POWER(2, 3);
Output:
8
Explanation:
\[ 2^3 = 2 \times 2 \times 2 = 8 \]
Another example:
SELECT POWER(5, 2);
Output:
25
The SQRT() function calculates the square root of a number.
Example:
SELECT SQRT(81);
Output:
9
Another example:
SELECT SQRT(64);
Output:
8
A negative input produces an error for real-valued square roots.
The MOD() function returns the remainder after division.
Syntax:
MOD(dividend, divisor)
Example:
SELECT MOD(10, 3);
Output:
1
Explanation:
\[ 10 \div 3 = 3 \text{ remainder } 1 \]
Another example:
SELECT MOD(20, 5);
Output:
0
Practical example: Check even and odd numbers
SELECT MOD(10, 2);
Output: 0
If the remainder is 0, the number is even. Otherwise, it is odd.
The SIGN() function tells us whether a number is positive, negative, or zero.
It returns:
1 for a positive number.-1 for a negative number.0 for zero.Examples:
SELECT SIGN(25);
Output: 1
SELECT SIGN(-25);
Output: -1
SELECT SIGN(0);
Output: 0
The PI() function returns the mathematical constant π (pi).
SELECT PI();
Output is approximately:
3.141592653589793
Practical example: Calculate the area of a circle
Formula:
\[ Area = \pi \times r^2 \]
SQL:
SELECT PI() * POWER(5, 2) AS circle_area;
Output is approximately:
78.54
The EXP() function calculates the exponential value of a number.
It raises the mathematical constant \(e\) to the specified power.
Example:
SELECT EXP(1);
Output is approximately:
2.718281828459045
Another example:
SELECT EXP(2);
These functions calculate logarithms.
The LN() function calculates the natural logarithm (base \(e\)).
SELECT LN(10);
The LOG() function calculates the base-10 logarithm when given one argument.
SELECT LOG(100);
Output:
2
You can also specify a base:
SELECT LOG(2, 8);
Output:
3
Explanation:
\[ 2^3 = 8 \]
The RANDOM() function generates a random decimal number between 0.0 (inclusive) and 1.0 (exclusive).
SELECT RANDOM();
Each execution may produce a different result.
Generate a random number between 1 and 10:
SELECT FLOOR(RANDOM() * 10) + 1 AS random_number;
This function is useful for practice exercises and random sampling. It should not be used for security-sensitive random values.
The GREATEST() function returns the largest value from a list of expressions.
Example:
SELECT GREATEST(10, 25, 15, 40, 5);
Output:
40
Practical example:
Find the highest marks among three values.
SELECT GREATEST(85, 92, 78) AS highest_marks;
The LEAST() function returns the smallest value from a list of expressions.
Example:
SELECT LEAST(10, 25, 15, 40, 5);
Output:
5
Practical example:
Find the lowest marks among three values.
SELECT LEAST(85, 92, 78) AS lowest_marks;
PostgreSQL also provides arithmetic operators for basic calculations.
| Operator | Operation | Example | Result |
|---|---|---|---|
+ |
Addition | 10 + 5 |
15 |
- |
Subtraction | 10 - 5 |
5 |
* |
Multiplication | 10 * 5 |
50 |
/ |
Division | 10.0 / 2 |
5 |
% |
Remainder | 10 % 3 |
1 |
^ |
Power | 2 ^ 3 |
8 |
Examples:
SELECT 10 + 5 AS addition;
SELECT 10 - 5 AS subtraction;
SELECT 10 * 5 AS multiplication;
SELECT 10.0 / 3 AS division;
SELECT 10 % 3 AS remainder;
SELECT 2 ^ 3 AS power;
Important: Integer division
When both operands are integers, PostgreSQL removes the fractional part:
SELECT 10 / 3;
Output:
3
To get a decimal result, use a decimal or numeric value:
SELECT 10.0 / 3;
Now let’s use mathematical functions with our student_results table.
SELECT
student_name,
fee,
fee + 500 AS updated_fee
FROM student_results;
SELECT
student_name,
fee,
ROUND(fee * 1.05, 2) AS increased_fee
FROM student_results;
SELECT
student_name,
ROUND(marks, 2) AS rounded_marks
FROM student_results;
SELECT
student_name,
marks,
ABS(100 - marks) AS remaining_marks
FROM student_results;
SELECT
student_name,
ROUND(marks) AS rounded_marks
FROM student_results
ORDER BY marks DESC;
SELECT
MAX(marks) AS highest_marks,
MIN(marks) AS lowest_marks
FROM student_results;
ROUND() rounds a number.TRUNC() removes decimal digits without rounding.CEIL() rounds upward.FLOOR() rounds downward.ABS() returns a positive absolute value.POWER() calculates powers.SQRT() calculates square roots.MOD() returns the remainder.GREATEST() returns the largest value.LEAST() returns the smallest value.NUMERIC for financial values where exact decimal precision matters.NULL when their input is NULL.Exercise 1: Write a query to round 78.456 to two decimal places.
Exercise 2: Find the absolute value of -150.
Exercise 3: Find the square root of 144.
Exercise 4: Calculate the remainder when 25 is divided by 4.
Exercise 5: Find the largest value among 15, 30, 45, and 20.
Exercise 6: Display each student’s name and marks rounded to the nearest whole number.
Exercise 7: Display each student’s fee after increasing it by 10%.
Exercise 8: Display student names and the absolute difference between their marks and 100.
Exercise 9: Display student fees after removing decimal digits without rounding.
Exercise 10: Generate a random integer between 1 and 100.
Exercise 11: Display student names, original fees, and fees after a 7.5% increase. Round the new fees to two decimal places.
Exercise 12: Find the difference between the highest and lowest marks in the table.
Exercise 13: Display the remainder when each student’s student_id is divided by 2. Use it to identify even and odd student IDs.
Exercise 14: Calculate the area of a circle with a radius of 7 using PI() and POWER().
Exercise 15: Write a query that displays each student’s marks rounded to one decimal place and sorts the result from highest to lowest.
ROUND() and TRUNC()?ABS()?CEIL() differ from FLOOR()?MOD() function return?POWER()?GREATEST() and LEAST()?NUMERIC data type useful for financial calculations?| Function | Purpose |
|---|---|
ROUND() |
Rounds a number |
CEIL() |
Rounds upward |
FLOOR() |
Rounds downward |
TRUNC() |
Removes decimal digits |
ABS() |
Returns absolute value |
POWER() |
Calculates power |
SQRT() |
Calculates square root |
MOD() |
Returns remainder |
SIGN() |
Identifies number sign |
PI() |
Returns pi |
EXP() |
Calculates exponential |
LN() |
Natural logarithm |
LOG() |
Logarithm |
RANDOM() |
Generates random number |
GREATEST() |
Returns largest value |
LEAST() |
Returns smallest value |
Next Lesson: JSON and JSONB in PostgreSQL
In the next lesson, students will learn how PostgreSQL stores and retrieves JSON data, how to use JSON operators, and how to work with structured data inside a database column.