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:

formatDescription
9Digit position, and is used to get the numeric value with specified number of digits (can be removed if insignificant)
0Digit 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)
FMSuppresses the blank padding and leading zeroes (Fill Mode)
PRA negative value in angle brackets
SSign anchored to a number (that uses locale)
LCurrency symbol (that uses locale)
DDecimal point (that uses locale)
GGroup separator (that uses locale)
MIMinus sign in specified position for the numbers less than 0
PLPlus sign in specified position for the numbers greater than 0
SGPlus/Minus sign in the specified position
RNRoman numeral for the number that ranges from 1 to 3999
VShift specified number of digits (It multiplies the value by 10 raised to the power n, where n = number of digits following V).
TH / thTH for Upper case and the for lower case ordinal number suffix
EEEExponent for the scientific notation
Table 1: Numeric formatting patterns in PostgreSQL for TO_CHAR function

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

formatDescription
SSIt specifies the second (00-59)
MSIt specifies the millisecond (000-999)
USIt specifies the microsecond (000000-999999)
FF1It specifies the tenth of a second (0-9)
FF2It specifies the hundredth of a second (00-99)
FF3It specifies the thousandth of a second that is a millisecond (000-999)
FF4It specifies the tenth of a millisecond (0000-9999)
FF5It specifies the hundredth of a millisecond (00000-99999)
FF6It specifies the thousandth of a millisecond that is a microsecond (000000-999999)
SSSS or SSSSSIt specifies the seconds past midnight (0-86399)
MIIt specifies the minute (00-59)
HHIt specifies the hour of a day (01-12)
HH12It specifies the hour of a day (01-12)
HH24It specifies the hour of a day (00-23)
AM or am or PM or pmIt specifies the meridiem indicator without periods
A.M. or a.m. or P.M. or p.m.It specifies the meridiem indicator with periods
DAYIt specifies the name of the day where all characters are in upper case with blank-padding to 9 characters
DayIt specifies the name of the day where all characters are capitalized with blank-padding to 9 characters
dayIt specifies the name of the day where all characters are in lower case with blank-padding to 9 characters
DYIt specifies the abbreviated upper case day name with 3 characters in English
DyIt specifies the abbreviated capitalized day name with 3 characters in English
dyIt specifies the abbreviated lower case day name with 3 characters in English
DDDIt specifies the day of the year (001-366)
IDDDIt 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)
DDIt specifies the day of the month (01-31)
DIt specifies the day of the week (1-7, that is Sunday to Saturday)
IDIt specifies the ISO 8601 day of the week (1-7, that is Monday to Sunday)
WIt specifies the week of the month (1-5, where the first week starts on the first day of the month)
WWIt specifies the week number of the year (1-53, where the first week starts on the first day of the year)
IWIt specifies the week number of ISO 8601 week-numbering year (01-53, where the first Thursday of the year is in week 1)
MONTHIt specifies the month name where all the characters are in upper case with blank-padded to 9 characters
MonthIt specifies the month name where all the characters are capitalized with blank-padded to 9 characters
monthIt specifies the month name where all the characters are in lower case with blank-padded to 9 characters
MONIt specifies the abbreviated upper case month name (3 chars in English, localized lengths vary)
MonIt specifies the abbreviated capitalized month name with 3 characters in English
monIt specifies the abbreviated lower case month name with 3 characters in English
MMIt specifies the month number (01-12)
RMIt specifies the month in upper case Roman numerals (I-XII, that is January to December)
rmIt specifies the month in lower case Roman numerals (i–xii, that is January to December)
QIt specifies the quarter of the year
Y,YYYIt specifies the 4 or more digits of the year with commas
YYYYIt specifies the 4 or more digits of the year without commas
YYYIt specifies the last 3 digits of the year
YYIt specifies the last 2 digits of the year
YIt specifies the last digit of the year
IYYYIt specifies the ISO 8601 week-numbering year (4 or more digits)
IYYIt specifies the last 3 digits of ISO 8601 week-numbering year
IYIt specifies the last 2 digits of ISO 8601 week-numbering year
IIt specifies the last digit of ISO 8601 week-numbering year
BC or bc or AD or adIt specifies the era indicator without periods
B.C. or b.c. or A.D. or a.d.It specifies the era indicator with periods
CCIt specifies the century in 2 digits (where the twenty-first century starts on 2001-01-01)
JIt specifies the Julian Date (integer days since from November 24th, 4714 BC at local midnight)
tzIt specifies the lower case time-zone abbreviation
TZIt specifies the upper case time-zone abbreviation
TZMIt specifies the time-zone minutes
TZHIt specifies the time-zone hours
OFIt specifies the time-zone offset from UTC (Coordinated Universal Time)
TMIt is applied to use localized day and month names (Translation Mode)
FMIt suppresses the blank padding and leading zeroes (Fill Mode)
Table 2: timestamp formatting patterns in PostgreSQL for TO_CHAR function

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 format date
PostgreSQL TO_CHAR format date

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 date locale
PostgreSQL TO_CHAR date 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;
PostgreSQL TO_CHAR add characters to date
PostgreSQL TO_CHAR add characters to date

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 quarter
PostgreSQL TO_CHAR quarter

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.');
PostgreSQL TO_CHAR time format
PostgreSQL TO_CHAR time format

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 24 hour
PostgreSQL TO_CHAR 24 hour

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.

PostgreSQL TO_CHAR timestamp with timezone
PostgreSQL TO_CHAR timestamp with timezone

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 timezone offset
PostgreSQL TO_CHAR timezone offset

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;
PostgreSQL TO_CHAR number format
PostgreSQL TO_CHAR number format

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 currency
PostgreSQL TO_CHAR currency

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;
PostgreSQL TO_CHAR percentage
PostgreSQL TO_CHAR 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 signed integers
PostgreSQL TO_CHAR signed integers

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;
PostgreSQL TO_CHAR into roman number
PostgreSQL TO_CHAR into roman number

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 ordinal suffix
PostgreSQL TO_CHAR ordinal suffix

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');
PostgreSQL TO_CHAR exponent
PostgreSQL TO_CHAR exponent

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 shift decimal point
PostgreSQL TO_CHAR shift decimal point

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"');
PostgreSQL TO_CHAR add string to number
PostgreSQL TO_CHAR add string to number

You may also like the following PostgreSQL tutorials:

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