Numeric Functions

Objectives

By the end of this lesson, you should be able to:

• Round a number to a given number of decimal places with ROUND()
• Round up or down to the nearest whole number with CEIL() and FLOOR()
• Get an absolute value, power, square root, and remainder with ABS(), POWER(), SQRT(), and MOD()

💡 Why this matters: Raw arithmetic (previous lesson) often leaves too many decimal places, or needs rounding in a specific direction. These functions handle that cleanup, along with a handful of other common numeric operations.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

ROUND()

SELECT ROUND(74123.456, 2) AS rounded;

 rounded
----------
 74123.46

ROUND(value, decimal_places) rounds to the given number of decimal places, using standard rounding (0.5 rounds up). Leaving off the second argument, ROUND(value), rounds to the nearest whole number.

CEIL() and FLOOR()

SELECT CEIL(74000.10) AS ceiling, FLOOR(74000.90) AS floored;

 ceiling | floored
---------+---------
   74001 |   74000

CEIL() always rounds up to the next whole number, even for a tiny fractional part (74000.10 becomes 74001). FLOOR() always rounds down, even for a large fractional part (74000.90 becomes 74000). Neither one does standard rounding, they always move in one direction. CEILING() is an accepted alias for CEIL().

ABS()

SELECT ABS(-42) AS absolute;

 absolute
----------
       42

ABS() returns the absolute value, stripping a negative sign. Useful for a difference where only the magnitude matters, not which value was larger, ABS(actual - expected).

POWER() and SQRT()

SELECT POWER(2, 10) AS powered, SQRT(81) AS square_root;

 powered | square_root
---------+-------------
    1024 |           9

POWER(base, exponent) raises a number to a power. SQRT() returns the square root.

MOD()

SELECT MOD(17, 5) AS remainder;

 remainder
-----------
         2

MOD(dividend, divisor) does the same thing as the % operator from the previous lesson, 17 % 5 and MOD(17, 5) both return 2, this is the function form of the same operation.

Try It

1. Write a query that rounds 128.4567 to 1 decimal place.
2. Write a query showing both CEIL(15.01) and FLOOR(15.99) in the same result, and explain why they don’t return the same number even though both inputs round to 15 or 16 under standard rounding.
3. Write a query for ABS(58000 - 95000), the salary gap between two employees, without worrying about which one is subtracted from which.
4. Write a query using MOD() that would identify every employee whose id is even (hint: id % 2 = 0 and MOD(id, 2) = 0 are equivalent).

Recap

• ROUND(value, places) rounds using standard rounding rules, CEIL() always rounds up, FLOOR() always rounds down.
• ABS() strips a negative sign, returning magnitude only.
• POWER(base, exponent) and SQRT() handle exponents and square roots.
• MOD(a, b) is the function form of the % operator.

Next lesson: date and time functions, extracting and calculating with dates.