PostgreSQL – TO_CHAR Function

In PostgreSQL, the TO_CHAR function is used to convert a value, such as a date, time, or number, to a string with a specified format. This function is particularly useful when you need to format the output of date, time, or numeric data types according to a specific pattern.

The syntax of the TO_CHAR function is as follows:

TO_CHAR(expression, format)

Here’s an example of using the TO_CHAR function to format a date:

SELECT TO_CHAR(current_date, 'YYYY-MM-DD') AS formatted_date;

In this example, the current_date is formatted as ‘YYYY-MM-DD’, which will return the current date in the format like ‘2023-08-12’.

You can also use the TO_CHAR function to format numeric values:

SELECT TO_CHAR(12345.6789, '999,999.99') AS formatted_number;

In this example, the numeric value 12345.6789 is formatted as ‘12,345.68’.

Additionally, you can use TO_CHAR to format timestamps and times:

SELECT TO_CHAR(current_timestamp, 'YYYY-MM-DD HH:MI:SS') AS formatted_timestamp; 
SELECT TO_CHAR(current_time, 'HH12:MI:SS AM') AS formatted_time;

The TO_CHAR function’s second parameter specifies the formatting pattern using special placeholders like ‘YYYY’, ‘MM’, ‘DD’, ‘HH12’, ‘MI’, ‘SS’, and more. These placeholders determine how the value is formatted in the output string.

Keep in mind that the available formatting options depend on the data type you are working with. The TO_CHAR function gives you significant flexibility in presenting your data in various ways, making your query results more readable and visually appealing.