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 functionscurrent_date,current_time, andcurrent_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.

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

| 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 y |

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

| Returns the largest whole integer not greater than |

| Returns the natural logarithm of exp() function) |

| Returns the base x |

| Returns the base 10 logarithm of |

| Returns the remainder (modulus) when dividing y |

| Returns the |

| Returns value of y |

| Returns radian equivalent to |

| Returns a pseudo-random value from 0.0 to 1.0 |

| Returns |

| Returns the value of decimal placess |

| Returns the sine of |

| Returns the square root of |

| Returns the tangent of |

| Returns the value of |

| Returns the value of decimal points truncateds |

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
form of the same data type which was passed to it.

*Example*

testdb=#testdb-#SELECT abs(100) AS abs_positive,abs_positive | abs_negative --------------+-------------- 100 | 100 (1 row)abs(-100) AS abs_negative;

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

*Example*

testdb=#testdb-#SELECT acos(1), acos(0), acos(-1),acos | acos | acos | inverse_example ------+-----------------+------------------+----------------- 0 | 1.5707963267949 | 3.14159265358979 | 1 (1 row)acos(cos(1)) AS inverse_example;

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

*Example*

testdb=#testdb-#SELECT asin(1), asin(0), asin(-1),asin | asin | asin | inverse_example -----------------+------+------------------+----------------- 1.5707963267949 | 0 | -1.5707963267949 | 1 (1 row)asin(sin(1)) AS inverse_example;

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

*Example*

testdb=#testdb-#SELECT atan(1), atan(0), atan(-1),atan | atan | atan | inverse_example -------------------+------+--------------------+----------------- 0.785398163397448 | 0 | -0.785398163397448 | 1 (1 row)atan(tan(1)) AS inverse_example;

atan2(,x)y

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

*Example*

testdb=#testdb-#SELECT atan2(0, 1), atan2(1, 1),testdb-#atan(0 / 1) AS functionally,atan2 | atan2 | functionally | identical -------+-------------------+--------------+------------------- 0 | 0.785398163397448 | 0 | 0.785398163397448 (1 row) testdb=#atan(1 / 1) AS identical;testdb-#SELECT atan2(1, 0) AS positive_x,testdb-#atan2(-1, 0) AS negative_x,testdb-#atan2(0, 0) AS zero_x,positive_x | negative_x | zero_x | pi_over_two -----------------+------------------+--------+----------------- 1.5707963267949 | -1.5707963267949 | 0 | 1.5707963267949 (1 row)pi() / 2 AS pi_over_two;

cbrt()x

The `cbrt()` function accepts a single `double precision`
argument ` x`, and returns its cubed root as a

*Example*

testdb=#testdb-#SELECT pow(2.0, 3) AS "two cubed",two cubed | eight's cube root -----------+------------------- 8 | 2 (1 row)cbrt(8.0) AS "eight's cube root";

ceil()x

The `ceil()` function accepts a value ` x` of any
numeric data type (

*Example*

testdb=#ceil | ceil | ceil ------+------+------ 1 | 2 | 2 (1 row)SELECT ceil(1.0), ceil(1.1), ceil(1.5);

cos()x

The `cos()` function accepts a single `double precision`
value ` x` representing an angle (in radians), and returns its cosine as a

*Example*

testdb=#testdb-#SELECT cos(pi()) AS cos_pi,cos_pi | cos_zero --------+---------- -1 | 1 (1 row)cos(0) AS cos_zero;

cot()x

The `cot()` function accepts a single `double precision`
value ` x` representing an angle (in radians), and returns its cotangent as a

*Example*

testdb=#cot | cot -------------------+-------------------- 0.642092615934331 | -0.642092615934331 (1 row)SELECT cot(1), cot(-1);

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

*Example*

testdb=#testdb-#SELECT degrees(acos(-1)) AS half_circle,half_circle | full_circle -------------+------------- 180 | 360 (1 row)degrees(pi() * 2) AS full_circle;

exp()x

The `exp()` function accepts a single `double precision`
or `numeric` argument ` x`, and returns the special

*Example*

testdb=#testdb-#SELECT exp(0.0) AS one,testdb-#exp(1.0) AS e,one | e | e squared -----+------------------+------------------ 1 | 2.71828182845905 | 7.38905609893065 (1 row)exp(2.0) AS "e squared";

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.

*Example*

testdb=#testdb-#SELECT floor(1.0) AS one,testdb-#floor(1.1) AS "one point one",one | one point one | one point eight -----+---------------+----------------- 1 | 1 | 1 (1 row)floor(1.8) AS "one point eight";

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

*Example*

testdb=#testdb-#SELECT ln(10.0) AS natural_log,testdb-#log(exp(1.0), 10.0) AS natural_log,natural_log | natural_log | inverse_example ------------------+------------------+----------------- 2.30258509299405 | 2.30258509299404 | 10 (1 row)ln(exp(10.0)) AS inverse_example;

log() log(x,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 the

*Example*

testdb=#testdb-#SELECT log(12.0) AS log_12,testdb-#log(10, 12.0) AS log_12,log_12 | log_12 | log 12, base 3 ------------------+--------------+---------------- 1.07918124604762 | 1.0791812460 | 2.2618595071 (1 row)log(3, 12.0) AS "log 12, base 3";

mod(,x)y

The `mod` function accepts two numeric arguments, ` x` and

*Example*

testdb=#testdb-#SELECT mod(5, 5) AS no_remainder,testdb-#mod(6, 5) AS remainder_one,no_remainder | remainder_one | remainder_four --------------+---------------+---------------- 0 | 1 | 4 (1 row)mod(19, 5) AS remainder_four;

pi()

The `pi()` function requires no arguments, and returns the
*pi* constant of roughly 3.14159265358979.

*Example*

testdb=#the pi constant ------------------ 3.14159265358979 (1 row)SELECT pi() AS "the pi constant";

pow(,x)y

The `pow()` function accepts two arguments, ` x` and

*Example*

testdb=#testdb-#SELECT pow(2.0, 3.0) AS "two cubed",testdb-#pow(2.0, 2.0) AS "two squared",two cubed | two squared | just two -----------+-------------+---------- 8 | 4 | 2 (1 row)pow(2.0, 1.0) AS "just two";

radians()d

The `radians()` function accepts a single argument ` d` of type

*Example*

testdb=#testdb-#SELECT radians(180) AS half_circle,half_circle | full_circle ------------------+------------------ 3.14159265358979 | 6.28318530717959 (1 row)radians(360) AS full_circle;

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*

testdb=#testdb-#SELECT random() AS natural_random,testdb-#round(random() * 9) + 1 AS one_through_ten,natural_random | one_through_ten | one_through_one_hundred -------------------+-----------------+------------------------- 0.478887704424042 | 2 | 37 (1 row)trunc(random() * 99) + 1 AS one_through_one_hundred;

round() round(x,x)s

The `round()` function may accept either one or two arguments. The first argument,
` x`, of type

If there are more digits specified by ` s` than by

*Example*

testdb=#testdb-#SELECT round(1.0) AS one,testdb-#round(1.1) AS "one point one",testdb-#round(1.5) AS "one point five",one | one point one | one point five | one point eight -----+---------------+----------------+----------------- 1 | 1 | 2 | 2 (1 row) testdb=#round(1.8) AS "one point eight";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,one_digit_scale | three_digit_scale | ten_digit_scale | rounded -----------------+-------------------+-----------------+--------- 1.5 | 1.495 | 1.4949000000 | 1 (1 row)round(1.4949, 0) AS rounded;

sin()x

The `sin()` function accepts a single argument ` x` of type

*Example*

testdb=#testdb-#SELECT sin(pi() / 4) AS quarter_pi,quarter_pi | half_pi -------------------+--------- 0.707106781186547 | 1 (1 row)sin(pi() / 2) AS half_pi;

sqrt()x

The `sqrt()` function accepts a single argument ` x`, of either type

*Example*

testdb=#testdb-#SELECT sqrt(2.0), sqrt(4.0),sqrt | sqrt | inverse_example -----------------+------+----------------- 1.4142135623731 | 2 | 2 (1 row)sqrt(pow(2.0, 2)) AS inverse_example;

tan()x

The `tan()` function accepts a single argument ` x`, of type

*Example*

testdb=#testdb-#SELECT tan(pi() / 8),tan | tan -------------------+----- 0.414213562373095 | 0 (1 row)tan(0);

trunc() trunc(x,x)s

The `trunc()` function accepts one or two arguments, ` x` and

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

*Example*

testdb=#testdb-#SELECT trunc(1.598) AS natural_truncation,testdb-#trunc(1.598, 1) AS one_decimal_point,natural_truncation | one_decimal_point | extra_places --------------------+-------------------+-------------- 1 | 1.5 | 1.59800000 (1 row)trunc(1.598, 8) AS extra_places;

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 (or whitespace, if t
is not specified)t |

| Returns the numeric length of character string |

| Returns the character whose ascii value corresponds to the number |

)f | Returns true if the expression s |

| Returns the character string |

| Returns the numeric length of character string |

)f | Returns true if the expression s |

| Returns the string |

| Returns the character string (or whitespace, if c is not defined to length of c characters (or truncated on the right to n characters)n |

| Returns character string (or whitespace, if f is not specified)f |

| Returns the number of 8-bit bytes in character string |

| Returns the location of character sub-string (counting from 1)s |

| Returns the character string timesn |

| Returns the character string (or whitespace, if c is not specified) to length of c characters (or truncated on the left to n characters)n |

| Returns character string (or whitespace, if f is not
specified)f |

| Returns the location of character sub-string (counting from 1). This is a PostgreSQL specific function which duplicates the effect of the SQL sposition() function, using C style arguments. |

| Returns a character sub-string of the character string (counting from 1), with optional maximum length n charactersl |

| Returns a character sub-string of the character string (counting from 1), with optional maximum length n charactersl |

| Returns text to plain ASCIIf |

| Returns the character string replaced with corresponding character in string fr |

| Returns character string , as dictated by the f
keyword (which is either sideLEADING, TRAILING or BOTH) |

| 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`.

*Examples*

booktown=#ascii ------- 84 (1 row) booktown=#SELECT ascii('T');booktown-#SELECT DISTINCT ON (substr)booktown-#title, substr(title, 1, 1),booktown-#ascii(title)booktown-#FROM bookstitle | 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)ORDER BY substr ASC;

btrim() btrim(s,s)t

The `btrim()` function accepts one or two arguments ` s`, and
(optionally)

It is important to understand that the order of the characters described by ` t` is not relevant
to

*Example*

booktown=#booktown-#SELECT btrim(' whitespace example ') AS trim_blanks,trim_blanks | trim_numbers ---------------------+-------------- whitespace example | example (1 row)btrim('123example 332', '123') AS trim_numbers;

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

*Example*

booktown=#booktown-#SELECT char_length(title), titlebooktown-#FROM bookschar_length | title -------------+----------------------- 11 | The Shining 4 | Dune 21 | 2001: A Space Odyssey (3 rows)LIMIT 3;

chr()n

The `chr()` function accepts a single numeric argument ` n` of
type

The `chr()` function is effectively the inverse of the
`ascii` function.

*Examples*

booktown=#chr | ascii -----+------- A | 65 (1 row)SELECT chr(65), ascii('A');

initcap()s

The `initcap()` function accepts a single argument ` s` of type

*Example*

booktown=#initcap -------------------------- A Prospective Book Title (1 row)SELECT initcap('a prospective book title');

length()s

Functionally identical to the `char_length()` SQL92 function. Accepts a single
argument ` s` of type

*Example*

booktown=#booktown-#SELECT length(title), titlebooktown-#FROM bookslength | title --------+----------------------- 11 | The Shining 4 | Dune 21 | 2001: A Space Odyssey (3 rows)LIMIT 3;

Note:The length evaluation functions for character strings defined in SQL92 arechar_length()andoctet_length(). Therefore, these functions are more likely to exist within other RDBMS systems than thelength()function.

like(s)fLIKEslike(f,s)filike(s)fILIKEsf

The `like()` function checks the expression described by ` f`, and
attempts to see if it matches the character string

Note:The SQL keywordLIKEactually invokes thelike()function with PostgreSQL. The ability to use theLIKEkeyword 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 (

For more advanced pattern matching capabilities within PostgreSQL, see the Section called *Regular expression matching operators*" earlier in this chapter.

*Examples*

booktown=#booktown-#SELECT * FROM booksid | title | author_id | subject_id ------+----------------------+-----------+------------ 1234 | The Velveteen Rabbit | 25041 | 3 (1 row) booktown=#WHERE title LIKE ('%Rabbit');booktown-#SELECT * FROM booksid | title | author_id | subject_id -------+----------------------+-----------+------------ 4513 | Dune | 1866 | 15 25908 | Franklin in the Dark | 15990 | 2 (2 rows) booktown=#WHERE title LIKE '%D___';booktown-#SELECT * FROM booksid | title | author_id | subject_id -------+--------------------+-----------+------------ 41473 | Programming Python | 7805 | 4 41477 | Learning Python | 7805 | 4 (2 rows)WHERE title ILIKE '%python%';

lower()s

The `lower()` SQL92 function accepts a single character string argument
` s` of type

*Example*

booktown=#booktown-#SELECT lower(title)booktown-#FROM bookslower ----------------------- the shining dune 2001: a space odyssey (3 rows)LIMIT 3;

lpad(,s) lpad(n,s,n)c

The `lpad()` function accepts either two or three arguments ` s`,

If the character string ` s` is longer than

*Example*

booktown=#booktown-#SELECT title, lpad(title, 12, '-') AS dashed,booktown-#lpad(title, 12, '-+-') AS plus_dashedbooktown-#FROM bookstitle | 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)LIMIT 4;

ltrim() ltrim(s,s)f

The `ltrim()` function accepts either one or two arguments, ` s`
and optionally

*Examples*

booktown=#ltrim -------------------- whitespace example (1 row) booktown=#SELECT ltrim(' whitespace example');booktown-#SELECT title, ltrim(title, 'TD2he ')booktown-#FROM bookstitle | 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)LIMIT 4;

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

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=#booktown-#SELECT title, octet_length(title)booktown-#FROM booksbooktown-#ORDER BY title ASCtitle | octet_length -----------------------------+-------------- 2001: A Space Odyssey | 21 Bartholomew and the Oobleck | 27 Dune | 4 (3 rows)LIMIT 3;

position(INb)s

The `position()` SQL92 function accepts two arguments, ` b` and

*Example*

booktown=#booktown-#SELECT title, position('the' IN title) AS the_posbooktown-#FROM bookstitle | the_pos -----------------------------+--------- The Cat in the Hat | 12 Bartholomew and the Oobleck | 17 Franklin in the Dark | 13 (3 rows)WHERE position('the' IN title) != 0;

repeat(,s)n

The `repeat()` function accepts two arguments ` s`
and

*Example*

booktown=#booktown-#SELECT repeat(last_name, 2)booktown-#FROM authorsrepeat -------------------- DenhamDenham BourgeoisBourgeois BiancoBianco (3 rows)LIMIT 3;

rpad(,s) rpad(n,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

If the character string ` s` is longer than

*Examples*

booktown=#rpad -------------------------------- whitespace example (1 row) booktown=#SELECT rpad('whitespace example', 30);booktown-#SELECT title, rpad(title, 12, '-') AS right_dashed,booktown-#rpad(title, 12, '-+-') AS right_plus_dashedbooktown-#FROM bookstitle | 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)LIMIT 3;

rtrim() rtrim(s,s)f

The `rtrim()` function accepts either one or two arguments, ` s`
and optionally

*Examples*

booktown=#rtrim -------------------- whitespace example (1 row) booktown=#SELECT rtrim('whitespace example ');booktown-#SELECT title, rtrim(title, 'yes')booktown-#FROM bookstitle | 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)LIMIT 4;

strpos(,s)b

The `strpos()` function is functionally identical to the SQL92
`position()` function, but accepts C-style arguments ` b` and

*Example*

booktown=#booktown-#SELECT title, strpos(lower(title), 'rabbit')booktown-#FROM bookstitle | strpos ----------------------+-------- The Velveteen Rabbit | 15 (1 row)WHERE strpos(lower(title), 'rabbit') != 0;

substr(,s) substr(n,s,n)l

The `substr()` function is effectively equivalent to the SQL92 function
`substring()`, but accepts C-style arguments ` s`,

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=#booktown-#SELECT title, substr(title, 15), substr(title, 5, 9)booktown-#FROM booksbooktown-#ORDER BY title DESCtitle | substr | substr ----------------------+--------+----------- The Velveteen Rabbit | Rabbit | Velveteen The Tell-Tale Heart | Heart | Tell-Tale The Shining | | Shining (3 rows)LIMIT 3;

substring(FROMs) substring(nFROMsFORn)l

The `substring()` function is the SQL92 equivalent to the PostgreSQL-specific
`substr()` function. It accepts two or three arguments, ` s`,

*Examples*

booktown=#booktown-#SELECT title, substring(title FROM 15)booktown-#FROM booksbooktown-#ORDER BY title DESCtitle | substring ----------------------+----------- The Velveteen Rabbit | Rabbit The Tell-Tale Heart | Heart The Shining | (3 rows) booktown=#LIMIT 3;booktown-#SELECT title, substring(title FROM 5 FOR 9)booktown-#FROM booksbooktown-#ORDER BY title DESCtitle | substring ----------------------+----------- The Velveteen Rabbit | Velveteen The Tell-Tale Heart | Tell-Tale The Shining | Shining (3 rows)LIMIT 3;

to_ascii(,s)f

The `to_ascii()` accepts a single argument ` s` of type

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

*Example*

booktown=#to_ascii ------------------- Multibyte Source (1 row)SELECT to_ascii('Multibyte Source', 'LATIN1');

translate(,s,f)r

The `translate()` function accepts three arguments, ` s`,

Note that this function does not replace only complete instances of the character string ` f`, but
replaces

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=#translate ------------------ I am an example! (1 row)SELECT translate('I am an example?', '?', '!');

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=#translate -------------------- Thwa wa a mwatake. (1 row)SELECT translate('This is a mistake.', 'is', 'was');

This final example replace all vowels with nothing, effectively removing all vowels from the input strings.

booktown=#booktown-#SELECT title,booktown-#translate(title, 'aeiouAEIOU', '') AS vowellessbooktown-#FROM bookstitle | 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)LIMIT 5;

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

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

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

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

*Examples*

booktown=#booktown-#SELECT isbn, trim(LEADING '0' FROM isbn)booktown-#FROM editionsisbn | ltrim ------------+----------- 039480001X | 39480001X 0451160916 | 451160916 (2 rows) booktown=#LIMIT 2;booktown-#SELECT isbn, trim(TRAILING 'X' FROM isbn)booktown-#FROM editionsisbn | rtrim ------------+------------ 039480001X | 039480001 0451160916 | 0451160916 (2 rows) booktown=#LIMIT 2;booktown-#SELECT isbn, trim(BOTH '0X' FROM isbn)booktown-#FROM editionsisbn | btrim ------------+----------- 039480001X | 39480001 0451160916 | 451160916 (2 rows)LIMIT 2;

upper()s

The `upper()` SQL92 function accepts a single argument ` s` of type

*Example*

booktown=#booktown-#SELECT title, upper(title)booktown-#FROM booksbooktown-#ORDER BY id ASCtitle | upper ----------------------+---------------------- The Tell-Tale Heart | THE TELL-TALE HEART Little Women | LITTLE WOMEN The Velveteen Rabbit | THE VELVETEEN RABBIT (3 rows)LIMIT 3;

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 s |

| Returns a date or time element from s |

| Returns s |

| Returns a date or time element from k |

| Returns a date or time element from k |

| Returns true if the invalid, nor infinity) |

| Returns true if the infinity) |

| 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`.

*Example*

booktown=#booktown-#SELECT current_date,date | date ------------+------------ 2001-08-31 | 2001-08-31 (1 row)'now'::date AS 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`.

*Example*

booktown=#booktown-#SELECT current_time,time | time ----------+---------- 11:36:52 | 11:36:52 (1 row)'now'::time AS 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.

*Example*

booktown=#booktown-#SELECT current_timestamp,timestamp | timestamp ------------------------+------------------------ 2001-08-31 11:39:42-07 | 2001-08-31 11:39:42-07 (1 row)now() AS timestamp;

date_part(,s) date_part(t,s)i

The `date_part()` function accepts two arguments, ` s` of type

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

**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 |

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 |

*Examples*

booktown=#booktown(#SELECT date_part('minute',date_part ----------- 12 (1 row) booktown=#interval('3 days 4 hours 12 minutes'));booktown-#SELECT isbn,booktown-#date_part('year', publication)booktown-#FROM editionsbooktown-#ORDER BY date_part ASCisbn | date_part ------------+----------- 0760720002 | 1868 0679803335 | 1922 0694003611 | 1947 (3 rows)LIMIT 3;

Note:The standard SQL function for achieving the same function as thedate_part()function is theextract()function.

date_trunc(,s)t

The `date_trunc()` function accepts two arguments ` s` and

See Table 5-12 for valid values for time unit ` s`.

*Example*

booktown=#date_trunc ------------------------ 2001-08-31 09:59:00-07 (1 row) booktown=#SELECT date_trunc('minute', now());date_trunc ------------------------ 2001-08-31 09:00:00-07 (1 row) booktown=#SELECT date_trunc('hour', now());date_trunc ------------------------ 2001-01-01 00:00:00-08 (1 row)SELECT date_trunc('year', now());

extract(FROMk) extract(tFROMk)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`.

*Examples*

booktown=#date_part ----------- 12 (1 row) booktown=#SELECT extract(MINUTE FROM interval('3 days 12 minutes'));date_part ----------- 8 (1 row)SELECT extract(MONTH FROM now());

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

*Example*

booktown=#booktown-#SELECT isfinite('now'::timestamp) AS now_is_finite,booktown-#isfinite('infinity'::timestamp) AS infinity,now_is_finite | infinity | invalid ---------------+----------+--------- t | f | f (1 row)isfinite('invalid'::timestamp) AS invalid;

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.

*Example*

booktown=#now ------------------------ 2001-08-31 10:31:18-07 (1 row)SELECT now();

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.

*Example*

booktown=#timeofday ------------------------------------- Fri Aug 31 10:33:00.837338 2001 PDT (1 row)SELECT timeofday();

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 f |

| Converts timestamp f |

| Converts character string to a fdate value |

| Converts character string to a fnumeric value |

| Converts character string to a ftimestamp value |

| Returns the timestamp |

| Returns a |

bitfromint4()n

The `bitfromint4()` function accepts a single argument ` n` of type

The returned value is of type `bit`, and may not exceed 32 bits. Therefore, since the
`integer` argument is signed, valid input values are between –2147483648
and 2147483647.

*Example*

booktown=#bitfromint4 ---------------------------------- 00000000000000000100000000000001 (1 row)SELECT bitfromint4(16385);

bittoint4()b

The `bittoint4()` function is essentially the inverse of the
`bitfromint4()` function; it accepts a single argument ` b` of type

The bounds of input and output are the reverse of the `bitfromint4` function,
in that it accepts up to 32 binary digits, and will thus not return more than 2147483647 or less than –2147483648
as its result value.

*Example*

booktown=#booktown-#SELECT bittoint4(B'101010'),bittoint4 | inverse_example -----------+----------------- 42 | 99 (1 row)bittoint4(bitfromint4(99)) AS inverse_example;

to_char(,n)f

The `to_char()` function, when used with argument ` n` of type

The ` f` format string consists of a series of

**Table 5-14. Numeric conversion formatting characters**

Character | Description |
---|---|

9 | The next sequential digit in the value |

0 | The next sequential digit in than are in f; may thus be used to force significant digits to the left or right of a valuen |

. | 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 PR at the end of surrounds the returned string in angle bracketsf |

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 4th, 2nd) |

V | Adds a zero to the right for each |

FM | Sets format to "fill mode," causing leading and trailing zeroes (created by the |

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

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=#booktown-#SELECT to_char(123456789, '999G999G999D99') AS formatted,booktown-#to_char(123456789, '999999999') AS just_digits,formatted | just_digits | with_zeroes -----------------+-------------+-------------- 123,456,789.00 | 123456789 | 00123456789 (1 row) booktown=#to_char(123456789, '00999999999') AS with_zeroes;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 translatedbooktown-#FROM stockcost_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=#LIMIT 3;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,first | second | third | shifted_up ------------+------------+------------+----------------- 1st Place | 2nd Place | 3rd Place | 1000th "Place" (1 row)to_char(10, '99V99th "\\"Place\\""') AS shifted_up;

Note:Note that as of PostgreSQL v7.1.x, there is a bug in the usage of theRNRoman Numeral conversion sequence which causes it to return invalid results unless used with theFMcharacter sequence. This is scheduled for correction in 7.2, but can be worked around by using the completeFMRNsequence.

to_char(,t)f

When used with argument ` t` of type

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

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=#the_time ---------- 05:04 PM (1 row) booktown=#SELECT to_char(now(), 'HH:MI PM') AS the_time;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')abbreviations | yesterday | yesterday UK ----------------------------------+-------------+-------------- Sat (Saturday ), Sep (September) | August 31st | 31st August (1 row) booktown=#AS "yesterday UK";booktown-#SELECT isbn, these must bebooktown-#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_publishedisbn | 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)FROM editions LIMIT 3;

to_date(,s)f

The `to_date()` function accepts two arguments ` s` and

While PostgreSQL can figure out a wide variety of date formats, it cannot support every arbitrary date format.
The `to_date()` function insures that, provided the format can be described using the meta-
characters from Table 5-14, nearly any date format can be converted to a valid date value.

*Example*

booktown=#booktown-#SELECT date('198025thJune')booktown-#AS non_standard_date_format,booktown-#to_date('198025thJune', 'YYYYDDthMonth')non_standard_date_format | correct_interpretation --------------------------+------------------------ 2025-08-27 | 1980-06-25 (1 row)AS correct_interpretation;

to_number(,s)f

The `to_number` function operates much like the inverse of the
`to_char()` function for numbers. It accepts two arguments ` s` and

*Examples*

booktown=#booktown-#SELECT to_number('$2,900.00', 'L9G999D99')monetary ---------- 2900.00 (1 row) booktown=#AS monetary;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')formatted | just_digits | leading_zeroes --------------+-------------+---------------- 123456789.00 | 123456789 | 123456789 (1 row)AS leading_zeroes;

to_timestamp(,s)f

The `to_timestamp()` function accepts two arguments ` s` and

Like `to_date()`, this function exists primarily as a means to be able to
correctly interpret the format of a non-standard date and time string.

*Example*

booktown=#booktown-#SELECT timestamp('197825thJuly01:12am')booktown-#AS non_standard_timestamp,booktown(#to_timestamp('197825July01:12am',booktown-#'YYYYDDFMMonthHH12:MIam')non_standard_timestamp | correct_interpretation ------------------------+------------------------ 2025-06-27 01:12:00-07 | 1978-07-25 01:12:00-07 (1 row)AS correct_interpretation;

Note:The use of theFMmodifier 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 theFMmodifier must precede each element which you wish it to apply to, as it is not a "global" modifier.

timestamp() timestamp(d,d)t

The `timestamp()` function accepts either a single argument ` d`
of type

*Example*

booktown=#booktown-#SELECT timestamp(date('now')) AS today_at_midnight,booktown(#timestamp(date('now'),today_at_midnight | right_now ------------------------+------------------------ 2001-09-01 00:00:00-07 | 2001-09-01 18:04:16-07 (1 row)time('now')) AS right_now;

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 NULL |

| 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-19 demonstrates each of the aggregate expression forms.

**Example 5-19. Using aggregate expressions**

booktown=#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_rowsset_locations | all_set_locations | unique_locations | all_rows ---------------+-------------------+------------------+---------- 15 | 15 | 7 | 16 (1 row)FROM subjects;

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 |

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

*Examples*

booktown=#booktown-#SELECT avg(cost) AS average_cost,booktown-#avg(retail) AS average_price,booktown-#avg(retail - cost) AS average_profitaverage_cost | average_price | average_profit ---------------+---------------+---------------- 24.8235294118 | 30.0088235294 | 5.1852941176 (1 row) booktown=#FROM stock;booktown-#SELECT avg(cost) AS average_cost, p.name AS publisherbooktown-#FROM (stock JOIN editions USING (isbn))booktown-#JOIN publishers AS p (publisher_id)booktown-#USING (publisher_id)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)GROUP BY p.name;

count()expression

The `count()` function returns the number of values in a set of aggregated rows
where the ` expression` is not

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=#count ------- 17 (1 row) booktown=#SELECT count(*) FROM editions;booktown-#SELECT count(isbn), p.namebooktown-#FROM editions JOIN publishers AS p (publisher_id)booktown-#USING (publisher_id)booktown-#GROUP BY p.namecount | 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)ORDER BY count DESC;

max()expression

The `max()` function returns the maximum found value described by
` expression` in a set of aggregated rows. It accepts an

*Examples*

booktown=#max | max -------+------- 36.00 | 46.95 (1 row) booktown=#SELECT max(cost), max(retail) FROM stock;booktown-#SELECT max(retail), p.namebooktown-#FROM (stock NATURAL JOIN editions)booktown-#JOIN publishers AS p (publisher_id)booktown-#USING (publisher_id)booktown-#GROUP BY p.namemax | 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)ORDER BY max DESC;

min()expression

The `min()` function returns the minimum found value described by
` expression` in a set of aggregated rows. It accepts an

*Examples*

booktown=#min | min -------+------- 16.00 | 16.95 (1 row) booktown=#SELECT min(cost), min(retail) FROM stock;booktown-#SELECT min(retail), p.namebooktown-#FROM (stock NATURAL JOIN editions)booktown-#JOIN publishers AS p (publisher_id)booktown-#USING (publisher_id)booktown-#GROUP BY p.namemin | 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)ORDER BY min ASC;

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.

*Examples*

booktown=#stddev -------- 8.46 (1 row) booktown=#SELECT stddev(retail) FROM stock;booktown-#SELECT stddev(retail), p.namebooktown-#FROM (stock NATURAL JOIN editions)booktown-#JOIN publishers AS p ON (publisher_id = p.id)booktown-#GROUP BY p.namebooktown-#ORDER BY stddev DESCstddev | name --------+-------------- 16.97 | Ace Books 16.97 | Roc 8.02 | Random House 5.66 | Doubleday (4 rows)LIMIT 4;

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.

*Examples*

booktown=#; sum ----- 508 (1 row) booktown=#SELECT sum(stock) FROM stockbooktown-#SELECT sum(stock), s.subjectbooktown(#FROM ((stock NATURAL JOIN editions)booktown-#JOIN books ON (books.id = book_id))booktown-#JOIN subjects AS sbooktown-#ON (books.subject_id = s.id)booktown-#GROUP BY s.subjectsum | subject -----+------------------ 189 | Horror 166 | Science Fiction 91 | Children's Books 28 | Drama 18 | Classics 16 | Arts (6 rows)ORDER BY sum DESC;

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.

*Examples*

booktown=#variance ---------- 71.60 (1 row) booktown=#SELECT variance(retail) FROM stock;booktown-#SELECT variance(retail), p.namebooktown-#FROM (stock NATURAL JOIN editions)booktown-#JOIN publishers AS pbooktown-#ON (editions.publisher_id = p.id)booktown-#GROUP BY p.namebooktown-#ORDER BY variance DESCvariance | name ----------+----------------------------- 288.00 | Ace Books 288.00 | Roc 64.33 | Random House 32.00 | Doubleday (4 rows)LIMIT 4;