A function is an identifier that instructs PostgreSQL to perform a programmatic operation within a SQL statement. A function returns a single value from its operation, and that value is then used in the SQL statement where the function was invoked. This process is similar to the way operators return their results in the location from which they were called in the query. (In fact, operators are technically pointers to built-in system functions, and are sometimes called “syntactic sugar” for functions, as they are a syntactically convenient way to call underlying functions.)
To use a function in a SQL statement, type the function’s name, followed by its list of
parameters (called arguments), if any. The arguments passed to a function
are enclosed in parentheses. There are two general styles of entering arguments: the standard
SQL92 functions are generally implemented so that they accept their arguments delimited by
special SQL keywords, such as FROM, FOR, and USING.
PostgreSQL-style functions, on the other hand, accept arguments delimited by commas (which you
might expect if you have experience with a programming language such as C).
Arguments may be constants, valid identifiers, or expressions. The particular arguments you need to pass to a function will depend completely on the function being used, and its requirements: especially with regards to data types. With a couple of exceptions, all functions require the open and closing parentheses following the function name, even if no arguments are passed.
sql92_style_function ( { argument | KEYWORD } [...] ) pgsql_style_function ( argument [, ...] )
Note
The exceptions to the parenthetical function syntax are the SQL92 functions current_date, current_time, and current_timestamp. These lack parentheses to remain compatible with the SQL92
specification.
A powerful use of functions is that they may be nested, provided that the data type returned by a nested function is compatible with the argument accepted by the function it is nested within. Functions may be nested to any depth:
function_name ( nested_function_name ( arguments [, ...] ) [, ...] )
PostgreSQL defines a rich
set of functions for its built-in data types. To view a complete list of functions available,
execute the \df slash command within psql. PostgreSQL
also supports extensibility of its function set through the CREATE FUNCTION
command. See Chapter 7 for more on this topic.
The mathematical functions provided for PostgreSQL operate on a variety of numeric data types, and generally return a value of the same type as the function’s arguments. They can perform many useful and common arithmetic and trigonometric operations; Table 5-9 provides an overview of some of the most common mathematical functions in PostgreSQL.
Table 5-9. Mathematical functions in PostgreSQL
|
Function |
Description |
|---|---|
|
|
Returns the absolute value of |
|
|
Returns the inverse cosine of |
|
|
Returns the inverse sine of |
|
|
Returns the inverse tangent of |
|
|
Returns the inverse tangent of the quotient of |
|
|
Returns the cube root of |
|
|
Returns the smallest whole integer not less than argument (rounds up) |
|
|
Returns the cosine of |
|
|
Returns the cotangent of |
|
|
Returns degrees from radians |
|
|
Returns the e constant (2.71828...), to the power of
|
|
|
Returns the largest whole integer not greater than |
|
|
Returns the natural logarithm of |
|
|
Returns the base |
|
|
Returns the base 10 logarithm of |
|
|
Returns the remainder (modulus) when dividing |
|
|
Returns the pi constant (3.14159...) |
|
|
Returns value of |
|
|
Returns radian equivalent to |
|
|
Returns a pseudo-random value from 0.0 to 1.0 |
|
|
Returns |
|
|
Returns the value of |
|
|
Returns the sine of |
|
|
Returns the square root of |
|
|
Returns the tangent of |
|
|
Returns the value of |
|
|
Returns the value of |
The following sections elaborate on each of the functions described in Table 5-9, detailing required arguments, data types, and functionality. Note that while a function will usually only accept one of a set of data types as its arguments, PostgreSQL will attempt to implicitly convert supplied arguments to the required types, if necessary. If an implicit type conversion fails, PostgreSQL will supply the appropriate error message, and you may need to use an explicit type conversion. See Chapter 3 for more information on explicitly converting types.
abs(x)The abs( ) function accepts a single numeric argument
x, and returns its absolute value (distance from
zero). It therefore has no effect on positive numbers, but inverts the sign of a negative
number to a positive number.
It can accept an argument which is of any of the numeric data types (numeric, bigint, smallint, real, or double precision), and returns the result in the form
of the same data type which was passed to it.
acos(x)The acos( ) function accepts a valid cosine, and returns the inverse
(or arc) cosine of the double precision argument x (between –1 and 1) passed to it. This effectively returns the inverse of the cos( )
function. The result is a double precision value of an angle, in radians,
between 0 and pi.
asin(x)The asin( ) function returns the inverse (or arc) sine of the
double precision argument x
(between –1 and 1) passed to it. Like acos( ), this effectively returns
the inverse of the sin( ) function. The result is a double
precision value of an angle, in radians, between pi / 2 and
–pi / 2.
atan(x)The atan( ) function returns the inverse (or arc) tangent of a
double precision argument x
passed to it, which effectively returns the inverse of the tan( )
function. The result is a double precision value of an angle, in radians,
between pi / 2 and –pi / 2.
atan2(x, y)
Similar to the atan( ) function, atan2(
) returns the inverse (or arc) tangent in the form of a double
precision value of an angle, in radians, between pi / 2 and
–pi / 2. Unlike atan( ), atan2( )
accepts two double precision arguments rather than one, and returns the
inverse tangent of the quotient of the first argument divided into the second
argument.
In general, atan2(x,
y) is functionally identical to atan(x
/
y), though specifying a
y value of 0 will not cause a divide by zero error
with atan2( ), as it would if specifying x /
y to the atan( ) function. If y is specified to atan2( ) as zero, the resultant
value will be pi / 2 for a positive value of x, –pi /
2 for a negative value of x,
or 0 for a zero value of x.
Example 5-23.
testdb=# SELECT atan2(0, 1), atan2(1, 1), testdb-# atan(0 / 1) AS functionally, testdb-# atan(1 / 1) AS identical; atan2 | atan2 | functionally | identical -------+-------------------+--------------+------------------- 0 | 0.785398163397448 | 0 | 0.785398163397448 (1 row) testdb=# SELECT atan2(1, 0) AS positive_x, testdb-# atan2(-1, 0) AS negative_x, testdb-# atan2(0, 0) AS zero_x, testdb-# pi( ) / 2 AS pi_over_two; positive_x | negative_x | zero_x | pi_over_two -----------------+------------------+--------+----------------- 1.5707963267949 | -1.5707963267949 | 0 | 1.5707963267949 (1 row)
cbrt(x)The cbrt( ) function accepts a single double
precision argument x, and returns its
cubed root as a double precision value. This function is effectively the
inverse of raising a number by the power of 3 with the pow
function.
ceil(x)The ceil( ) function accepts a value
x of any numeric data type (numeric, bigint, smallint, real, or double precision), and rounds it up to the smallest
whole integer greater than the passed value. If a whole integer is passed, ceil(
) has no effect.
cos(x)The cos( ) function accepts a single double
precision value x representing an angle
(in radians), and returns its cosine as a double precision value.
cot(x)The cot( ) function accepts a single double
precision value x representing an angle
(in radians), and returns its cotangent as a double precision value. The
argument passed must be non-zero.
degrees(r)The degrees( ) function accepts a double precision argument r representing
a value expressed in radians, and converts them into degrees. The result is returned as a
value of type double precision. degrees( ) is
effectively the inverse of the radians( ) function.
exp(x)The exp( ) function accepts a single double
precision or numeric argument x, and returns the special e
constant, raised to the power passed to the function.
floor(x)The floor( ) function accepts a single numeric
value x, and rounds it down to the largest whole
integer not greater than the passed argument. It therefore has no effect on a whole
integer.
ln(x)ln( ) accepts a single numeric
or double precision value x and
returns the natural logarithm of that argument. This is effectively the inverse of the
exp( ) function, as well as the equivalent of selecting the log(
) of the argument, with base e.
log(x) log(b, x)
The log( ) function accepts either one or two arguments of type
numeric. If one argument is specified, log(x) returns the
base 10 logarithm of x. If two arguments are
specified, log(b, x) returns the base b logarithm of
x.
mod(x, y)The mod( ) function accepts two numeric arguments, x and y, which may be of type
numeric, integer, smallint, or bigint. The value
returned is the remainder, or modulus, left over from dividing x /
y.
pi( )
The pi( ) function requires no arguments, and
returns the pi constant of roughly 3.14159265358979.
pow(x, y)
The pow( ) function accepts two arguments, x and y, of type numeric or double precision. It returns the value of
x raised to the exponent of y. The result is returned as a value of the same data type as the
passed arguments. Note that the arguments must contain decimal points.
radians(d)The radians( ) function accepts a single argument d of type double precision, specifying degrees.
The function returns the equivalent number of radians, as a value of type double
precision. radians( ) is effectively the inverse of the
degrees( ) function.
random( )
The random( ) function accepts no arguments, and
returns a pseudo-random value between 0.0 and 1.0, of type double
precision. Each invocation of random( ) returns a different
value, even when used in multiple places within the same query.
Typically this function is used in conjunction with mathematical operators (e.g.,
+ and *) to set a range of random numbers, and then
rounded with an appropriate rounding function (e.g., round( ), trunc( )).
Example 5-37.
testdb=# SELECT random( ) AS natural_random, testdb-# round(random( ) * 9) + 1 AS one_through_ten, testdb-# trunc(random( ) * 99) + 1 AS one_through_one_hundred; natural_random | one_through_ten | one_through_one_hundred -------------------+-----------------+------------------------- 0.478887704424042 | 2 | 37 (1 row)
round(x) round(x,s)
The round( ) function may accept either one or two arguments. The
first argument, x, of type numeric
or double precision, is the number that you intend to round. The second
optional argument, s, of type integer, specifies how many digits past the decimal to round from. The result is
returned as a value of the same type as the first argument.
If there are more digits specified by s than by
x, the extra digits will be padded with
zeroes.
Example 5-38.
testdb=# SELECT round(1.0) AS one, testdb-# round(1.1) AS "one point one", testdb-# round(1.5) AS "one point five", testdb-# round(1.8) AS "one point eight"; one | one point one | one point five | one point eight -----+---------------+----------------+----------------- 1 | 1 | 2 | 2 (1 row) testdb=# SELECT round(1.4949, 1) AS one_digit_scale, testdb-# round(1.4949, 3) AS three_digit_scale, testdb-# round(1.4949, 10) AS ten_digit_scale, testdb-# round(1.4949, 0) AS rounded; one_digit_scale | three_digit_scale | ten_digit_scale | rounded -----------------+-------------------+-----------------+--------- 1.5 | 1.495 | 1.4949000000 | 1 (1 row)
sin(x)The sin( ) function accepts a single argument
x of type double precision,
representing an angle described in radians. The sine of the argument is returned as a value
of type double precision.
sqrt(x)The sqrt( ) function accepts a single argument x, of either type double precision, or numeric, and returns its square root. The returned value is of the same data type
passed to it. The sqrt function is effectively the inverse of the pow( ) function, used with a power of 2.
tan(x)The tan( ) function accepts a single argument x, of type double precision, representing an angle
described in radians. The tangent of the argument is returned as a value of type double precision.
trunc(x) trunc(x, s)
The trunc( ) function accepts one or two
arguments, x and s. The x argument may be of
the numeric or double precision type, and represents
the value to be truncated. The s argument may be of
the integer type.
If specified, s dictates the number of digits
allowed to the right of the decimal before truncation. If unspecified, any digits past the
decimal in x are truncated. If more digits are
specified by s than there are represented by
x, the extra digits will be padded with
zeroes.
PostgreSQL supports a wide variety of text formatting, analysis and comparison functions.
These include both SQL92 standard functions, such as substring( ) and
trim( ), as well as PostgreSQL-specific extensions, such as ltrim( ), rtrim( ) and substr( ). Table 5-10 lists the functions available to PostgreSQL
for use with character strings. In general, when referring to a value of type text, it is functionally synonymous with a value of type character, or varchar.
Table 5-10. Character string functions
|
Function |
Description |
|---|---|
|
|
Returns the ascii code of the first character passed to it in character string
|
|
|
Returns character string |
|
|
Returns the numeric length of character string |
|
|
Returns the character whose ascii value corresponds to the number |
|
|
Returns true if the expression |
|
|
Returns the character string |
|
|
Returns the numeric length of character string |
|
|
Returns true if the expression |
|
|
Returns the string |
|
|
Returns the character string |
|
|
Returns character string |
|
|
Returns the number of 8-bit bytes in character string |
|
|
Returns the location of character sub-string |
|
|
Returns the character string |
|
|
Returns the character string |
|
|
Returns character string |
|
|
Returns the location of character sub-string |
|
|
Returns a character sub-string of the character string |
|
|
Returns a character sub-string of the character string |
|
|
Returns text |
|
|
Returns the character string |
|
|
Returns character string |
|
|
Returns the character string |
The following sections describe each of these character string functions, detailing their argument requirements, return types, and general usage.
ascii(s)The ascii( ) function accepts a single argument of either a single
character, or a character string of type text, and returns the numeric
ASCII value of the first character interpreted. The result is returned as a value of type
integer.
Example 5-43.
booktown=# SELECT ascii('T'); ascii ------- 84 (1 row) booktown=# SELECT DISTINCT ON (substr) booktown-# title, substr(title, 1, 1), booktown-# ascii(title) booktown-# FROM books booktown-# ORDER BY substr ASC; title | substr | ascii -----------------------------+--------+------- 2001: A Space Odyssey | 2 | 50 Bartholomew and the Oobleck | B | 66 Dune | D | 68 Franklin in the Dark | F | 70 Goodnight Moon | G | 71 Little Women | L | 76 Practical PostgreSQL | P | 80 The Shining | T | 84 (8 rows)
btrim(s) btrim(s, t)
The btrim( ) function accepts one or two
arguments s, and (optionally) t, each of type text. If t is specified, the function trims the string value
s of any leading or trailing strings consisting
solely of characters described in t. If
t is not specified, leading and trailing whitespace
is trimmed. The resultant trimmed value is returned as type text.
It is important to understand that the order of the characters described by
t is not relevant to btrim( ).
Any strings at the beginning or end of s that
consecutively match any of the characters described in
t will be trimmed.
char_length(s)The char_length( ) SQL92 function accepts a single argument of type
text, varchar, or character, and
returns the number of characters in the character string s passed to it. The returned value is of type integer.
chr(n)The chr( ) function accepts a single numeric argument
n of type integer, and returns
the corresponding character value for that ASCII value of n. The resultant value is of type text.
The chr( ) function is effectively the inverse
of the ascii function.
initcap(s)
The initcap( ) function accepts a single argument s of type text, and returns its value, with the
first letter of each word capitalized. In this context, a “word” is a string of characters
separated from other words by whitespace.
length(s)
Functionally identical to the char_length( ) SQL92 function. Accepts
a single argument s of type text,
character, or varchar, and returns its length as a
value of type integer.
s like(f) s LIKE f like(s, f) s ilike(f) s ILIKE f
The
like( ) function checks the expression described by f, and attempts to see if it matches the character string
s. It may either accept two arguments of type
text, s and f, or it may be used in a special SQL syntax format where the
argument s precedes the function name, adding to the
readability of the statement. The ilike( ) function is a non-standard,
case-insensitive version of like( ), and may only be invoked through the
SQL-style syntax.
Note
The SQL keyword LIKE actually invokes the like( )
function with PostgreSQL. The ability to use the LIKE keyword without
parentheses to invoke this functionality is a syntactic convenience, and there is no
different in practice.
The use of like( ) differs from a normal equivalence operation in
that the character string f may contain either an
underscore (_) or percent (%) symbol to indicate
special meaning in matching character values. PostgreSQL interprets the _
symbol as indicating that any single character should be considered a match, while the
% symbol is interpreted as indicating that zero or more characters of any
value will be considered a match. These special characters may be interspersed throughout the
character string f.
For more advanced pattern matching capabilities within PostgreSQL, see the section titled Regular expression matching operators earlier in this chapter.
Example 5-49.
booktown=# SELECT * FROM books booktown-# WHERE title LIKE ('%Rabbit'); id | title | author_id | subject_id ------+----------------------+-----------+------------ 1234 | The Velveteen Rabbit | 25041 | 3 (1 row) booktown=# SELECT * FROM books booktown-# WHERE title LIKE '%D___'; id | title | author_id | subject_id -------+----------------------+-----------+------------ 4513 | Dune | 1866 | 15 25908 | Franklin in the Dark | 15990 | 2 (2 rows) booktown=# SELECT * FROM books booktown-# WHERE title ILIKE '%python%'; id | title | author_id | subject_id -------+--------------------+-----------+------------ 41473 | Programming Python | 7805 | 4 41477 | Learning Python | 7805 | 4 (2 rows)
lower(s)The lower( ) SQL92 function accepts a single
character string argument s of type text, and returns the same value with all characters converted to lowercase. The
resultant value is returned as type text.
lpad(s, n) lpad(s, n, c)
The lpad( ) function accepts either two or three arguments
s, n, and
optionally c, of types text,
integer, and text, respectively. The function “pads”
the left side of the character string s with either
whitespace, or the optional character string defined by c, until it is exactly n
characters in length.
If the character string s is initially longer
than n, s
will be truncated from the right until it is exactly the length of n.
Example 5-51.
booktown=# SELECT title, lpad(title, 12, '-') AS dashed, booktown-# lpad(title, 12, '-+-') AS plus_dashed booktown-# FROM books LIMIT 4; title | dashed | plus_dashed -----------------------+--------------+-------------- The Shining | -The Shining | -The Shining Dune | --------Dune | -+--+--+Dune 2001: A Space Odyssey | 2001: A Spac | 2001: A Spac The Cat in the Hat | The Cat in t | The Cat in t (4 rows)
ltrim(s) ltrim(s, f)
The ltrim( ) function accepts either one or two
arguments, s and optionally f, each of type text. If f is unspecified, the function returns the value of
s, with any leading whitespace trimmed off.
Otherwise, the function returns the character string s, with any leading substring containing exclusively characters
contained in f removed. If no such substring is
found, no change is made.
Example 5-52.
booktown=# SELECT ltrim(' whitespace example'); ltrim -------------------- whitespace example (1 row) booktown=# SELECT title, ltrim(title, 'TD2he ') booktown-# FROM books booktown-# LIMIT 4; title | ltrim -----------------------+---------------------- The Shining | Shining Dune | une 2001: A Space Odyssey | 001: A Space Odyssey The Cat in the Hat | Cat in the Hat (4 rows)
char_length(s)The octet_length( ) SQL92 function accepts a single argument of type
text, varchar or, character, and
returns the number of 8-bit character bytes in the character string s passed to it. The returned value is of type integer.
In most circumstances, there will be the same number of octets as there are characters to a character string, though this may not necessarily be the case with multibyte characters. This is because a multibyte character may consist of more than a single octet (byte), by definition.
position(b IN s)
The position( ) SQL92 function accepts two
arguments, b and s, each of type text. The position of the string
b within the string s is returned as a value of type integer (counting
from 1). If the string is not found, zero is returned.
repeat(s, n)
The repeat( ) function accepts two arguments s and n, of types text and integer, respectively. The function returns the
character string described by s, repeated
n consecutive times, as a value of type text.
rpad(s, n) rpad(s, n, c)
The rpad( ) function is essentially the same as the lpad function, but operates on the right side of the string
s, rather than the left. It accepts either two or
three arguments s, n, and optionally c, of types
text, integer, and text,
respectively. The function pads the right side of the character string s with either whitespace, or the optional character string defined by c, until it is exactly n
characters in length.
If the character string s is longer than
n characters long to begin with, it will be
truncated from the left until it is exactly n characters in length.
Example 5-56.
booktown=# SELECT rpad('whitespace example', 30); rpad -------------------------------- whitespace example (1 row) booktown=# SELECT title, rpad(title, 12, '-') AS right_dashed, booktown-# rpad(title, 12, '-+-') AS right_plus_dashed booktown-# FROM books booktown-# LIMIT 3; title | right_dashed | right_plus_dashed -----------------------+--------------+------------------- The Shining | The Shining- | The Shining- Dune | Dune-------- | Dune-+--+--+ 2001: A Space Odyssey | 2001: A Spac | 2001: A Spac (3 rows)
rtrim(s) rtrim(s, f)
The rtrim( ) function accepts either one or two arguments,
s and optionally f, each of type text. If f is unspecified, the function returns the value of
s, with any trailing whitespace trimmed off.
Otherwise, the function returns the character string s, with any trailing substring containing exclusively characters
contained in f removed. If no such substring is
found, no change is made.
Example 5-57.
booktown=# SELECT rtrim('whitespace example '); rtrim -------------------- whitespace example (1 row) booktown=# SELECT title, rtrim(title, 'yes') booktown-# FROM books booktown-# LIMIT 4; title | rtrim -----------------------+---------------------- The Shining | The Shining Dune | Dun 2001: A Space Odyssey | 2001: A Space Od The Cat in the Hat | The Cat in the Hat (4 rows)
strpos(s, b)
The strpos( ) function is functionally identical
to the SQL92 position( ) function, but accepts C-style arguments
b and s,
each of type text. The position of the string b within the string s is
returned as a value of type integer (counting from 1). If the string is
not found, zero is returned.
substr(s, n) substr(s, n, l)
The substr( ) function is effectively equivalent to the SQL92
function substring( ), but accepts C-style arguments s, n, and optionally
l, of types text, integer, and integer, respectively. The function returns the
substring of s, beginning at character index
n, and optionally stopping after l characters.
If the length of the substring to be selected is longer than the available characters, only the available substring will be returned. In other words, it will not be padded as it would be with a trim function.
Example 5-59.
booktown=# SELECT title, substr(title, 15), substr(title, 5, 9) booktown-# FROM books booktown-# ORDER BY title DESC booktown-# LIMIT 3; title | substr | substr ----------------------+--------+----------- The Velveteen Rabbit | Rabbit | Velveteen The Tell-Tale Heart | Heart | Tell-Tale The Shining | | Shining (3 rows)
substring(s FROM n) substring(s FROM n FOR l)
The substring( ) function is the SQL92 equivalent to the
PostgreSQL-specific substr( ) function. It accepts two or three arguments,
s, n, and
optionally l, of types text, integer, and
integer, respectively. The function returns the substring of
s, beginning at character index n, and optionally stopping after l characters.
Example 5-60.
booktown=# SELECT title, substring(title FROM 15) booktown-# FROM books booktown-# ORDER BY title DESC booktown-# LIMIT 3; title | substring ----------------------+----------- The Velveteen Rabbit | Rabbit The Tell-Tale Heart | Heart The Shining | (3 rows) booktown=# SELECT title, substring(title FROM 5 FOR 9) booktown-# FROM books booktown-# ORDER BY title DESC booktown-# LIMIT 3; title | substring ----------------------+----------- The Velveteen Rabbit | Velveteen The Tell-Tale Heart | Tell-Tale The Shining | Shining (3 rows)
to_ascii(s, f)
The to_ascii( ) function accepts a single argument s of type text containing multibyte text with
format f, and returns normal ASCII text as a value of
type text.
The available multibyte encoding formats are LATIN1 (ISO 8859-1), LATIN2 (ISO 8859-2), and WIN1250 (Windows CP1250, or WinLatin2). This function requires that multibyte encoding be enabled (which is a compile-time option when building and installing PostgreSQL).
translate(s, f, r)
The translate( ) function accepts three arguments, s, f and r, each of type text. It replaces any instance of
a character in the string s that matches any
character in f with the corresponding character at
the same index from string r. The result is returned
as a value of type text.
Note that this function does not replace only complete instances of the character string
f, but replaces any character
within s that matches any
character in f with the corresponding
character from r. If there are more characters in
f than in r, any character in f without a
corresponding character in r will simply be omitted
(this can be a useful way to remove unwanted characters).
The important thing to remember about this method of replacement is that there is always a one-to-one relationship between the character found and its replacement character (though its replacement may be empty, if omitted).
The following examples replace all question marks with exclamation points.
Example 5-62.
booktown=# SELECT translate('I am an example?', '?', '!');
translate
------------------
I am an example!
(1 row)The next example replaces all instances of the character i with the character w, and all instances of the character s with the character a. The extra s at the end of “was” is ignored.
booktown=# SELECT translate('This is a mistake.', 'is', 'was');
translate
--------------------
Thwa wa a mwatake.
(1 row)This final example replaces all vowels with nothing, effectively removing all vowels from the input strings.
booktown=# SELECT title, booktown-# translate(title, 'aeiouAEIOU', '') AS vowelless booktown-# FROM books booktown-# LIMIT 5; title | vowelless -----------------------------+-------------------- The Shining | Th Shnng Dune | Dn 2001: A Space Odyssey | 2001: Spc dyssy The Cat in the Hat | Th Ct n th Ht Bartholomew and the Oobleck | Brthlmw nd th blck (5 rows)
trim(side f FROM s)
The trim( )
function is the SQL92 function used to achieve the same effects as PostgreSQL’s rtrim( ), ltrim( ), and btrim( ) functions.
It accepts three arguments, including a leading keyword side (which may be either LEADING, TRAILING, or BOTH), and two character strings,
f and s.
When specified as LEADING, trim( ) behaves as
ltrim( ), trimming the longest substring from the beginning of the string
s which consist solely of characters contained
within f.
When specified as TRAILING, trim( ) behaves as
rtrim( ), trimming the longest substring from the end of the string
s which consists solely of characters contained
within f.
When specified as BOTH, trim( ) behaves as
btrim( ), trimming the longest substrings from both the beginning and end
of the string s which consists solely of characters
contained within f.
Example 5-63.
booktown=# SELECT isbn, trim(LEADING '0' FROM isbn) booktown-# FROM editions booktown-# LIMIT 2; isbn | ltrim ------------+----------- 039480001X | 39480001X 0451160916 | 451160916 (2 rows) booktown=# SELECT isbn, trim(TRAILING 'X' FROM isbn) booktown-# FROM editions booktown-# LIMIT 2; isbn | rtrim ------------+------------ 039480001X | 039480001 0451160916 | 0451160916 (2 rows) booktown=# SELECT isbn, trim(BOTH '0X' FROM isbn) booktown-# FROM editions booktown-# LIMIT 2; isbn | btrim ------------+----------- 039480001X | 39480001 0451160916 | 451160916 (2 rows)
upper(s)The upper( ) SQL92 function accepts a single argument s of type text, and returns the character string
with each character converted to lowercase as a value of type text.
Example 5-64.
booktown=# SELECT title, upper(title) booktown-# FROM books booktown-# ORDER BY id ASC booktown-# LIMIT 3; title | upper ----------------------+---------------------- The Tell-Tale Heart | THE TELL-TALE HEART Little Women | LITTLE WOMEN The Velveteen Rabbit | THE VELVETEEN RABBIT (3 rows)
The standard SQL92 date and time functions (current_date, current_time, current_timestamp, and extract(
)) are each supported by PostgreSQL, as well as a variety of PostgreSQL-specific
extensions. Each of PostgreSQL’s date and time retrieval and extraction functions are listed
in Table 5-11.
Table 5-11. Date and time functions
|
Function |
Description |
|---|---|
|
|
Returns the current date as a value of type |
|
|
Returns the current time as a value of type |
|
|
Returns the current date and time as a value of type |
|
|
Returns a date or time element from |
|
|
Returns a date or time element from |
|
|
Returns |
|
|
Returns a date or time element from |
|
|
Returns a date or time element from |
|
|
Returns true if the |
|
|
Returns true if the |
|
|
Returns the date and time as a |
|
|
Returns the current date and time as a |
The following sections
elaborate on each of PostgreSQL’s date and time functions described in Table 5-11. Note that the syntax for the current_date, current_time and current_timestamp functions omits the parentheses. This is done to remain
compliant with the SQL92 standard requirements.
current_date
The current_date function accepts no arguments, and returns the
current date as a value of type date. This is identical to casting the
special now constant to a value of type date.
current_time
The current_time function accepts no arguments, and returns the
current time as a value of type time. This is identical to casting the
special now constant to a value of type time.
current_timestamp
The current_timestamp function accepts no
arguments, and returns the current date and time as a value of type timestamp. This is identical to casting the special now
constant to a value of type timestamp, or to calling the now(
) function.
date_part(s, t) date_part(s, i)
The date_part( ) function accepts two arguments, s of type text, and either t of type timestamp, or i of type interval. The function removes the part
of the time length specified by s, and returns it as
a value of type double precision.
To understand the function of date_part( ), it can be helpful to
think of a timestamp or interval value as being broken
up into several fields. These fields each describe a discrete component
of the temporal value, such as the number of days, hours, or minutes described. The valid
values for time field units described by s are
detailed in Table 5-12. Notice that some values are only
appropriate for use with a timestamp value, and not with an interval.
Table 5-12. Timestamp and interval units
|
Unit |
Description |
|---|---|
|
century |
Describes the year field, divided by 100 (will not describe the literal century) |
|
day |
Describes the day field, from 1 to 31, for a |
|
decade |
Describes the year field, divided by 10 |
|
dow |
Describes the day of the week field, from 0 to 6 (beginning on Sunday), for a
|
|
doy |
Describes the day of the year field, from 1 to 365 or 366 for a |
|
epoch |
Describes the number of seconds since the epoch (Jan 1, 1970)
for a |
|
hour |
Describes the hour represented by a |
|
microseconds |
Describes the millionths of seconds following the decimal in the seconds field of a
|
|
millennium |
Describes the year field, divided by 1000 (will not describe the literal millennium) |
|
milliseconds |
Describes the thousandths of seconds following the decimal in the seconds field of
a |
|
minute |
Describes the minutes field of a |
|
month |
Describes the month of the year for a |
|
quarter |
Describes the quarter of the year, from 1 to 4, for |
|
second |
Describes the seconds field of a |
|
week |
Describes the week of the year of a |
|
year |
Describes the year field of a |
Example 5-68.
booktown=# SELECT date_part('minute', booktown(# interval('3 days 4 hours 12 minutes')); date_part ----------- 12 (1 row) booktown=# SELECT isbn, booktown-# date_part('year', publication) booktown-# FROM editions booktown-# ORDER BY date_part ASC booktown-# LIMIT 3; isbn | date_part ------------+----------- 0760720002 | 1868 0679803335 | 1922 0694003611 | 1947 (3 rows)
date_trunc(s, t)
The date_trunc( ) function accepts two arguments
s and t, of
types text and timestamp, respectively. The character
string s defines the degree to which the timestamp
value t should be truncated. In this context,
truncation means eliminating an amount of detail in the value represented.
See Table 5-12 for valid values for time unit
s.
Example 5-69.
booktown=# SELECT date_trunc('minute', now( )); date_trunc ------------------------ 2001-08-31 09:59:00-07 (1 row) booktown=# SELECT date_trunc('hour', now( )); date_trunc ------------------------ 2001-08-31 09:00:00-07 (1 row) booktown=# SELECT date_trunc('year', now( )); date_trunc ------------------------ 2001-01-01 00:00:00-08 (1 row)
extract(k FROM t) extract(k FROM i)
The extract( ) function is the SQL92 equivalent to PostgreSQL’s
date_part( ) function, with a slightly modified syntax. The SQL syntax
for this function uses the FROM keyword, rather than a comma. The
arguments are similar to those for the date_part( ) function, though it
differs in that its first argument is a SQL keyword, rather than a
character string, and should therefore not be quoted. Valid values for k are the same as those listed in Table 5-12.
Note that the extract( ) function exists as a SQL92 syntax “alias”
for the PostgreSQL date_part( ) function; for this reason, the output
column name from PostgreSQL is, by default, date_part.
isfinite(t) isfinite(i)
The isfinite( ) function accepts one argument,
of type timestamp or type interval. It returns true if
the value passed to it is not found to be an infinite value, which would be one set with
either the special constant infinity or
invalid (a special timestamp
constant only).
now( )
The now( ) function accepts no arguments, and returns the time and
date of when now( ) is executed by PostgreSQL, in the form of a timestamp value.
timeofday( )
The timeofday( ) function accepts no arguments. It returns the time
and date of when the function is executed by PostgreSQL. The timeofday( )
function is similar in use to the now( ) function. However, the timeofday( ) function returns a value of the type text. This
means that it is less flexible to work with, as you cannot use the date_part(
) or to_char( ) functions to break down elements of the value
without casting it first to
another type. It can be useful for applications that require a Unix style timestamp, as well
as providing extended precision for the seconds value.
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 |
|---|---|
|
|
Converts numeric value |
|
|
Converts bit string |
|
|
Converts numeric value |
|
|
Converts timestamp |
|
|
Converts character string |
|
|
Converts character string |
|
|
Converts character string |
|
|
Returns the |
|
|
Returns a |
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.
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.
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 |
|
0 |
The next sequential digit in |
|
. |
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 |
|
SG |
A plus or minus sign, depending on the value of |
|
MI |
A minus sign, if the |
|
PL |
A plus sign, if |
|
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 |
|
TH, th |
The appropriate ordinal suffix for |
|
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.
Example 5-76.
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)
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 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).
Example 5-77.
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 5-78.
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 accepts two arguments of the text type,
s and f.
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.
Example 5-79.
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 5-80.
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.
An
aggregate function is a special kind of function that operates on
several rows of a query at once, returning a single result. Such
functions are generally only used in queries which make use of the GROUP BY
clause to associate rows together by like criteria, though they may be used in queries which
only contain aggregate functions in their target list. When performing the latter, the
aggregate function operates on all selected rows from the result
set.
Table 5-16 provides an overview of PostgreSQL’s
supported aggregate functions. To see a complete list of aggregate functions, you may use the
\da command within psql.
Table 5-16. Aggregate functions
|
Function |
Description |
|---|---|
|
|
Returns the average of the |
|
|
Returns the number of values, per each aggregated group of rows, for which
|
|
|
Returns the maximum value of |
|
|
Returns the minimum value of |
|
|
Returns the standard deviation of the values of |
|
|
Returns the sum of the values of |
|
|
Returns the variance of the values of |
The following sections describe each aggregate function in further detail, including
specific information on usage, examples, and valid input data types. In each of the functional
explanations, the term expression refers to any valid
identifier in a result set, or any valid expression operating on such an identifier.
When calling an aggregate function, aggregate expressions are
employed to describe an expression from the result set created by the SELECT statement. An aggregate expression is similar to an ordinary SQL
expression, but may be preceded by either the ALL or the DISTINCT keyword.
The use of the DISTINCT keyword in an aggregate expression causes
only grouped rows with unique values (as described by the expression) to be evaluated by the
function. Any duplicate rows will be suppressed. Similar to the use of the ALL
keyword in a SELECT statement, the use of
ALL in an aggregate expression has no function other than to make more
explicit the request for all grouped rows to be evaluated to the function. Example 5-82 demonstrates each of the aggregate expression
forms.
Example 5-82. Using aggregate expressions
booktown=# SELECT count(location) AS set_locations, booktown-# count(ALL location) AS all_set_locations, booktown-# count(DISTINCT location) AS unique_locations, booktown-# count(*) AS all_rows booktown-# FROM subjects; set_locations | all_set_locations | unique_locations | all_rows ---------------+-------------------+------------------+---------- 15 | 15 | 7 | 16 (1 row)
There is one final form of aggregate expression, as demonstrated by the all_rows result column in Example 5-82. When the
asterisk (*) symbol is supplied as the aggregate expression, it instructs
the aggregate function to evaluate all rows, including rows with values
of NULL, which are ordinarily ignored. Since the subjects table contains one row with a NULL value in the
location column, the counted rows for location differ
from those counted for *.
avg(expression)The avg( ) function accepts an expression describing aggregated
values that are either of any numeric type (numeric, bigint, smallint, real, or double
precision), or of the interval time type.
The average, or mean, of the values described by expression in the grouped rows is returned. The resultant value is
returned as a value of type numeric for expressions of type integer and double precision for expressions of type real. All other expression types cause a value of the same data type to be
returned.
Example 5-83.
booktown=# SELECT avg(cost) AS average_cost, booktown-# avg(retail) AS average_price, booktown-# avg(retail - cost) AS average_profit booktown-# FROM stock; average_cost | average_price | average_profit ---------------+---------------+---------------- 24.8235294118 | 30.0088235294 | 5.1852941176 (1 row) booktown=# SELECT avg(cost) AS average_cost, p.name AS publisher booktown-# FROM (stock JOIN editions USING (isbn)) booktown-# JOIN publishers AS p (publisher_id) booktown-# USING (publisher_id) booktown-# GROUP BY p.name; average_cost | publisher ---------------+----------------------------- 26.5000000000 | Ace Books 19.0000000000 | Books of Wonder 26.5000000000 | Doubleday 25.0000000000 | HarperCollins 18.0000000000 | Henry Holt & Company, Inc. 23.0000000000 | Kids Can Press 23.0000000000 | Mojo Press 20.0000000000 | Penguin 23.0000000000 | Random House 26.5000000000 | Roc 26.0000000000 | Watson-Guptill Publications (11 rows)
count(expression)The count( ) function returns the number of
values in a set of aggregated rows where the expression is not NULL. The count(
) is not restricted as to the data type described by expression. It is important to understand that the count(
) function only counts values which are not NULL. As a result,
it is important to use an expression whose value will
not be returned NULL in order for the expression to be meaningful to the counted results.
You may pass the asterisk (*) character to count(
) in order to simply count all rows in an aggregation (including rows with
NULL values).
Example 5-84.
booktown=# SELECT count(*) FROM editions; count ------- 17 (1 row) booktown=# SELECT count(isbn), p.name booktown-# FROM editions JOIN publishers AS p (publisher_id) booktown-# USING (publisher_id) booktown-# GROUP BY p.name booktown-# ORDER BY count DESC; count | name -------+----------------------------- 3 | Random House 2 | Ace Books 2 | Doubleday 2 | Roc 1 | Books of Wonder 1 | HarperCollins 1 | Henry Holt & Company, Inc. 1 | Kids Can Press 1 | Mojo Press 1 | O'Reilly & Associates 1 | Penguin 1 | Watson-Guptill Publications (12 rows)
max(expression)The max( ) function returns the maximum found
value described by expression in a set of aggregated
rows. It accepts an expression that may represent any
numeric, string, date, or time data type. The maximum is returned as a value of the same data
type as the expression.
Example 5-85.
booktown=# SELECT max(cost), max(retail) FROM stock; max | max -------+------- 36.00 | 46.95 (1 row) booktown=# SELECT max(retail), p.name booktown-# FROM (stock NATURAL JOIN editions) booktown-# JOIN publishers AS p (publisher_id) booktown-# USING (publisher_id) booktown-# GROUP BY p.name booktown-# ORDER BY max DESC; max | name -------+----------------------------- 46.95 | Roc 45.95 | Ace Books 36.95 | Doubleday 32.95 | Random House 28.95 | HarperCollins 28.95 | Watson-Guptill Publications 24.95 | Mojo Press 24.95 | Penguin 23.95 | Henry Holt & Company, Inc. 23.95 | Kids Can Press 21.95 | Books of Wonder (11 rows)
min(expression)The min( ) function returns the minimum found
value described by expression in a set of aggregated
rows. It accepts an expression which may represent
any numeric, string, date, or
time data type. The minimum is returned as a value of the same data type
as the expression.
Example 5-86.
booktown=# SELECT min(cost), min(retail) FROM stock; min | min -------+------- 16.00 | 16.95 (1 row) booktown=# SELECT min(retail), p.name booktown-# FROM (stock NATURAL JOIN editions) booktown-# JOIN publishers AS p (publisher_id) booktown-# USING (publisher_id) booktown-# GROUP BY p.name booktown-# ORDER BY min ASC; min | name -------+----------------------------- 16.95 | Random House 21.95 | Ace Books 21.95 | Books of Wonder 22.95 | Roc 23.95 | Henry Holt & Company, Inc. 23.95 | Kids Can Press 24.95 | Mojo Press 24.95 | Penguin 28.95 | Doubleday 28.95 | HarperCollins 28.95 | Watson-Guptill Publications (11 rows)
stddev(expression)The stddev( ) function accepts an expression describing values of any
numeric type (numeric, bigint, smallint, real, or double precision), and
returns the standard deviation of the values within the aggregated rows. The resultant value
is returned as double precision for an expression describing floating
point values, and numeric for all other types.
Example 5-87.
booktown=# SELECT stddev(retail) FROM stock; stddev -------- 8.46 (1 row) booktown=# SELECT stddev(retail), p.name booktown-# FROM (stock NATURAL JOIN editions) booktown-# JOIN publishers AS p ON (publisher_id = p.id) booktown-# GROUP BY p.name booktown-# ORDER BY stddev DESC booktown-# LIMIT 4; stddev | name --------+-------------- 16.97 | Ace Books 16.97 | Roc 8.02 | Random House 5.66 | Doubleday (4 rows)
sum(expression)The sum( ) function accepts an expression
describing values of any numeric type (numeric, bigint,
smallint, real, or double
precision), and returns the sum of the values within the aggregated rows. The
returned value is of the type numeric when operating on values of type
integer and double precision when operating on values
of type real. The result is returned as the same data type as the values
described by expression for all other data
types.
Example 5-88.
booktown=# SELECT sum(stock) FROM stock; sum ----- 508 (1 row) booktown=# SELECT sum(stock), s.subject booktown-# FROM ((stock NATURAL JOIN editions) booktown(# JOIN books ON (books.id = book_id)) booktown-# JOIN subjects AS s booktown-# ON (books.subject_id = s.id) booktown-# GROUP BY s.subject booktown-# ORDER BY sum DESC; sum | subject -----+------------------ 189 | Horror 166 | Science Fiction 91 | Children's Books 28 | Drama 18 | Classics 16 | Arts (6 rows)
variance(expression)The variance( ) function accepts an expression
describing values of any numeric type (numeric, bigint,
smallint, real, or double
precision) and returns the variance of the values within the aggregated rows. The
variance is equivalent to the stddev( ) squared. The resultant value is
returned as double precision for an expression describing floating-point
values, and numeric for all other types.
Example 5-89.
booktown=# SELECT variance(retail) FROM stock; variance ---------- 71.60 (1 row) booktown=# SELECT variance(retail), p.name booktown-# FROM (stock NATURAL JOIN editions) booktown-# JOIN publishers AS p booktown-# ON (editions.publisher_id = p.id) booktown-# GROUP BY p.name booktown-# ORDER BY variance DESC booktown-# LIMIT 4; variance | name ----------+----------------------------- 288.00 | Ace Books 288.00 | Roc 64.33 | Random House 32.00 | Doubleday (4 rows)