Follow Techotopia on Twitter

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

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

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Mathematical Functions

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.

abs()

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=# 
SELECT abs(100) AS abs_positive,

testdb-# 
       abs(-100) AS abs_negative;

 abs_positive | abs_negative
--------------+--------------
          100 |          100
(1 row)

acos()

acos(
x
 )

The acos() function accepts a valid cosine, and returns the inverse (or arc) cosine of the double precision argument x (between –1 and 1) passed to it. This effectively returns the inverse of the cos() function. The result is a double precision value of an angle, in radians, between 0 and pi .

Example

testdb=# 
SELECT acos(1), acos(0), acos(-1),

testdb-# 
       acos(cos(1)) AS inverse_example;

 acos |      acos       |       acos       | inverse_example
------+-----------------+------------------+-----------------
    0 | 1.5707963267949 | 3.14159265358979 |               1
(1 row)

asin()

asin(
x
 )

The asin() function returns the inverse (or arc) sine of the double precision argument x (between –1 and 1) passed to it. Like acos(), this effectively returns the inverse of the sin() function. The result is a double precision value of an angle, in radians, between pi / 2 and –pi / 2 .

Example

testdb=# 
SELECT asin(1), asin(0), asin(-1),

testdb-# 
       asin(sin(1)) AS inverse_example;

      asin       | asin |       asin       | inverse_example
-----------------+------+------------------+-----------------
 1.5707963267949 |    0 | -1.5707963267949 |               1
(1 row)

atan()

atan(
x
 )

The atan() function returns the inverse (or arc) tangent of a double precision argument x passed to it, which effectively returns the inverse of the tan() function. The result is a double precision value of an angle, in radians, between pi / 2 and –pi / 2 .

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)

atan2()

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

Example

testdb=# 
SELECT atan2(0, 1), atan2(1, 1),

testdb-# 
       atan(0 / 1) AS functionally,

testdb-# 
       atan(1 / 1) AS identical;

 atan2 |       atan2       | functionally |     identical
-------+-------------------+--------------+-------------------
     0 | 0.785398163397448 |            0 | 0.785398163397448
(1 row)

testdb=# 
SELECT atan2(1, 0) AS positive_x,

testdb-# 
       atan2(-1, 0) AS negative_x,

testdb-# 
       atan2(0, 0) AS zero_x,

testdb-# 
       pi() / 2 AS pi_over_two;

   positive_x    |    negative_x    | zero_x |   pi_over_two
-----------------+------------------+--------+-----------------
 1.5707963267949 | -1.5707963267949 |      0 | 1.5707963267949
(1 row)

cbrt()

cbrt(
x
 )

The cbrt() function accepts a single double precision argument x , and returns its cubed root as a double precision value. This function is effectively the inverse of raising a number by the power of 3 with the pow function.

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)

ceil()

ceil(
x
 )

The ceil() function accepts a value x of any numeric data type (numeric, bigint, smallint, real, or double precision), and rounds it up to the smallest whole integer greater than the passed value. If a whole integer is passed, ceil() has no effect.

Example

testdb=# 
SELECT ceil(1.0), ceil(1.1), ceil(1.5);

 ceil | ceil | ceil
------+------+------
    1 |    2 |    2
(1 row)

cos()

cos(
x
 )

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

Example

testdb=# 
SELECT cos(pi()) AS cos_pi,

testdb-# 
       cos(0) AS cos_zero;

 cos_pi | cos_zero
--------+----------
     -1 |        1
(1 row)

cot()

cot(
x
 )

The cot() function accepts a single double precision value x representing an angle (in radians), and returns its cotangent as a double precision value. The argument passed must be non-zero.

Example

testdb=# 
SELECT cot(1), cot(-1);

        cot        |        cot
-------------------+--------------------
 0.642092615934331 | -0.642092615934331
(1 row)

degrees()

degrees(
r
 )

The degrees() function accepts a double precision argument r representing a value expressed in radians, and converts them into degrees. The result is returned as a value of type double precision. degrees() is effectively the inverse 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)

exp()

exp(
x
 )

The exp() function accepts a single double precision or numeric argument x , and returns the special e constant, raised to the power passed to the function.

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)

floor()

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

ln(
x
 )

ln() accepts a single numeric or double precision value x and returns the natural logarithm of that argument. This is effectively the inverse of the exp() function, as well as the equivalent of selecting the log() of the argument, with base e .

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)

log()

log(
x
 )
log(
b
 , 
x
 )

The log() function accepts either one or two arguments of type numeric. If one argument is specified, log( x  ) returns the base 10 logarithm of 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)

mod()

mod(
x
 , 
y
 )

The mod function accepts two numeric arguments, x and y , which may be of type numeric, integer, smallint, or bigint. The value returned is the remainder, or modulus, left over from dividing x / y , 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)

pi()

pi()

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

Example

testdb=# 
SELECT pi() AS "the pi constant";

 the pi constant
------------------
 3.14159265358979
(1 row)

pow()

pow(
x
 , 
y
 )

The pow() function accepts two arguments, x and y , of type numeric or double precision. It returns the value of x raised to the exponent of y . The result is returned as a value of the same data type as the passed arguments. Note that the arguments must contain decimal points.

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)

radians()

radians(
d
 )

The radians() function accepts a single argument d of type double precision, specifying degrees. The function returns the equivalent number of radians, as a value of type double precision. radians() is effectively the inverse of the degrees() function.

Example

testdb=# 
SELECT radians(180) AS half_circle,

testdb-# 
       radians(360) AS full_circle;

   half_circle    |   full_circle
------------------+------------------
 3.14159265358979 | 6.28318530717959
(1 row)

random()

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=# 
SELECT random() AS natural_random,

testdb-# 
       round(random() * 9) + 1 AS one_through_ten,

testdb-# 
       trunc(random() * 99) + 1 AS one_through_one_hundred;

  natural_random   | one_through_ten | one_through_one_hundred
-------------------+-----------------+-------------------------
 0.478887704424042 |               2 |                      37
(1 row)

round()

round(
x
 )
round(
x
 ,
s
 )

The round() function may accept either one or two arguments. The first argument, x , of type numeric or double precision, is the number that you intend to round. The second optional argument, s , of type integer, specifies how many digits past the decimal to round from. The result is returned as a value of the same type as the first argument.

If there are more digits specified by s than by x , the extra digits will be padded with zeroes.

Example

testdb=# 
SELECT round(1.0) AS one,

testdb-# 
       round(1.1) AS "one point one",

testdb-# 
       round(1.5) AS "one point five",

testdb-# 
       round(1.8) AS "one point eight";

 one | one point one | one point five | one point eight
-----+---------------+----------------+-----------------
   1 |             1 |              2 |               2
(1 row)

testdb=# 
SELECT round(1.4949, 1) AS one_digit_scale,

testdb-# 
       round(1.4949, 3) AS three_digit_scale,

testdb-# 
       round(1.4949, 10) AS ten_digit_scale,

testdb-# 
       round(1.4949, 0) AS rounded;

 one_digit_scale | three_digit_scale | ten_digit_scale | rounded
-----------------+-------------------+-----------------+---------
             1.5 |             1.495 |    1.4949000000 |       1
(1 row)

sin()

sin(
x
 )

The sin() function accepts a single argument x of type double precision, representing an angle described in radians. The sine of the argument is returned as a value of type double precision.

Example

testdb=# 
SELECT sin(pi() / 4) AS quarter_pi,

testdb-# 
       sin(pi() / 2) AS half_pi;

    quarter_pi     | half_pi
-------------------+---------
 0.707106781186547 |       1
(1 row)

sqrt()

sqrt(
x
 )

The sqrt() function accepts a single argument x , of either type double precision, or numeric, and returns its square root. The returned value is of the same data type passed to it. The sqrt function is effectively the inverse of the pow() function, used with a power of 2.

Example

testdb=# 
SELECT sqrt(2.0), sqrt(4.0),

testdb-# 
       sqrt(pow(2.0, 2)) AS inverse_example;

      sqrt       | sqrt | inverse_example
-----------------+------+-----------------
 1.4142135623731 |    2 |               2
(1 row)

tan()

tan(
x
 )

The tan() function accepts a single argument x , of type double precision, representing an angle described in radians. The tangent of the argument is returned as a value of type double precision.

Example

testdb=# 
SELECT tan(pi() / 8),

testdb-# 
       tan(0);

        tan        | tan
-------------------+-----
 0.414213562373095 |   0
(1 row)

trunc()

trunc(
x
 )
trunc(
x
 , 
s
 )

The trunc() function accepts one or two arguments, x and s . The x argument may be of the numeric or double precision type, and represents the value to be truncated. The s argument may be of the integer type.

If specified, s dictates the number of digits allowed to the right of the decimal before truncation. If unspecified, any digits past the decimal in x are truncated. If more digits are specified by s than there are represented by x , the extra digits will be padded with zeroes.

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

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