Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Type Conversion Functions

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()

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()

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() with numbers

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() with timestamps

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()

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()

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()

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()

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)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire