| |
While PostgreSQL is able to explicitly cast between most commonly used data types, some conversions require a function
in order to meaningfully translate values. Some of PostgreSQL's commonly used type conversion functions
are listed in Table 5-13. These are detailed in the following sections.
Table 5-13. Type conversion functions
|
Function
|
Description
|
|
bitfromint4(
n
)
|
Converts numeric value
n
to a binary bit string
|
|
bittoint4(
b
)
|
Converts bit string
b
to its numeric decimal representation
|
|
to_char(
n
,
f
)
|
Converts numeric value
n
to a character string with format
f
|
|
to_char(
t
,
f
)
|
Converts timestamp
t
to a character string with format
f
|
|
to_date(
s
,
f
)
|
Converts character string
s
with date format
f
to a date value
|
|
to_number(
s
,
f
)
|
Converts character string
s
with format
f
to a numeric value
|
|
to_timestamp(
s
,
f
)
|
Converts character string
s
with format
f
to a timestamp value
|
|
timestamp(
d
)
|
Returns the date
d
as a value of type timestamp
|
|
timestamp(
d
,
t
)
|
Returns a timestamp value derived from date d and time t
|
bitfromint4(
n
)
The bitfromint4() function accepts a single argument
n
of type
integer and returns its binary bit string equivalent. As explicit casts between binary and
integer types do not exist, this function is required to transform decimal values to their binary counterparts.
The returned value is of type bit, and may not exceed 32 bits. Therefore, since the
integer argument is signed, valid input values are between –2147483648
and 2147483647.
Example
booktown=#
SELECT bitfromint4(16385);
bitfromint4
----------------------------------
00000000000000000100000000000001
(1 row)
bittoint4(
b
)
The bittoint4() function is essentially the inverse of the
bitfromint4() function; it accepts a single argument
b
of type
bit and returns its decimal numeric value as type
integer.
The bounds of input and output are the reverse of the bitfromint4 function,
in that it accepts up to 32 binary digits, and will thus not return more than 2147483647 or less than –2147483648
as its result value.
Example
booktown=#
SELECT bittoint4(B'101010'),
booktown-#
bittoint4(bitfromint4(99)) AS inverse_example;
bittoint4 | inverse_example
-----------+-----------------
42 | 99
(1 row)
to_char(
n
,
f
)
The to_char() function, when used with argument
n
of type
numeric and argument
f
, of type
text, formats the numeric value of n to a character
string returned as type text. The character string
f
describes
the character string format within which to place the value of
n
.
The
f
format string consists of a series of
meta-characters
, which PostgreSQL
translates into the literal values they represent. Valid meta-characters that may be used within this
format string for a numeric conversion are outlined in Table 5-14.
Table 5-14. Numeric conversion formatting characters
|
Character
|
Description
|
|
9
|
The next sequential digit in the value
n
|
|
0
|
The next sequential digit in
n
, or a leading or trailing zero if more digits are specified by
f
than are in
n
; may thus be used to force significant digits to the left or right of a value
|
|
.
|
A decimal point (there can be only one)
|
|
,
|
A comma (there can be several, for separating thousands, millions, etc.)
|
|
D
|
A decimal point (e.g., a period) derived from locale
|
|
G
|
A group separator (e.g., a comma) derived from locale
|
|
PR
|
If
n
is a negative value, placing
PR
at the end of
f
surrounds the returned string in angle brackets
|
|
SG
|
A plus or minus sign, depending on the value of
n
|
|
MI
|
A minus sign, if the
n
is negative
|
|
PL
|
A plus sign, if
n
is positive
|
|
S
|
A plus or minus sign, derived from locale
|
|
L
|
A currency symbol, derived from locale
|
|
RN
|
The Roman Numeral characters for numeric values of
n
between 1 and 3999
|
|
TH, th
|
The appropriate ordinal suffix for
n
(e.g.,
4th
,
2nd
)
|
|
V
|
Adds a zero to the right for each
9
following
V
, effectively shifting up by exponents of ten
|
|
FM
|
Sets format to "fill mode," causing leading and trailing zeroes (created by the
9
character, but not
0
), and extra whitespace, to be omitted
|
When more digits are specified with the
9
character in the format string than are within the
numeric value
n
, the extra digits will be padded with whitespace. When more digits are specified
with the
0
character, the extra digits will be padded with zeroes.
If
fewer
digits are specified then are necessary to represent the digits to the left
of the decimal, the meaning of the conversion becomes ambiguous, as significant digits must be omitted. Since it is
unclear which digits should be omitted, the to_char() function will enter the
#
character in place of each specified digit. It is therefore important to specify the maximum
number of digits that you expect to receive back from the translation. You should also use a function such as
translate() or one of the trim functions to remove unwanted whitespace from the translation.
Literal versions of meta-characters may be used within the format string by surrounding them with double quotes.
Doing this within the format string changes the quoted meta-characters so they are interpreted literally. Note that in order to
use a literal double-quote within this scheme,
two backslashes
must prefix the double-quote, as it
is essentially twice escaped.
Note: Any character that is not a meta-character may be safely used in a format string (e.g., the
$
symbol). Such characters will appear in the formatted string unchanged.
Examples
booktown=#
SELECT to_char(123456789, '999G999G999D99') AS formatted,
booktown-#
to_char(123456789, '999999999') AS just_digits,
booktown-#
to_char(123456789, '00999999999') AS with_zeroes;
formatted | just_digits | with_zeroes
-----------------+-------------+--------------
123,456,789.00 | 123456789 | 00123456789
(1 row)
booktown=#
SELECT cost * 100 AS cost_to_order,
booktown-#
to_char(cost * 100, '$99,999.99') AS monetary,
booktown-#
translate(to_char(cost * 100, '$9,999.99'),' ','')
booktown-#
AS translated
booktown-#
FROM stock
booktown-#
LIMIT 3;
cost_to_order | monetary | translated
---------------+-------------+------------
2900.00 | $ 2,900.00 | $2,900.00
3000.00 | $ 3,000.00 | $3,000.00
1600.00 | $ 1,600.00 | $1,600.00
(3 rows)
booktown=#
SELECT to_char(1.0, '9th "Place"') AS first,
booktown-#
to_char(2.2, '9th "Place"') AS second,
booktown-#
to_char(pi(), '9th "Place"') AS third,
booktown-#
to_char(10, '99V99th "\\"Place\\""') AS shifted_up;
first | second | third | shifted_up
------------+------------+------------+-----------------
1st Place | 2nd Place | 3rd Place | 1000th "Place"
(1 row)
Note: Note that as of PostgreSQL v7.1.x, there is a bug in the usage of the
RN
Roman Numeral
conversion sequence which causes it to return invalid results unless used with the
FM
character
sequence. This is scheduled for correction in 7.2, but can be worked around by using the complete
FMRN
sequence.
to_char(
t
,
f
)
When used with argument
t
of type timestamp and argument
f
of type text the to_char
function formats the date and time represented by of
t
to a character string returned as
type text.
As with the numeric functionality of to_char(), the character string
f
describes the meta-characters which are translated by PostgreSQL into the literal values they
represent. Valid meta-characters that may be used within this format string for date and time values are outlined in
Table 5-15.
Table 5-15. Timestamp conversion formatting characters
|
Character
|
Description
|
|
HH, HH12
|
The hour of day, from 1 to 12
|
|
HH24
|
The hour of the day, from 0 to 23
|
|
MI
|
The minute, from 0 to 59
|
|
SS
|
The second, from 0 to 59
|
|
SSSS
|
The seconds past midnight, from 0 to 86,399
|
|
AM, PM, A.M., P.M.
|
The meridian indicator in uppercase, with optional periods
|
|
am, pm, a.m., p.m.
|
The meridian indicator in lowercase, with optional periods
|
|
TZ, tz
|
The time zone, in upper or lowercase
|
|
CC
|
The two-digit century (
not
the year divided by 100)
|
|
Y, YY, YYY, YYYY, Y,YYY
|
The year's last digit, last two digits, last three digits, or last four digits (with optional comma)
|
|
BC, AD, B.C., A.D.
|
Year qualifier, in uppercase
|
|
bc, ad, b.c., a.d.
|
Year qualifier, in lowercase
|
|
MONTH, Month, month
|
The full month name, padded on the right with blanks to 9 characters in length, in uppercase, init-capped, or lowercase
|
|
MON, Mon, mon
|
The abbreviated 3-letter month, in uppercase, init-capped, or lowercase
|
|
MM
|
The month number, from 1 to 12
|
|
RN, rn
|
The month in Roman Numerals, from I to XII, in upper or lowercase
|
|
DAY, Day, day
|
The full day name, padded on the right to 9 characters in length, in uppercase, init-capped, or lowercase
|
|
DY, Dy, dy
|
The abbreviated 3-letter day, in uppercase, init-capped, or lowercase
|
|
DDD, DD, D
|
The day of the year, from 1 to 366, day of the month, from 1 to 31, or day of the week, from 1 to 7 (beginning on Sunday)
|
|
W
|
The week of the month, from 1 to 5 (from the 1st day of the month)
|
|
WW
|
The week of the year, from 1 to 53 (from the 1st day of the year)
|
|
IW
|
The ISO week of the year (from the 1st Thursday of the new year)
|
|
TH, th
|
The appropriate ordinal suffix for the preceding numeric value, upper or lowercase
|
|
fm
|
Causes extra padding to be omitted, including whitespace, and extra zeroes
|
The
TH
suffix and
FM
prefix must be
directly adjacent to the value they are modifying. For example, to apply
FM
to the
Day
value, the complete sequence would be
FMDay
(not
FM Day
).
Similarly, to attach the ordinal suffix to the
DD
day of the month, the complete
sequence would be
DDTH
(not
DD TH
).
Examples
booktown=#
SELECT to_char(now(), 'HH:MI PM') AS the_time;
the_time
----------
05:04 PM
(1 row)
booktown=#
SELECT to_char(now(), 'Dy (Day), Mon (Month)')
booktown-#
AS abbreviations,
booktown-#
to_char('yesterday'::timestamp, 'FMMonth FMDDth')
booktown-#
AS yesterday,
booktown-#
to_char('yesterday'::timestamp, 'FMDDth FMMonth')
booktown-#
AS "yesterday UK";
abbreviations | yesterday | yesterday UK
----------------------------------+-------------+--------------
Sat (Saturday ), Sep (September) | August 31st | 31st August
(1 row)
booktown=#
SELECT isbn, these must be
booktown-#
to_char(publication, 'FMMonth FMDDth, YYYY')
booktown-#
AS informal,
booktown-#
to_char(publication, 'YYYY-MM-DD') AS formal,
booktown-#
to_char(publication, 'Y,YYY "years" A.D.')
booktown-#
AS first_published
booktown-#
FROM editions LIMIT 3;
isbn | informal | formal | first_published
------------+------------------+------------+------------------
039480001X | March 1st, 1957 | 1957-03-01 | 1,957 years A.D.
0451160916 | August 1st, 1981 | 1981-08-01 | 1,981 years A.D.
0394800753 | March 1st, 1949 | 1949-03-01 | 1,949 years A.D.
(3 rows)
to_date(
s
,
f
)
The to_date() function accepts two arguments
s
and
f
, each of type text. The argument
f
describes, using the date-specific meta-characters detailed in Table 5-15,
the format of the date described by the string
s
. The result is returned as type
date.
While PostgreSQL can figure out a wide variety of date formats, it cannot support every arbitrary date format.
The to_date() function insures that, provided the format can be described using the meta-
characters from Table 5-14, nearly any date format can be converted to a valid date value.
Example
booktown=#
SELECT date('198025thJune')
booktown-#
AS non_standard_date_format,
booktown-#
to_date('198025thJune', 'YYYYDDthMonth')
booktown-#
AS correct_interpretation;
non_standard_date_format | correct_interpretation
--------------------------+------------------------
2025-08-27 | 1980-06-25
(1 row)
to_number(
s
,
f
)
The to_number function operates much like the inverse of the
to_char() function for numbers. It accepts two arguments
s
and
f
, each of type text. The character string described by
s
should have its format described by
f
, using the same meta-characters shown
in Table 5-14. The result is returned as type
numeric.
Examples
booktown=#
SELECT to_number('$2,900.00', 'L9G999D99')
booktown-#
AS monetary;
monetary
----------
2900.00
(1 row)
booktown=#
SELECT to_number('123,456,789.00', '999G999G999D99')
booktown-#
AS formatted,
booktown-#
to_number('123456789', '999999999')
booktown-#
AS just_digits,
booktown-#
to_number('00123456789', '00999999999')
booktown-#
AS leading_zeroes;
formatted | just_digits | leading_zeroes
--------------+-------------+----------------
123456789.00 | 123456789 | 123456789
(1 row)
to_timestamp(
s
,
f
)
The to_timestamp() function accepts two arguments
s
and
f
, each of type text. The argument
f
describes, using the meta-characters detailed in Table 5-15,
the format of the date and time described by the string
s
. The result is returned as type
date.
Like to_date(), this function exists primarily as a means to be able to
correctly interpret the format of a non-standard date and time string.
Example
booktown=#
SELECT timestamp('197825thJuly01:12am')
booktown-#
AS non_standard_timestamp,
booktown-#
to_timestamp('197825July01:12am',
booktown(#
'YYYYDDFMMonthHH12:MIam')
booktown-#
AS correct_interpretation;
non_standard_timestamp | correct_interpretation
------------------------+------------------------
2025-06-27 01:12:00-07 | 1978-07-25 01:12:00-07
(1 row)
Note: The use of the
FM
modifier can be crucial in making sure the evaluation of values following
a month or day name are interpreted correctly, as these names are normally padded to nine characters in length. Note
that the
FM
modifier must precede each element which you wish it to apply to, as it is not a
"global" modifier.
timestamp(
d
)
timestamp(
d
,
t
)
The timestamp() function accepts either a single argument
d
of type date, or two arguments
d
and
t
, of
types date and time, respectively. The arguments passed
are converted to a value of type timestamp and returned. In the former case, the time
is assumed to be midnight on the date specified.
Example
booktown=#
SELECT timestamp(date('now')) AS today_at_midnight,
booktown-#
timestamp(date('now'),
booktown(#
time('now')) AS right_now;
today_at_midnight | right_now
------------------------+------------------------
2001-09-01 00:00:00-07 | 2001-09-01 18:04:16-07
(1 row)
|
|
