How Round() Works in PostgreSQL

In PostgreSQL, the round() function is used for rounding a number.

The function can work in one of two ways, depending on how you use it. You can make it round the number to the nearest integer, or to a specified number of decimal places.

This is determined by the number of arguments you pass to the function when you call it.

Syntax

The round() function can be used in either of the following ways:

round(dp or numeric)
round(v numeric, s int)

The first syntax rounds the number to the nearest integer. The dp indicates double precision.

The second syntax rounds it to a specified number of decimal places. In this case, v is the number, and s is the number of decimal places.

The return type for this function is the same as the input type.

Example of First Syntax

Here’s an example that uses the first syntax.

SELECT round(13.50);

Result:

In this case the fractional seconds are 50 and so the number is rounded up to the nearest integer.

If the fractional seconds were less than 50, it would have been rounded down, and the result would be 13.

Here’s an example of that.

SELECT round(13.49);

Result:

Example of Second Syntax

Here’s an example that uses the second syntax.

SELECT round(13.50, 1);

Result:

13.5

In this case, nothing was rounded up and zero was simply dropped. In such cases, it returns the same result that we would have seen if we’d used the trunc() function.

However, if we adjust the fractional seconds slightly, we’ll see that the result is rounded.

SELECT 
  round(13.45, 1) AS "13.45",
  round(13.44, 1) AS "13.44";

Result:

13.45 | 13.44
-------+-------
13.5 | 13.4

However, it’s important to note that the result is completely dependent on how many fractional seconds you specify in the second argument.

Here’s the same example again, but with two decimal places specified.

SELECT 
  round(13.45, 2) AS "13.45",
  round(13.44, 2) AS "13.44";

Result:

13.45 | 13.44
-------+-------
13.45 | 13.44

In this case nothing was rounded, because I specified the exact number of decimal places that I provided.

Adding Fractional Seconds

There’s nothing to stop you specifying more fractional seconds than is in the original number.

SELECT 
  round(13.45, 3) AS "13.45",
  round(13.44, 3) AS "13.44";

Result:

13.45 | 13.44
--------+--------
13.450 | 13.440

In this case it simply adds a zero to the right part of the fractional seconds.

Here’s an example of specifying a number of decimal places when the original number doesn’t include any fractional seconds.

SELECT round(12, 3);

Result:

12.000

Negative Decimal Places

Here’s what happens when the second argument is a negative value.

SELECT round(789, -2);

Result:

Here are some more.

SELECT 
  round(789, -2),
  round(789, -1),
  round(489, -2),
  round(489, -1),
  round(489, -3);

Result:

round | round | round | round | round
-------+-------+-------+-------+-------
800 | 790 | 500 | 490 | 0

Round() vs Trunc()

In some cases, the round() function might appear to work in a similar way to trunc(). But they are quite different.

The round() function rounds the number. The number is rounded to the nearest integer or to the specified number of decimal places (depending on whether you give it one argument or two arguments).

The trunc() function on the other hand simply truncates the number toward zero or to the specified decimal place. No rounding is performed.

Here’s an example to demonstrate the difference.

SELECT 
  round(1.49, 1),
  trunc(1.49, 1);

Result:

round | trunc
-------+-------
1.5 | 1.4