PostgreSQL TO_CHAR Function

Summary: In this tutorial, you’ll learn how to convert a value of various types to a string based on a pattern using the PostgreSQL TO_CHAR function.

PostgreSQL TO_CHAR Function Overview #

The TO_CHAR function allows you to format a value to a string based on a specified format.

Here’s the syntax of the TO_CHAR function:

TO_CHAR(value, format)

The TO_CHAR function takes two parameters:

• value – The value to format. It can be a date, time, timestamp, interval, or number.
• format – The format string.

Formatting Date Example #

The following example uses the TO_CHAR function to format the date '2025-03-19' to a string:

SELECT
  TO_CHAR(DATE '2025-03-19', 'Mon DD, YYYY') result;Code language: JavaScript (javascript)

Try it

Output:

    result
--------------
 Mar 19, 2025

Formatting Number Example #

The following example uses the TO_CHAR function to format a number to a string:

SELECT
  TO_CHAR(12345.678, '99,999.99') result;Code language: JavaScript (javascript)

Try it

Output:

   result
------------
  12,345.68Code language: CSS (css)

Here are common number format patterns:

• 9 – Represents a digit (optional if not present).
• 0 – Represents a digit (forced, even if it’s a leading zero).
• , – Thousand separator.
• . – Decimal point.

Formatting Currency #

The following statement uses the TO_CHAR function to format a number to a currency value:

SELECT
  TO_CHAR(12345.67, 'L99G999D99') result;Code language: JavaScript (javascript)

Output:

   result
-------------
 $ 12,345.67

Note: The output depends on the current locale. The Playground does not display the $ symbol.

In the format string 'L99G999D99':

• L – Currency symbol (based on locale).
• G – Group separator (thousands separator).
• D – Decimal point.

Formatting with Roman Numerals #

The following example uses the TO_CHAR function to format a number to a Roman numeral:

SELECT
  TO_CHAR(2000, 'RN') result;Code language: JavaScript (javascript)

Try it

Output:

     result
-----------------
              MM

In this example, the format string RN instructs the TO_CHAR to format a number to a Roman numeral.

Summary #

• Use the TO_CHAR function to format a value to a string according to a format.