3 Ways to Format a Number as a Percentage in PostgreSQL

We have several options if we want to display numbers with a percentage sign in PostgreSQL.

We can use the TO_CHAR() function to format the number along with the percentage sign. Or we can simply concatenate the number with the percentage sign, either with the CONCAT() function or with the concatenation operator.

The TO_CHAR() Function

This option involves passing the number and a numeric template pattern to the function to return the number formatted in the way specified by the template pattern. To get a percentage sign, we include it in our template pattern:

SELECT TO_CHAR(35, 'fm00D00%');

Result:

35.00%

Here, I used the 0 template pattern, which means that the digit position will always be printed, even if it contains a leading/trailing zero.

I also used the fm format modifier to suppress any leading/trailing zeros or blanks.

Here it is with some other template patterns:

SELECT 
    TO_CHAR(7, 'fm00%') AS "1",
    TO_CHAR(7, 'fm99%') AS "2",
    TO_CHAR(7.4567, 'fm0D00%') AS "3",
    TO_CHAR(7, 'fm0D00%') AS "4";

Result:

+-----+----+-------+-------+
|  1  | 2  |   3   |   4   |
+-----+----+-------+-------+
| 07% | 7% | 7.46% | 7.00% |
+-----+----+-------+-------+

We can perform a calculation against the number if required:

SELECT 
    TO_CHAR(35 * 0.1, 'fm99D00%') AS "1",
    TO_CHAR(0.35 * 100, 'fm99D00%') AS "2";

Result:

+-------+--------+
|   1   |   2    |
+-------+--------+
| 3.50% | 35.00% |
+-------+--------+

In this case I include the 9 template pattern so that any leading zeros are omitted.

The CONCAT() Function

Another way to do it is to use the CONCAT() function to concatenate the number and the percentage sign:

SELECT CONCAT(35, '%');

Result:

35%

The Concatenation Operator

Another way to concatenate the number and the percentage sign is to use the concatenation operator (||):

SELECT 35 || '%';

Result:

35%