Math functions

Rounding, absolute values, powers, logarithms and related numeric functions.

Syntax

round(x [, digits])  trunc(x [, digits])  ceil(x)  floor(x)
abs(x)  sign(x)  mod(a, b)  power(a, b)  sqrt(x)  exp(x)  ln(x)  log(x)  log(b, x)
greatest(a, b, ...)  least(a, b, ...)  random()  width_bucket(x, lo, hi, n)

round(x, n) rounds half away from zero for numeric; for double precision it rounds half to even. round(double precision, integer) does not exist — cast first: round(avg(x)::numeric, 2). (avg of integer/numeric returns numeric; avg of real/double precision, random() and percentile_cont return double precision.)
trunc(x, n) drops digits without rounding; ceil rounds up, floor rounds down (towards −∞).
greatest / least return the largest / smallest argument, ignoring NULLs.
width_bucket(x, lo, hi, n) assigns x to one of n equal-width buckets (0 or n+1 outside the range) — quick histograms.
numeric is exact (money); real / double precision are approximate (0.1 + 0.2 ≠ 0.3).
NULL: all of them return NULL for NULL input (except greatest/least, which skip NULLs).

Examples

Rounding variants starter

SELECT round(2.345, 2) AS round2, trunc(2.349, 2) AS trunc2,
       ceil(2.1) AS ceil, floor(-2.1) AS floor,
       round(2.5) AS numeric_half, round(2.5::float8) AS float_half,
       greatest(3, NULL, 7) AS g;

numeric 2.5 rounds to 3, double precision 2.5 rounds to 2 (half to even); greatest skips the NULL.

Rounding an average for a report people

SELECT reason, round(avg(base_salary), 2) AS avg_base
FROM salary_history
GROUP BY reason
ORDER BY reason;

avg of a numeric column is numeric, so round(x, 2) works directly.

Histogram with width_bucket shop

SELECT width_bucket(unit_price, 0, 2000, 4) AS bucket,
       min(unit_price) AS from_price, max(unit_price) AS to_price, count(*) AS products
FROM products
GROUP BY bucket
ORDER BY bucket;

Four 500-SAR buckets; bucket 5 holds anything at or above 2000.

Pitfalls

round(double_precision_value, 2): function round(double precision, integer) does not exist. Cast to numeric.
Storing money in real/double precision (rounding errors). Use numeric.
Expecting floor(-2.1) to be -2 (it is -3).

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: ROUND(x, n, 1) truncates; CEILING instead of ceil; % for modulo; no GREATEST/LEAST before 2022.
Oracle: MOD(a, b) instead of %; GREATEST/LEAST return NULL if any argument is NULL.
MySQL: GREATEST/LEAST return NULL if any argument is NULL.