Mathematical Functions
beginneradvanced
2 min read
What are Mathematical Functions?
PostgreSQL provides a wide range of mathematical functions for performing calculations on numeric data directly in SQL queries.
Common Functions
ROUND(num, decimals) -- Round to N decimal places
CEIL(num) / CEILING(num) -- Round up to nearest integer
FLOOR(num) -- Round down to nearest integer
ABS(num) -- Absolute value
MOD(a, b) or a % b -- Modulo (remainder)
POWER(base, exp) -- Exponentiation
SQRT(num) -- Square root
GREATEST(a, b, ...) -- Largest value
LEAST(a, b, ...) -- Smallest valueWhen to Use
- Rounding prices for display
- Computing distance, percentages, or growth rates
- Normalizing data for analysis
- Generating random values with RANDOM()
Key Points
- ROUND — ROUND(3.456, 2) returns 3.46. ROUND(3.456) returns 3.
- Integer Division — In PostgreSQL, 5 / 2 = 2 (integer). Cast to get decimals: 5.0 / 2 = 2.5.
- RANDOM() — Returns a random float between 0 and 1. Useful for sampling.
- GREATEST/LEAST — Return the max/min of a list of values (not aggregate functions).
- Precision — Use NUMERIC type for exact decimal arithmetic; FLOAT for approximate.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Mathematical Functions Challenge
Write a query that solve this task: round prices to the nearest integer.
Expected result
Products with prices rounded to the nearest whole number.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are Mathematical Functions?
Initializing database...Each run starts from fresh sample data.
More Examples
Calculate percentage
Show each student's age as a percentage of the max age.
Initializing database...Each run starts from fresh sample data.
Random sample of rows
Get 5 random students.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Why does 5/2 return 2 instead of 2.5?
PostgreSQL performs integer division when both operands are integers. Cast one to numeric or float: 5::numeric / 2 returns 2.5.
What is the difference between GREATEST and MAX?
GREATEST compares values in a single row. MAX is an aggregate that finds the maximum across rows.