Often in PostgreSQL you may want to format numbers in a particular column as a percentage.
You can use the following basic syntax to do so:
SELECT *,
to_char(fg,'990D99%') AS fg_percentage
FROM athletes;
This particular example will create a new column named fg_percentage that contains the values from the fg column formatted as a percentage.
Note that we use the to_char() function to format a column in a specific manner.
We then use 9 as a placeholder for each digit along with D to represent the decimal place, followed by two more digits.
Note #1: We include a 0 before the D so that a number like .88% will be formatted as 0.88% instead.
Note #2: Feel free to use as many 9‘s after the D as you’d like to display even more digits after the decimal place.
The following example shows how to use this syntax in practice.
Example: How to Format Numbers as a Percentage 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,
fg NUMERIC
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 42.51);
INSERT INTO athletes VALUES (0002, 'Hawks', 30.65);
INSERT INTO athletes VALUES (0003, 'Kings', 52.23);
INSERT INTO athletes VALUES (0004, 'Kings', 25.01);
INSERT INTO athletes VALUES (0005, 'Mavs', 31.77);
INSERT INTO athletes VALUES (0006, 'Mavs', 36.98);
INSERT INTO athletes VALUES (0007, 'Hawks', 60.9);
INSERT INTO athletes VALUES (0008, 'Celtics', 43.55);
INSERT INTO athletes VALUES (0009, 'Hawks', 38.3);
INSERT INTO athletes VALUES (0010, 'Hawks', 48.88);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | fg
-----------+---------+-------
1 | Mavs | 42.51
2 | Hawks | 30.65
3 | Kings | 52.23
4 | Kings | 25.01
5 | Mavs | 31.77
6 | Mavs | 36.98
7 | Hawks | 60.9
8 | Celtics | 43.55
9 | Hawks | 38.3
10 | Hawks | 48.88
The fg column displays the field goal percentage for each player.
We can use the following syntax to format the numbers in this column as percentages:
SELECT *,
to_char(fg,'990D99%') AS fg_percentage
FROM athletes;
athleteid | team | fg | fg_percentage
-----------+---------+-------+---------------
1 | Mavs | 42.51 | 42.51%
2 | Hawks | 30.65 | 30.65%
3 | Kings | 52.23 | 52.23%
4 | Kings | 25.01 | 25.01%
5 | Mavs | 31.77 | 31.77%
6 | Mavs | 36.98 | 36.98%
7 | Hawks | 60.9 | 60.90%
8 | Celtics | 43.55 | 43.55%
9 | Hawks | 38.3 | 38.30%
10 | Hawks | 48.88 | 48.88%
The new column named fg_percentage shows the numbers from the fg column formatted as percentages.
Feel free to use similar syntax to display numbers in a column of your own table using a percentage format.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Calculate Percent of Total in Column
PostgreSQL: How to Calculate a Rolling Sum
PostgreSQL: How to Calculate Sum by Group
PostgreSQL: How to Calculate Sum of Multiple Columns
