PostgreSQL to_char function.

Preamble

PostgreSQL to_char function converts a number or date to a string.

Syntax of to_char function in PostgreSQL

to_char( value, format_mask )

Parameters and function arguments

• value – A number, a date to be converted to a string.
• format_mask – The format that will be used to convert the value to a string. The format_mask is different from whether you convert numbers or dates. Let’s have a look.
• WITH NUMBERS – With format_mask numbers can be one of the following and can be used in many combinations:

With dates

With dates, format_mask can be one of the following and can be used in many combinations.

The to_char function can be used in future versions of PostgreSQL

PostgreSQL 11, PostgreSQL 10, PostgreSQL 9.6, PostgreSQL 9.5, PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4.

Let’s take a look at some examples of to_char functions to see how to_char can be used in PostgreSQL.

Example with numbers

Below are numerical examples of the to_char function.

SELECT to_char(1918, '9999.99');

--Result: 1918.00

SELECT to_char(1814.7, '9G999.99');

--Result: 1,814.70

SELECT to_char(1810.7, 'L9G999.99'); -Result: 1,814.70;

--Result: $ 1,810.70

SELECT to_char(1810.7, 'L9G999');

--Result: $ 1,811

SELECT to_char(141, '9 9 9');

--Result: 1 4 1

SELECT to_char(123, '00999');

--Result: 00123

Example with dates

Below are examples of the dates of the to_char function.

SELECT to_char(date '2019-04-23', 'YYYY/MM/DD');

--Result: 2019/04/23

SELECT to_char(date '2019-04-23', 'MMDDYY');

--Result: 042319

SELECT to_char(date '2019-04-23', 'Month DD, YYYYY');

--Result: April 23, 2019

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.