Often in PostgreSQL you may want to format numbers in a specific column with commas.
You can use the following syntax to do so:
SELECT *,
to_char(sales, 'FM9,999,999,999') AS sales_formatted
FROM sales_data;
This particular example will create a new column named sales_formatted that contains the number from the sales column formatted with commas.
Note that we use the to_char() function to format a column in a specific manner.
We use FM to represent “fill mode” which suppresses leading zeros and padding blanks.
We then use 9 as a placeholder for each digit with commas separating every three digits.
The following example shows how to use this syntax in practice.
Example: How to Format Numbers with Commas in PostgreSQL
Suppose that we create the following table named sales_datathat contains information about the total number of sales made during various years at some retail store:
-- create table named 'sales'
CREATE TABLE sales_data (
year INT,
sales INT
);
-- add rows to the table
INSERT INTO sales_data VALUES (2019, 5439901);
INSERT INTO sales_data VALUES (2020, 7005443);
INSERT INTO sales_data VALUES (2021, 7704531);
INSERT INTO sales_data VALUES (2022, 7100453);
INSERT INTO sales_data VALUES (2023, 9088431);
INSERT INTO sales_data VALUES (2024, 8611095);
-- view all rows in table
SELECT * FROM sales_data;
year | sales
------+---------
2019 | 5439901
2020 | 7005443
2021 | 7704531
2022 | 7100453
2023 | 9088431
2024 | 8611095
Notice that the numbers in the sales column are not currently formatted with commas.
We can use the following syntax to create a new column that formats the numbers in the sales column with commas:
SELECT *,
to_char(sales, 'FM9,999,999,999') AS sales_formatted
FROM sales_data;
year | sales | sales_formatted
------+---------+-----------------
2019 | 5439901 | 5,439,901
2020 | 7005443 | 7,005,443
2021 | 7704531 | 7,704,531
2022 | 7100453 | 7,100,453
2023 | 9088431 | 9,088,431
2024 | 8611095 | 8,611,095
Notice that the resulting table has a new column named sales_formatted that contains the number from the sales column, formatted with commas.
Feel free to use similar syntax to format numbers with commas in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Numbers from a String
PostgreSQL: How to Pad Numbers with Leading Zeros
PostgreSQL: How to Select Rows where Column Contains Number
