Understanding PostgreSQL: to_char and to_number Conversion Functions

Overview

The conversion functions to_char and to_number in PostgreSQL are powerful tools for formatting numbers and dates as strings and converting strings into numbers, respectively. Since PostgreSQL is an evolving database system, new functions and features are introduced with each version. The exact version in which to_char and to_number were introduced predates the public changelogs, but they’ve been present at least since version 7.3, released in 2002 (more than 22 years ago), making them well-established features in PostgreSQL.

The purpose of the to_char function is to convert various data types such as integer, floating-point numbers, and timestamp values into a formatted text string. On the other hand, the to_number function is used to convert a formatted text string into a numeric value.

Syntax and Parameters

The to_char function:

to_char(value, format)

Where:

• value: The value to format, which can be a number or date/time.
• format: The format string, which uses template patterns for formatting.

The to_number function:

to_number(text, format)

Here:

• text: The text string to convert to a number.
• format: The format string, which uses the same template patterns as to_char for understanding the text’s format.

The returned value from each function corresponds to the output of the respective operation, i.e., a formatted string for to_char and a numeric value for to_number.

Examples

Here are some code examples showing how to use these conversion functions in PostgreSQL.

Example 1: Formatting Dates

This example demonstrates how to use to_char to format a TIMESTAMP value into a string.

SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS formatted_date;

Example 2: Formatting Numbers

Summary: This example illustrates formatting a number into a currency string.

SELECT to_char(123456.789, 'FM$999,999,999.00') AS formatted_number;

Example 3: Parsing Text to Number

Summary: This example shows how to parse a formatted number string back into a numeric type.

SELECT to_number('$123,456.78', 'FM$999,999,999.00') AS parsed_number;

Conclusion

In summary, the to_char and to_number functions are versatile and essential for proper data representation in PostgreSQL. Whether for formatting or parsing, they adhere to specific patterns that allow consistency and accuracy in data-driven applications. Well-formatted data contributes to the readability and potentially to the internationalization of PostgreSQL applications, making these functions crucial in a developer’s toolkit.