In PostgreSQL, the ceiling() function is used for rounding a number up to the nearest integer.
It’s similar to round(), except that it only rounds up. The round() function will round up or down as required.
It’s also similar to floor(), except that floor() rounds down instead of up.
Syntax
The ceiling() function can be used in either of the following ways:
ceil(dp or numeric)
ceiling(dp or numeric)Where dp indicates double precision.
The return type is the same as the input type.
Both syntaxes work exactly the same. The first syntax is simply a more concise way of doing it.
Example
Here’s an example to demonstrate its usage.
SELECT ceiling(12.10);Result:
13
In this case the fractional seconds are 10 and the number is rounded up to the nearest integer.
It probably goes without saying, but if I increase the fractional seconds to 50, it still rounds it up.
SELECT ceiling(12.70);Result:
13
Negative Numbers
Here’s an example that uses negative numbers.
SELECT
ceiling(-12.10),
ceiling(-12.90);Result:
ceiling | ceiling ---------+--------- -12 | -12
Ceil() vs Ceiling()
As mentioned, ceil() and ceiling() are equivalent.
Here’s an example that shows both syntaxes side by side.
SELECT
ceil(12.10),
ceiling(12.10);Result:
ceil | ceiling ------+--------- 13 | 13
Ceiling() vs Round()
If I’d been using round(), the 12.10 value would have been rounded down (because the fractional seconds are less than 50).
Here’s an example that illustrates the difference between ceiling() and round().
SELECT
ceiling(12.10),
round(12.10);Result:
ceiling | round ---------+------- 13 | 12
However, if I increase the fractional seconds to 50, they both return the same result.
SELECT
ceiling(12.50),
round(12.50);Result:
ceiling | round ---------+------- 13 | 13
Ceiling() vs Floor()
Postgres also has a floor() function, which is similar to ceiling() except that it always rounds the number down.
Here’s an example that shows both syntaxes side by side.
SELECT
ceiling(12.80),
floor(12.80);Result:
ceiling | floor ---------+------- 13 | 12
And here’s an example that uses negative values.
SELECT
ceiling(-12.30),
floor(-12.30);Result:
ceiling | floor ---------+------- -12 | -13
