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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.