In Postgres, to_char() is a data type formatting function that converts its first argument to a string.

The format of the string is determined by the second argument.

The to_char() function can be used to do the following conversions:

• time stamp to string
• interval to string
• integer to string
• real/double precision to string
• numeric to string

Syntax

The syntax goes like this:

to_char(timestamp, text)
to_char(interval, text)
to_char(int, text)
to_char(double precision, text)
to_char(numeric, text)

Where the first argument is the value to convert (in any of the data types listed) and text is a format string with which to format the first argument.

Example

Here’s an example of formatting a date value:

SELECT to_char(date '2020-12-25', 'Day, DD Month YYYY');

Result:

Friday , 25 December 2020

In this case, the format string Day, DD Month YYYY determines how the date is formatted when it’s returned as a string by to_char().

Template Patterns

The format string consists of one or more template patterns.

In the previous example, my format string was Day, DD Month YYYY. Therefore, it consisted of the following template patterns:

• Day
• DD
• Month
• YYYY

I could have used more or less template patterns, and the output would have been different.

For example, I could have done the following:

SELECT to_char(date '2020-12-25', 'Day');

Result:

Friday

Alternatively, my format string could have consisted of a different series of template patterns altogether.

For example:

SELECT to_char(date '2020-12-25', 'Dy, DD Mon YY');

Result:

Fri, 25 Dec 20

Template Pattern Modifiers

You can also add template pattern modifiers to your format string.

The result will be slightly different depending on the modifier used.

For example, the following example presents the day of the month as an ordinal number.

SELECT to_char(date '2020-12-25', 'Day, DDth Month YYYY');

Result:

Friday , 25th December 2020

This is the same as the first example but the difference is that I added the th template modifier as a suffix to the DD template pattern. This appends the ordinal number suffix to the day of the month.

Therefore we end up with 25th instead of just 25.

To emphasise the effect of the template modifier, here’s how it looks when applied to different values.

SELECT 
  to_char(date '2020-12-01', 'DDth') AS "1",
  to_char(date '2020-12-02', 'DDth') AS "2",
  to_char(date '2020-12-03', 'DDth') AS "3",
  to_char(date '2020-12-04', 'DDth') AS "4";

Result:

  1   |  2   |  3   |  4   
------+------+------+------
 01st | 02nd | 03rd | 04th

I could add another modifier to suppress the leading zeros if required.

SELECT 
  to_char(date '2020-12-01', 'FMDDth') AS "1",
  to_char(date '2020-12-02', 'FMDDth') AS "2",
  to_char(date '2020-12-03', 'FMDDth') AS "3",
  to_char(date '2020-12-04', 'FMDDth') AS "4";

Result:

  1  |  2  |  3  |  4  
-----+-----+-----+-----
 1st | 2nd | 3rd | 4th

In this case I added FM which is for “fill mode”. This modifier suppresses leading zeros and padding blanks.

Numeric

The previous example can also be applied to numeric values.

However, when using numeric values, you’ll need to swap D with either 9 or 0.

SELECT 
  to_char(1, '9th') AS "1",
  to_char(2, '9th') AS "2",
  to_char(3, '9th') AS "3",
  to_char(4, '9th') AS "4";

Result:

  1   |  2   |  3   |  4   
------+------+------+------
  1st |  2nd |  3rd |  4th