Mathematical functions (ROUND, CEIL, FLOOR, ABS) in PostgreSQL

Introduction

Mathematical functions help you change numbers in useful ways, like rounding or finding absolute values.

When you want to round prices to the nearest whole number.

When you need to find the smallest integer greater than or equal to a number.

When you want to get the largest integer less than or equal to a number.

When you want to find the positive value of a number, ignoring its sign.

Syntax

PostgreSQL

ROUND(number, [decimal_places])
CEIL(number)
FLOOR(number)
ABS(number)

ROUND can round to a specific number of decimal places.

CEIL and FLOOR always return whole numbers.

Examples

Rounds 3.14159 to 2 decimal places, result is 3.14.

PostgreSQL

SELECT ROUND(3.14159, 2);

Returns 5, the smallest integer greater than or equal to 4.2.

PostgreSQL

SELECT CEIL(4.2);

Returns 4, the largest integer less than or equal to 4.8.

PostgreSQL

SELECT FLOOR(4.8);

Returns 7, the positive value of -7.

PostgreSQL

SELECT ABS(-7);

Sample Program

This query shows how each function works on the number 5 and -5.

PostgreSQL

SELECT ROUND(5.6789, 1) AS rounded_value,
       CEIL(5.1) AS ceiling_value,
       FLOOR(5.9) AS floor_value,
       ABS(-5) AS absolute_value;

Important Notes

ROUND without the second argument rounds to the nearest whole number.

CEIL and FLOOR always return integers, even if input is already an integer.

ABS works on both integers and decimals.

Summary

ROUND changes numbers to a set number of decimals.

CEIL finds the smallest integer not less than the number.

FLOOR finds the largest integer not greater than the number.

ABS returns the positive value of any number.