🌱BeginnerFiltering & Sorting
Mathematical Functions and Operators
Perform calculations using arithmetic operators and PostgreSQL math functions.
Overview
PostgreSQL supports the full range of arithmetic operators (+, -, *, /, %) and a rich library of mathematical functions. These are used constantly in SELECT expressions for computing values, in WHERE for filtering, and in UPDATE for modifying numeric columns.
Key things to remember: integer division in SQL truncates toward zero (10 / 3 = 3, not 3.33). Use 10.0 / 3 or cast to NUMERIC for decimal division. The % operator is modulo (remainder). ROUND, CEIL, FLOOR, and TRUNC are for rounding.
Key Points
- •+ - * / % basic arithmetic; / between integers truncates
- •ABS(n) absolute value
- •ROUND(n, d) round to d decimal places
- •CEIL(n) / CEILING(n) round up; FLOOR(n) round down
- •SQRT(n) square root; POWER(b, e) b to the power e
- •MOD(a, b) or a % b remainder
Examples
Basic arithmetic and rounding
SELECT 10 / 3; -- 3 (integer division)
SELECT 10.0 / 3; -- 3.3333...
SELECT 10 % 3; -- 1 (modulo)
SELECT ABS(-42); -- 42
SELECT ROUND(3.567, 2); -- 3.57
SELECT CEIL(3.1); -- 4
SELECT FLOOR(3.9); -- 3
SELECT TRUNC(3.9); -- 3 (truncate, no rounding)Power, square root, and random
SELECT POWER(2, 10); -- 1024
SELECT SQRT(144); -- 12
SELECT PI(); -- 3.14159...
SELECT RANDOM(); -- random number between 0 and 1
-- Random integer 1-100:
SELECT FLOOR(RANDOM() * 100 + 1)::INTEGER;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4