How to Create Ordinal Numbers in PostgreSQL

In PostgreSQL, you can use the to_char() function to output numbers in a given format. This includes appending the number with the ordinal indicator.

For example 1, 2, 3 becomes 1st, 2nd, 3rd.

Modifiers for Ordinal Indicators

The following template pattern modifiers can be used to apply the ordinal number suffix.

Modifier	Description
`th`	Lowercase ordinal number suffix.
`TH`	Uppercase ordinal number suffix.

Example

Here’s an example.

SELECT to_char(1, '9th');

Result:

1st

Here’s the output when applied to more numbers.

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

And here it is with larger numbers.

SELECT 
  to_char(101, '999th') AS "11",
  to_char(102, '999th') AS "12",
  to_char(103, '999th') AS "13",
  to_char(104, '999th') AS "14";

Result:

   11   |   12   |   13   |   14   
--------+--------+--------+--------
  101st |  102nd |  103rd |  104th

Uppercase Ordinal Indicator

Here it is again but using TH instead of th so that the ordinal number suffix is output in uppercase.

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