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.
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:
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.
Note: The default name for a column that is described by a function in the target list will be the name
of the function, without trailing parentheses, or arguments (e.g., to_char).
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
abs(x )
Returns the absolute value of x
acos(x )
Returns the inverse cosine of x
asin(x )
Returns the inverse sine of x
atan(x )
Returns the inverse tangent of x
atan2(x , y )
Returns the inverse tangent of the quotient of x and y
cbrt(x )
Returns the cube root of x
ceil(x )
Returns the smallest whole integer not less than argument (rounds up)
cos(x )
Returns the cosine of x
cot(x )
Returns the cotangent of x
degrees(r )
Returns degrees from radians r
exp(x )
Returns the e constant (2.71828...), to the power of x
floor(x )
Returns the largest whole integer not greater than x (rounds down)
ln(x )
Returns the natural logarithm of x (the inverse of the exp() function)
log(b , x )
Returns the base b logarithm of x
log(x )
Returns the base 10 logarithm of x
mod(x , y )
Returns the remainder (modulus) when dividing x / y
pi()
Returns the pi constant (3.14159...)
pow(x , y )
Returns value of x to the exponential power of y
radians(d )
Returns radian equivalent to d degrees
random()
Returns a pseudo-random value from 0.0 to 1.0
round(x )
Returns x rounded to the nearest whole integer
round(x , s )
Returns the value of x, optionally rounded to s decimal places
sin(x )
Returns the sine of x
sqrt(x )
Returns the square root of x
tan(x )
Returns the tangent of x
trunc(x )
Returns the value of x, with any digits past the decimal point truncated
trunc(x , s )
Returns the value of x, with any digits past s decimal points truncated
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.
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
form of the same data type which was passed to it.
Example
testdb=# SELECT abs(100) AS abs_positive,
testdb-# abs(-100) AS abs_negative;
abs_positive | abs_negative
--------------+--------------
100 | 100
(1 row)
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.
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.
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.
Example
testdb=# SELECT atan(1), atan(0), atan(-1),
testdb-# atan(tan(1)) AS inverse_example;
atan | atan | atan | inverse_example
-------------------+------+--------------------+-----------------
0.785398163397448 | 0 | -0.785398163397448 | 1
(1 row)
Similar to the atan() function, the 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 dividing
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.
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.
Example
testdb=# SELECT pow(2.0, 3) AS "two cubed",
testdb-# cbrt(8.0) AS "eight's cube root";
two cubed | eight's cube root
-----------+-------------------
8 | 2
(1 row)
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.
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.
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 function of the radians() function.
Example
testdb=# SELECT degrees(acos(-1)) AS half_circle,
testdb-# degrees(pi() * 2) AS full_circle;
half_circle | full_circle
-------------+-------------
180 | 360
(1 row)
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.
Example
testdb=# SELECT exp(0.0) AS one,
testdb-# exp(1.0) AS e,
testdb-# exp(2.0) AS "e squared";
one | e | e squared
-----+------------------+------------------
1 | 2.71828182845905 | 7.38905609893065
(1 row)
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.
Example
testdb=# SELECT floor(1.0) AS one,
testdb-# floor(1.1) AS "one point one",
testdb-# floor(1.8) AS "one point eight";
one | one point one | one point eight
-----+---------------+-----------------
1 | 1 | 1
(1 row)
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.
Example
testdb=# SELECT ln(10.0) AS natural_log,
testdb-# log(exp(1.0), 10.0) AS natural_log,
testdb-# ln(exp(10.0)) AS inverse_example;
natural_log | natural_log | inverse_example
------------------+------------------+-----------------
2.30258509299405 | 2.30258509299404 | 10
(1 row)
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 the x. If two arguments are specified,
log(b , x ) returns the base b logarithm of
x.
Example
testdb=# SELECT log(12.0) AS log_12,
testdb-# log(10, 12.0) AS log_12,
testdb-# log(3, 12.0) AS "log 12, base 3";
log_12 | log_12 | log 12, base 3
------------------+--------------+----------------
1.07918124604762 | 1.0791812460 | 2.2618595071
(1 row)
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, and is of the same data type which is passed to the function.
Example
testdb=# SELECT mod(5, 5) AS no_remainder,
testdb-# mod(6, 5) AS remainder_one,
testdb-# mod(19, 5) AS remainder_four;
no_remainder | remainder_one | remainder_four
--------------+---------------+----------------
0 | 1 | 4
(1 row)
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.
Example
testdb=# SELECT pow(2.0, 3.0) AS "two cubed",
testdb-# pow(2.0, 2.0) AS "two squared",
testdb-# pow(2.0, 1.0) AS "just two";
two cubed | two squared | just two
-----------+-------------+----------
8 | 4 | 2
(1 row)
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.
Example
testdb=# SELECT radians(180) AS half_circle,
testdb-# radians(360) AS full_circle;
half_circle | full_circle
------------------+------------------
3.14159265358979 | 6.28318530717959
(1 row)
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()).
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
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)
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.
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.
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.
Example
testdb=# SELECT tan(pi() / 8),
testdb-# tan(0);
tan | tan
-------------------+-----
0.414213562373095 | 0
(1 row)
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.
Example
testdb=# SELECT trunc(1.598) AS natural_truncation,
testdb-# trunc(1.598, 1) AS one_decimal_point,
testdb-# trunc(1.598, 8) AS extra_places;
natural_truncation | one_decimal_point | extra_places
--------------------+-------------------+--------------
1 | 1.5 | 1.59800000
(1 row)
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
ascii(s )
Returns the ascii code of the first character passed to it in character string s
btrim(s [, t ])
Returns character string s, trimmed on the left and right of any substrings
consisting solely of letters in character string t (or whitespace, if t
is not specified)
char_length(s )
Returns the numeric length of character string s
chr(n )
Returns the character whose ascii value corresponds to the number n
s ilike(f )
Returns true if the expression f is found to match (case-insensitively) s
initcap(s )
Returns the character string s, with each word's first letter capitalized
length(s )
Returns the numeric length of character string s
s like(f )
Returns true if the expression f is found to match s
lower(s )
Returns the string s, in all lowercase
lpad(s , n [, c ])
Returns the character string s, padded to the left with character string c (or whitespace, if c is not defined to length of n characters (or truncated on the right to n characters)
ltrim(s [, f ])
Returns character string s, trimmed on the left of a substring consisting solely
of letters in character string f (or whitespace, if f is not specified)
octet_length(s )
Returns the number of 8-bit bytes in character string s
position(b IN s )
Returns the location of character sub-string b in character string s (counting from 1)
repeat(s , n )
Returns the character string s, repeated n times
rpad(s , n [, c ])
Returns the character string s, padded to the right with character string c (or whitespace, if c is not specified) to length of n characters (or truncated on the left to n characters)
rtrim(s [, f ])
Returns character string s, trimmed on the right of a substring consisting solely
of letters in character string f (or whitespace, if f is not
specified)
strpos(s , b )
Returns the location of character sub-string b in character string s (counting from 1). This is a PostgreSQL specific function which duplicates the effect of the SQL position() function, using C style arguments.
substr(s , n [, l ])
Returns a character sub-string of the character string s, starting at digit n (counting from 1), with optional maximum length l characters
substring(s FROM n FOR l )
Returns a character sub-string of the character string s, starting at digit n (counting from 1), with optional maximum length l characters
to_ascii(s , f )
Returns text s converted from multibyte encoding format f to plain ASCII
translate(s , f , r )
Returns the character string s, with any found characters from string f replaced with corresponding character in string r
trim(sidef FROM s)
Returns character string s, trimmed of leading and/or trailing substrings which
consist solely of letters in character string f, as dictated by the side
keyword (which is either LEADING, TRAILING or BOTH)
upper(s )
Returns the character string s, converted to all uppercase
The following sections describe each of these character string functions, detailing their argument requirements, return
types, and general usage.
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.
Examples
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)
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.
Example
booktown=# SELECT btrim(' whitespace example ') AS trim_blanks,
booktown-# btrim('123example 332', '123') AS trim_numbers;
trim_blanks | trim_numbers
---------------------+--------------
whitespace example | example
(1 row)
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.
Example
booktown=# SELECT char_length(title), title
booktown-# FROM books
booktown-# LIMIT 3;
char_length | title
-------------+-----------------------
11 | The Shining
4 | Dune
21 | 2001: A Space Odyssey
(3 rows)
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.
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.
Example
booktown=# SELECT initcap('a prospective book title');
initcap
--------------------------
A Prospective Book Title
(1 row)
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.
Example
booktown=# SELECT length(title), title
booktown-# FROM books
booktown-# LIMIT 3;
length | title
--------+-----------------------
11 | The Shining
4 | Dune
21 | 2001: A Space Odyssey
(3 rows)
Note: The length evaluation functions for character strings defined in SQL92 are
char_length() and octet_length(). Therefore, these
functions are more likely to exist within other RDBMS systems than the length()
function.
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.
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)
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.
Example
booktown=# SELECT lower(title)
booktown-# FROM books
booktown-# LIMIT 3;
lower
-----------------------
the shining
dune
2001: a space odyssey
(3 rows)
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 longer than n characters to begin with,
s will be truncated from the right until it is exactly n characters in length.
Example
booktown=# SELECT title, lpad(title, 12, '-') AS dashed,
booktown-# lpad(title, 12, '-+-') AS plus_dashed
booktown-# FROM books
booktown-# 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)
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.
Examples
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)
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.
Example
booktown=# SELECT title, octet_length(title)
booktown-# FROM books
booktown-# ORDER BY title ASC
booktown-# LIMIT 3;
title | octet_length
-----------------------------+--------------
2001: A Space Odyssey | 21
Bartholomew and the Oobleck | 27
Dune | 4
(3 rows)
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.
Example
booktown=# SELECT title, position('the' IN title) AS the_pos
booktown-# FROM books
booktown-# WHERE position('the' IN title) != 0;
title | the_pos
-----------------------------+---------
The Cat in the Hat | 12
Bartholomew and the Oobleck | 17
Franklin in the Dark | 13
(3 rows)
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.
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.
Examples
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)
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.
Examples
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)
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.
Example
booktown=# SELECT title, strpos(lower(title), 'rabbit')
booktown-# FROM books
booktown-# WHERE strpos(lower(title), 'rabbit') != 0;
title | strpos
----------------------+--------
The Velveteen Rabbit | 15
(1 row)
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
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)
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.
Examples
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)
The to_ascii() accepts a single argument s of type
text describing multibyte encoded text of the 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).
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.
Examples
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 replace 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)
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 consists 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.
Examples
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)
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
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
current_date
Returns the current date as a value of type date
current_time
Returns the current time as a value of type time
current_timestamp
Returns the current date and time as a value of type timestamp
date_part(s , t )
Returns a date or time element from timestampt as specified by character string s
date_part(s , i )
Returns a date or time element from intervali as specified by character string s
date_trunc(s , t )
Returns timestampt truncated to the degree specified by s
extract(k FROM t )
Returns a date or time element from timestampt as specified by the keyword k
extract(k FROM i )
Returns a date or time element from intervali as specified by the keyword k
isfinite(t )
Returns true if the timestampt is a finite value (neither invalid, nor infinity)
isfinite(i )
Returns true if the intervali is a finite value (not infinity)
now()
Returns the date and time as a timestamp value. This is equivalent to the now timestamp constant.
timeofday()
Returns the current date and time as a text value
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.
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.
Example
booktown=# SELECT current_date,
booktown-# 'now'::date AS date;
date | date
------------+------------
2001-08-31 | 2001-08-31
(1 row)
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.
Example
booktown=# SELECT current_time,
booktown-# 'now'::time AS time;
time | time
----------+----------
11:36:52 | 11:36:52
(1 row)
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.
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 timestamp, or the total number of days for an interval
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 timestamp, not applicable to an interval
doy
Describes the day of the year field, from 1 to 365 or 366 for a timestamp value, not application to an interval
epoch
Describes the number of seconds since the epoch (Jan 1, 1970) for a timestamp, or total number of seconds for an interval
hour
Describes the hour represented by a timestamp
microseconds
Describes the millionths of seconds following the decimal in the seconds field of a timestamp value
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 timestamp value
minute
Describes the minutes field of a timestamp or interval value
month
Describes the month of the year for a timestamp value, or the number of months modulo 12 for interval values
quarter
Describes the quarter of the year, from 1 to 4, for timestamp values
second
Describes the seconds field of a timestamp or interval value
week
Describes the week of the year of a timestamp value. ISO-8601 defines the first week of the year to be the week containing January 4.
year
Describes the year field of a timestamp or interval value
Examples
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)
Note: The standard SQL function for achieving the same function as the date_part() function
is the extract() function.
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.
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.
Examples
booktown=# SELECT extract(MINUTE FROM interval('3 days 12 minutes'));
date_part
-----------
12
(1 row)
booktown=# SELECT extract(MONTH FROM now());
date_part
-----------
8
(1 row)
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).
Example
booktown=# SELECT isfinite('now'::timestamp) AS now_is_finite,
booktown-# isfinite('infinity'::timestamp) AS infinity,
booktown-# isfinite('invalid'::timestamp) AS invalid;
now_is_finite | infinity | invalid
---------------+----------+---------
t | f | f
(1 row)
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
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 dated as a value of type timestamp
timestamp(d , t )
Returns a timestamp value derived from date d and time t
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.
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.
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.
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.
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.
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)
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.
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.
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.
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.
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
avg(expression )
Returns the average of the expression values from all rows in a group.
count(expression )
Returns the number of values, per each aggregated group of rows, for which expression is not NULL
max(expression )
Returns the maximum value of expression in the grouped rows
min(expression )
Returns the minimum value of expression in the grouped rows
stddev(expression )
Returns the standard deviation of the values of expression in the grouped rows
sum(expression )
Returns the sum of the values of expression in the grouped rows
variance(expression )
Returns the variance of the values of expression in the grouped rows
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-19 demonstrates each of the aggregate expression forms.
Example 5-19. 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-19. 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 *.
Warning
Rows whose evaluated aggregate expression contain NULL values will not be evaluated
by an aggregate function (with the exception of the count() function).
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.
Examples
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)
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).
Examples
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)
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.
Examples
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)
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.
Examples
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)
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.
Examples
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)
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.
Examples
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)
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.
Examples
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)
