Often in PostgreSQL you may want to convert an integer column to a decimal column.
You can use the following syntax to do so:
ALTER TABLE athletes ALTER COLUMN points TYPE DECIMAL(10,2);
This particular example converts the integer column named points to a decimal column with two values after the decimal point.
The first number in the DECIMAL() function represents the max length of the number, excluding the decimal point. We chose a max length of 10 in this example.
The second number in the DECIMAL() function represents the number of values to display after the decimal point. We chose to display 2 values after the decimal point in this example.
The following example shows how to use this syntax in practice.
Example: How to Convert an Integer to Decimal in PostgreSQL
Suppose that we create the following table named athletes that contains information about points scored by various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Hawks', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points
-----------+---------+--------
1 | Mavs | 22
2 | Hawks | 30
3 | Kings | 12
4 | Kings | 25
5 | Mavs | 31
6 | Mavs | 16
7 | Hawks | 10
8 | Celtics | 14
9 | Hawks | 20
10 | Hawks | 26
Suppose that we would like to convert each integer value in the points column to a decimal with two values after the decimal point.
We can use the following syntax to do so:
-- convert points column from integer to decimal
ALTER TABLE athletes ALTER COLUMN points TYPE DECIMAL(10,2);
-- view updated table
SELECT * FROM athletes;
athleteid | team | points
-----------+---------+--------
1 | Mavs | 22.00
2 | Hawks | 30.00
3 | Kings | 12.00
4 | Kings | 25.00
5 | Mavs | 31.00
6 | Mavs | 16.00
7 | Hawks | 10.00
8 | Celtics | 14.00
9 | Hawks | 20.00
10 | Hawks | 26.00
When we view the updated athletes table, we can see that the points column has been converted from an integer to a decimal with two values shown after the decimal place.
Feel free to use similar syntax to convert an integer to a decimal in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Round Numbers Up to Nearest Integer
PostgreSQL: How to Round Numbers to 2 Decimal Places
PostgreSQL: How to Generate a Random Integer
