How Floor() Works in PostgreSQL

In PostgreSQL, the floor() function is used for rounding a number down to the nearest integer.

It’s basically the opposite of ceiling(), which rounds a number up.

It’s also similar to round(), except that it only rounds down. The round() function will round up or down as required.

Syntax

The syntax goes like this:

floor(dp or numeric)

Where dp indicates double precision.

The return type is the same as the input type.

Here’s an example to demonstrate its usage.

SELECT floor(3.99);

Result:

In this case the fractional seconds are 99 and the number is rounded down to the nearest integer.

Not surprisingly, I get the same result if I decrease the fractional seconds to say, 15.

SELECT floor(3.15);

Result:

Here’s an example that uses negative numbers.

SELECT 
  floor(-3.99),
  floor(-3.15);

Result:

 floor | floor
-------+-------
    -4 | -4

Here’s an example that illustrates the difference between floor() and round().

SELECT 
  floor(3.99),
  round(3.99);

Result:

 floor | round
-------+-------
     3 | 4

However, if I decrease the fractional seconds to below 50, they both return the same result.

SELECT 
  floor(3.49),
  round(3.49);

Result:

 floor | round
-------+-------
     3 | 3

Postgres also has a ceiling() function, which is similar to floor() except that it always rounds the number up.

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