PostgreSQL: How to Round Numbers to 2 Decimal Places

Often in PostgreSQL you may want to round the numbers in a specific column to 2 decimal places.

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

SELECT ROUND(points, 2) AS round_points
FROM athletes;

This particular example selects each of the values in the points column of the table named athletes, rounded to 2 decimal places. The result is saved in a new column named round_points.

Note that the first argument in the ROUND function specifies the column to round and the second argument specifies the number of decimal places to round to.

By specifying a value of 2, we are able to round each value in the column to 2 decimal places.

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

Example: Round Numbers to 2 Decimal Places 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 to 2 decimal places.

We can use the following syntax to do so:

SELECT *,
       ROUND(points, 2) AS round_points
FROM athletes;

 athleteid |  team   | points | round_points 
-----------+---------+--------+--------------
         1 | Mavs    | 22.557 |        22.56
         2 | Celtics | 30.347 |        30.35
         3 | Kings   | 12.322 |        12.32
         4 | Kings   | 25.918 |        25.92
         5 | Mavs    |  31.14 |        31.14
         6 | Mavs    |  16.34 |        16.34
         7 | Celtics | 10.596 |        10.60
         8 | Celtics | 14.007 |        14.01
         9 | Kings   | 20.654 |        20.65
        10 | Celtics | 26.328 |        26.33

Notice that the resulting table contains a column named round_points that contains each of the original values from the points column rounded to 2 decimal places.

Note: Feel free to replace the 2 in the ROUND function to instead round numbers to a different number of decimal places.

Additional Resources

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

PostgreSQL: How to Select Rows Having Count Greater Than
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: How to Find Duplicate Records in a Table