How Ceiling() Works in PostgreSQL

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.

Here’s an example to demonstrate its usage.

SELECT ceiling(12.10);

Result:

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:

Here’s an example that uses negative numbers.

SELECT 
  ceiling(-12.10),
  ceiling(-12.90);

Result:

 ceiling | ceiling
---------+---------
     -12 | -12

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

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

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