PostgreSQL: How to Round Numbers Up to Nearest Integer

Often in PostgreSQL you may want to round the numbers in a column up to the nearest integer.

You can use the CEIL function with the following syntax to do so:

SELECT CEIL(points) AS roundup_points
FROM athletes;

This particular example selects each of the values in the points column of the table named athletes, rounded up to the nearest integer. The result is saved in a new column named roundup_points.

The following example shows how to use this syntax in practice.

Example: Round Numbers Up to Nearest Integer in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  points NUMERIC
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22.557);
INSERT INTO athletes VALUES (0002, 'Celtics', 30.347);
INSERT INTO athletes VALUES (0003, 'Kings', 12.322);
INSERT INTO athletes VALUES (0004, 'Kings', 25.918);
INSERT INTO athletes VALUES (0005, 'Mavs', 31.14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16.34);
INSERT INTO athletes VALUES (0007, 'Celtics', 10.596);
INSERT INTO athletes VALUES (0008, 'Celtics', 14.007);
INSERT INTO athletes VALUES (0009, 'Kings', 20.654);
INSERT INTO athletes VALUES (0010, 'Celtics', 26.328);

-- view all rows in table
SELECT * FROM athletes;

 athleteid |  team   | points 
-----------+---------+--------
         1 | Mavs    | 22.557
         2 | Celtics | 30.347
         3 | Kings   | 12.322
         4 | Kings   | 25.918
         5 | Mavs    |  31.14
         6 | Mavs    |  16.34
         7 | Celtics | 10.596
         8 | Celtics | 14.007
         9 | Kings   | 20.654
        10 | Celtics | 26.328

Suppose that we would like to round each of the values in the points column up to the nearest integer.

We can use the following syntax to do so:

SELECT *,
       CEIL(points) AS roundup_points
FROM athletes;

 athleteid |  team   | points | roundup_points 
-----------+---------+--------+----------------
         1 | Mavs    | 22.557 |             23
         2 | Celtics | 30.347 |             31
         3 | Kings   | 12.322 |             13
         4 | Kings   | 25.918 |             26
         5 | Mavs    |  31.14 |             32
         6 | Mavs    |  16.34 |             17
         7 | Celtics | 10.596 |             11
         8 | Celtics | 14.007 |             15
         9 | Kings   | 20.654 |             21
        10 | Celtics | 26.328 |             27

Notice that the resulting table has a column named roundup_points that contains each of the original values from the points column rounded up to the nearest integer.

For example, we can see:

• The value 22.557 is rounded up to 23.
• The value 30.347 is rounded up to 31.
• The value 12.32 is rounded up to 13.

And so on.

Note: The opposite of the CEIL function is the FLOOR function, which can be used to round numbers down to the nearest integer instead.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Round Numbers to 2 Decimal Places
PostgreSQL: How to Round Numbers to Nearest 10
PostgreSQL: Count Occurrences of Each Value in Column