PostgreSQL TO_CHAR function – How to use + Examples

In this PostgreSQL tutorial, we will discuss the PostgreSQL TO_CHAR() function to convert the timestamp, numeric, etc., type value data into a formatted string, and will also cover the following topics:

• PostgreSQL TO_CHAR function
• PostgreSQL TO_CHAR format date
• PostgreSQL TO_CHAR date locale
• PostgreSQL TO_CHAR add characters to date
• PostgreSQL TO_CHAR quarter
• PostgreSQL TO_CHAR time format
• PostgreSQL TO_CHAR 24 hour
• PostgreSQL TO_CHAR timestamp with timezone
• PostgreSQL TO_CHAR timezone offset
• PostgreSQL TO_CHAR number format
• PostgreSQL TO_CHAR currency
• PostgreSQL TO_CHAR percentage
• PostgreSQL TO_CHAR signed integers
• PostgreSQL TO_CHAR into roman number
• PostgreSQL TO_CHAR ordinal suffix
• PostgreSQL TO_CHAR exponent
• PostgreSQL TO_CHAR shift decimal point
• PostgreSQL TO_CHAR add string to number

PostgreSQL TO_CHAR function

PostgreSQL provides some data type formatting functions that provide a very powerful set of tools to convert various data types (like date/time, integer, numeric, floating-point) to formatted strings and vice versa.

TO_CHAR() is one of these data type formatting functions which is used to convert a date/time (timestamp), a numeric, an integer, an interval, or a double-precision type to a formatted string. The syntax is as follows:

TO_CHAR(expression, format)

In the above syntax,

• The expression is the argument that specifies the value of the the data type to be converted into the string according to the given format. The expression can be a timestamp, a numeric, an integer, an interval, or a double precision value.
• The format specifies the pattern (format) in which the returned output (formatted string) you want.
• The TO_CHAR() function returns a TEXT type string, which represents the formatted string output after converting the first argument expression on applying the specified format pattern to it.

The numeric value formatting patterns available in PostgreSQL:

format	Description
9	Digit position, and is used to get the numeric value with specified number of digits (can be removed if insignificant)
0	Digit position, and is used to get the numeric value with leading zeros (will not be removed, even if insignificant)
. (period)	Decimal point
, (comma)	Group separator (thousands separator for currency, etc)
FM	Suppresses the blank padding and leading zeroes (Fill Mode)
PR	A negative value in angle brackets
S	Sign anchored to a number (that uses locale)
L	Currency symbol (that uses locale)
D	Decimal point (that uses locale)
G	Group separator (that uses locale)
MI	Minus sign in specified position for the numbers less than 0
PL	Plus sign in specified position for the numbers greater than 0
SG	Plus/Minus sign in the specified position
RN	Roman numeral for the number that ranges from 1 to 3999
V	Shift specified number of digits (It multiplies the value by 10 raised to the power n, where n = number of digits following V).
TH / th	TH for Upper case and the for lower case ordinal number suffix
EEE	Exponent for the scientific notation

The date/time (timestamp) value formatting patterns available in PostgreSQL:

format	Description
SS	It specifies the second (00-59)
MS	It specifies the millisecond (000-999)
US	It specifies the microsecond (000000-999999)
FF1	It specifies the tenth of a second (0-9)
FF2	It specifies the hundredth of a second (00-99)
FF3	It specifies the thousandth of a second that is a millisecond (000-999)
FF4	It specifies the tenth of a millisecond (0000-9999)
FF5	It specifies the hundredth of a millisecond (00000-99999)
FF6	It specifies the thousandth of a millisecond that is a microsecond (000000-999999)
SSSS or SSSSS	It specifies the seconds past midnight (0-86399)
MI	It specifies the minute (00-59)
HH	It specifies the hour of a day (01-12)
HH12	It specifies the hour of a day (01-12)
HH24	It specifies the hour of a day (00-23)
AM or am or PM or pm	It specifies the meridiem indicator without periods
A.M. or a.m. or P.M. or p.m.	It specifies the meridiem indicator with periods
DAY	It specifies the name of the day where all characters are in upper case with blank-padding to 9 characters
Day	It specifies the name of the day where all characters are capitalized with blank-padding to 9 characters
day	It specifies the name of the day where all characters are in lower case with blank-padding to 9 characters
DY	It specifies the abbreviated upper case day name with 3 characters in English
Dy	It specifies the abbreviated capitalized day name with 3 characters in English
dy	It specifies the abbreviated lower case day name with 3 characters in English
DDD	It specifies the day of the year (001-366)
IDDD	It specifies the day of the ISO 8601 week-numbering year (001-371, where day 1 of the year is Monday of the first ISO week)
DD	It specifies the day of the month (01-31)
D	It specifies the day of the week (1-7, that is Sunday to Saturday)
ID	It specifies the ISO 8601 day of the week (1-7, that is Monday to Sunday)
W	It specifies the week of the month (1-5, where the first week starts on the first day of the month)
WW	It specifies the week number of the year (1-53, where the first week starts on the first day of the year)
IW	It specifies the week number of ISO 8601 week-numbering year (01-53, where the first Thursday of the year is in week 1)
MONTH	It specifies the month name where all the characters are in upper case with blank-padded to 9 characters
Month	It specifies the month name where all the characters are capitalized with blank-padded to 9 characters
month	It specifies the month name where all the characters are in lower case with blank-padded to 9 characters
MON	It specifies the abbreviated upper case month name (3 chars in English, localized lengths vary)
Mon	It specifies the abbreviated capitalized month name with 3 characters in English
mon	It specifies the abbreviated lower case month name with 3 characters in English
MM	It specifies the month number (01-12)
RM	It specifies the month in upper case Roman numerals (I-XII, that is January to December)
rm	It specifies the month in lower case Roman numerals (i–xii, that is January to December)
Q	It specifies the quarter of the year
Y,YYY	It specifies the 4 or more digits of the year with commas
YYYY	It specifies the 4 or more digits of the year without commas
YYY	It specifies the last 3 digits of the year
YY	It specifies the last 2 digits of the year
Y	It specifies the last digit of the year
IYYY	It specifies the ISO 8601 week-numbering year (4 or more digits)
IYY	It specifies the last 3 digits of ISO 8601 week-numbering year
IY	It specifies the last 2 digits of ISO 8601 week-numbering year
I	It specifies the last digit of ISO 8601 week-numbering year
BC or bc or AD or ad	It specifies the era indicator without periods
B.C. or b.c. or A.D. or a.d.	It specifies the era indicator with periods
CC	It specifies the century in 2 digits (where the twenty-first century starts on 2001-01-01)
J	It specifies the Julian Date (integer days since from November 24th, 4714 BC at local midnight)
tz	It specifies the lower case time-zone abbreviation
TZ	It specifies the upper case time-zone abbreviation
TZM	It specifies the time-zone minutes
TZH	It specifies the time-zone hours
OF	It specifies the time-zone offset from UTC (Coordinated Universal Time)
TM	It is applied to use localized day and month names (Translation Mode)
FM	It suppresses the blank padding and leading zeroes (Fill Mode)

Now, you have seen all the various formats that can be applied to an expression, Now let’s do some examples to get it clearly.

Read How to connect to PostgreSQL database

PostgreSQL TO_CHAR format date

You can convert date (timestamp) type values to formatted strings according to the specified pattern. All the pattern formats are discussed above, so let’s do some examples.

Example :

SELECT current_timestamp, TO_CHAR(current_timestamp, 'Day, DD  HH12:MI:SS') AS Date_Format;

SELECT TO_CHAR(current_timestamp, 'FMDay, FMDD  HH24:MI:SS') AS 24HourFormat;

SELECT TO_CHAR(TIMESTAMP '1998-03-08 22:30:59', 'FMDay, FMDD  HH12:MIPM') AS DateAnd12HourFormat;

PostgreSQL TO_CHAR date locale

You can convert day and month of the date/time (timestamp) type values to locale language pattern by using the format string TM with Day and Month format string in the TO_CHAR() function.

Example :

SELECT now(), TO_CHAR(now(), 'Day Dy, Month, Mon');

SELECT TO_CHAR(now(), 'TMDay TMDy, TMMonth TMMon') AS Locale;

PostgreSQL TO_CHAR add characters to date

You can convert date/time (timestamp) type values to a formatted string according to the specified pattern with characters including into the output string, by specifying the characters(strings) enclosed into the double quotes (“”) inside the format string argument in the TO_CHAR() function.

Example :

SELECT TO_CHAR(current_timestamp, '"Year: "FMYYYY", Day: "FMDay", Date: "FMDD", Time: " HH12:MI:SS') AS DateTime;

SELECT TO_CHAR(TIMESTAMP '1998-03-08 16:30:59', '"Year: "FMYYYY", Day: "FMDay", Date: "FMDD", Time: " HH12:MI:SS') AS DateTime;

Read How to create a table in PostgreSQL

PostgreSQL TO_CHAR quarter

You can specify the quarter of the year in the output string using Q in the format string argument of TO_CHAR() function.

Example :

SELECT current_timestamp::Date, TO_CHAR(current_timestamp, '"Quarter: "FMQ, "of year: "FMYYYY') AS DateAndQuarter;

SELECT current_timestamp::Date, TO_CHAR(current_timestamp, '"Quarter: "FMQTH, "of year: "FMYYYY') AS DateAndQuarter;

PostgreSQL TO_CHAR time format

You can output different formatted time from the timestamp type value as the output string according to your need by specifying the format strings related to the time that we have discussed in Table 2 in the format argument of the TO_CHAR() function.

Example :

SELECT TO_CHAR(TIMESTAMP '2017-08-18 22:30:59', 'HH12:MI:SS');

SELECT TO_CHAR(TIMESTAMP '2017-08-18 22:30:59', 'HH12:MI:SS:MS');

SELECT current_timestamp::time, TO_CHAR(current_timestamp, 'HH12:MIp.m.');

Read How to import CSV file into PostgreSQL

PostgreSQL TO_CHAR 24 hour

You can specify the time as the 24 hour format in the format string argument in the TO_CHAR() functions given ih the following example.

Example :

SELECT TO_CHAR(TIMESTAMP '2017-08-18 22:30:59', 'HH24:MI:SS');

SELECT current_timestamp, TO_CHAR(current_timestamp, 'HH12:MI:SSPM');

SELECT current_timestamp::time, TO_CHAR(current_timestamp, 'HH24:MI:SS');

PostgreSQL TO_CHAR timestamp with timezone

You can include the timezone from the timestamp type value given, by specifying the TZ or tz in the format string argument of the TO_CHAR() function.

Example :

SELECT current_timestamp, TO_CHAR(current_timestamp, 'HH24:MI:SS" Timezone: "TZ') AS timezone;

SELECT current_timestamp, TO_CHAR(current_timestamp, 'HH24:MI:SS" Timezone: "TZH TZM') AS TimezoneHourMinute;

SELECT * FROM pg_timezone_names
WHERE abbrev='CDT';

SHOW TIMEZONE;

In the above code, CDT stands for Central Daylight Time which is the second easternmost time zone in the United States, and the third easternmost in Canada. CDT covers all or parts of 20 states in the US and three provinces or territories in Canada.

Read How to backup PostgreSQL database

PostgreSQL TO_CHAR timezone offset

You can also specify the timezone offset in the format string argument by including OF to it. The timezone offset means the hours and minutes difference from the UTC(Coordinated Universal Time) from the current timezone.

Example :

SELECT TO_CHAR(current_timestamp, 'HH24:MI:SS" Timezone (offset from UTC): "OF');

PostgreSQL TO_CHAR number format

You can convert a numeric/number/integer/floating-point type values to formatted strings according to the specified pattern, that we have discussed in the Table 1, in the 1st topic. So, let’s do some hands-on practice.

Example :

SELECT TO_CHAR(-0.7, '99.99') AS number_format,
TO_CHAR(-0.7, 'FM9.99') AS with_padding;

SELECT TO_CHAR(-0.62, 'FM90.99') AS with_leading_zero,
TO_CHAR(0.62, '0.9') AS without_padding;

SELECT TO_CHAR(71, '9990999.9') AS with_leading_zeroes,
TO_CHAR(71, 'FM9990999.9') AS with_padding;

SELECT TO_CHAR(420, '999') AS positive_integer,
TO_CHAR(-420, '999') AS negative_integer;

SELECT TO_CHAR(786, '9 9 9') with_spaces,
TO_CHAR(2034, '9,999') with_comma,
TO_CHAR(2034, '9G999') with_group_separater;

SELECT TO_CHAR(485.7, '999.999') AS number_format,
TO_CHAR(485.7, 'FM999.999') AS with_padding,
TO_CHAR(485.7, 'FM999.990') AS with_trailing_zeroes;

SELECT TO_CHAR(485.7, '999D999') AS format_with_decimal,
TO_CHAR(6128.7, '9G999D999') AS add_group_separator;

SELECT TO_CHAR(-786, '999PR') AS negative_value_to_angular_brackets;

Read PostgreSQL Export Table to CSV

PostgreSQL TO_CHAR currency

You can get the currency symbol in the output string by specifying the character L in the format string argument of the TO_CHAR() function.

Example :

SELECT TO_CHAR(786, 'L999') AS currency_formatting,
TO_CHAR(786, 'LFM999') with_padding,
TO_CHAR(786, 'FM999L') AS currency_symbol_at_the_end;

PostgreSQL TO_CHAR percentage

You can specify the expression argument as the percentage expression to get the result of the expression as the output of the TO_CHAR() function, can also specify the percentage sign (%) in the pattern string argument.

Example :

SELECT '-87 out of 180' AS loss, TO_CHAR(100.0*(-87)/180,'999D99%') AS percentage;

Read PostgreSQL drop all tables

PostgreSQL TO_CHAR signed integers

You can get the formatted string outputs for the signed integers by using the TO_CHAR() function.

Example :

SELECT TO_CHAR(-786, '999') AS negative_integer_formatting,
TO_CHAR(-786, '999S') AS trailing_sign_format,

TO_CHAR(-786, '999MI') AS minus_sign_format;

SELECT TO_CHAR(786, '999') AS positive_integer_formatting,

TO_CHAR(786, '999MI') AS trailing_minus_sign,
TO_CHAR(786, 'FM999MI') AS with_padding,
TO_CHAR(786, 'PL999') AS plus_sign_format;

SELECT TO_CHAR(786, 'SG999') AS sign_format_for_positive,

TO_CHAR(-786, 'SG999') AS sign_format_for_negative,

TO_CHAR(-786, '9SG99') AS sign_inbetween;

PostgreSQL TO_CHAR into roman number

You can convert the number into the roman numeral by specifying the RN (for upper case) or rn (for lower case) in the format string argument of the TO_CHAR() function.

Example :

SELECT '786' AS original, TO_CHAR(786, 'RN') AS roman_numerial_format,

TO_CHAR(786, 'FMrn') AS lower_case_with_padding,

'7.1' AS floating_point_roman_format,
TO_CHAR(7.1, 'FMRN') AS roman;

Read Postgres RegEx

PostgreSQL TO_CHAR ordinal suffix

You can get the ordinal suffix to the number value in the output string produced by the TO_CHAR() function by specifying the TH or th in the format string argument.

Example :

SELECT '71' AS number, TO_CHAR(71, '99th');

SELECT '742' AS number, TO_CHAR(742, '999TH');

SELECT '593' AS number, TO_CHAR(573, '999th');

SELECT '1786' AS number, TO_CHAR(1786, '9999TH');

PostgreSQL TO_CHAR exponent

You can convert number/floating-point/integer type values into the exponent form by adding EEEE in the format string argument of the TO_CHAR() function.

Example :

SELECT '0.0000007869' AS number, TO_CHAR(0.0000007869, '9.99EEEE');

SELECT '7869632' AS number, TO_CHAR(7869632, '9.99EEEE');

Read PostgreSQL DATE_PART() Function with examples

PostgreSQL TO_CHAR shift decimal point

You can shift the decimal point to the right side of the given value by using the V in the format string argument of the TO_CHAR() function. The 10 raised to the power of the trailing digits is multiplied by the given value, to shift the decimal point to the right side.

Example :

SELECT '15' AS number, TO_CHAR(15, '99V999') AS decimal_shift;

SELECT '71.5' AS number, TO_CHAR(71.5, '99V999')
 AS decimal_shift;

SELECT '97.25' AS number, TO_CHAR(97.25, '99V9')AS decimal_shift;

PostgreSQL TO_CHAR add string to number

You can add the strings in the format string argument by enclosing the given strings into double quotes (“”) in the TO_CHAR() function.

Example :

SELECT TO_CHAR(786, '"My lucky number is: "999') AS add_string;

SELECT TO_CHAR(691.7, '"Pre-decimal: "FM999" Post-decimal: "FM.999');

SELECT TO_CHAR(94873, '"I have "FM99,999l" in my account"');

You may also like the following PostgreSQL tutorials:

• PostgreSQL INSERT Multiple Rows
• PostgreSQL DROP TABLE + Examples
• PostgreSQL DROP COLUMN
• PostgreSQL INSERT INTO table + 9 Examples
• PostgreSQL ALTER TABLE + 19 Examples
• PostgreSQL TO_TIMESTAMP function

In this PostgreSQL tutorial, we have learned about PostgreSQL TO_CHAR() function to convert the timestamp, numeric, etc., type value data into a formatted string, and have also covered the following topics:

• PostgreSQL TO_CHAR function
• PostgreSQL TO_CHAR format date
• PostgreSQL TO_CHAR date locale
• PostgreSQL TO_CHAR add characters to date
• PostgreSQL TO_CHAR quarter
• PostgreSQL TO_CHAR time format
• PostgreSQL TO_CHAR 24 hour
• PostgreSQL TO_CHAR timestamp with timezone
• PostgreSQL TO_CHAR timezone offset
• PostgreSQL TO_CHAR number format
• PostgreSQL TO_CHAR currency
• PostgreSQL TO_CHAR percentage
• PostgreSQL TO_CHAR signed integers
• PostgreSQL TO_CHAR into roman number
• PostgreSQL TO_CHAR ordinal suffix
• PostgreSQL TO_CHAR exponent
• PostgreSQL TO_CHAR shift decimal point
• PostgreSQL TO_CHAR add string to number