In PostgreSQL, we can use the TO_CHAR() function to format numbers in a given format. This includes formatting numbers with commas in the relevant location.

PostgreSQL also has a money data type, which outputs the value using the current locale. This can include commas in the appropriate place, based on the locale being used.

The TO_CHAR() Function

Here’s an example of using the TO_CHAR() function to output a number with commas:

SELECT TO_CHAR(123456.78, 'fm999G999D99');

Result:

123,456.78

The second argument consists of a bunch of numeric template patterns that determine how the first argument is formatted.

Here, I used the G template pattern for a locale-aware group separator (also referred to as a “thousands separator”). I could have used a comma (,), but that would not have been locale-aware.

I also used the D template pattern for a locale-aware decimal separator.

In this case, I also used fm (which stands for “Fill Mode”) to suppress any trailing zeros and leading blanks that may have been automatically applied to the result.

The 9 template pattern is for each digit. You could use 0 instead if you want to include leading zeros.

Because the G and D template patterns are locale-aware, they are able to produce the appropriate character for the group and decimal separators.

Here’s an example to demonstrate what I mean:

SET lc_numeric = 'fr_FR';
SELECT TO_CHAR(123456.78, 'fm999G999D99');

Result:

123.456,78

Now, the group separator is represented by a period/full stop, and the decimal separator is represented by a comma. This is because I changed the lc_numeric variable to use fr_FR (for France), and it is the French custom to have its group separator and decimal point represented in this way.

Different locales have different conventions for formatting numbers, and the G and D format modifiers are able to determine which characters to use for group separators and the decimal separator.

The money Data Type

PostgreSQL has a money data type that outputs its values formatted in a locale aware fashion.

Example:

SET lc_monetary = 'en_US';
SELECT CAST(123456.78 AS money);

Result:

$123,456.78

Now let’s change the lc_monetary variable and see how this affects the output:

SET lc_monetary = 'fr_FR';
SELECT CAST(123456.78 AS money);

Result:

123 456,78 Eu

In this case, the group separator is represented by a space, and the decimal separator is a comma.