14th February 2019: PostgreSQL 11.2, 10.7, 9.6.12, 9.5.16, and 9.4.21 Released!

Development Versions:
devel

Mathematical operators are provided for many PostgreSQL types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections.

Table 9.4 shows the available mathematical operators.

**Table 9.4. Mathematical Operators**

Operator | Description | Example | Result |
---|---|---|---|

`+` |
addition | `2 + 3` |
`5` |

`-` |
subtraction | `2 - 3` |
`-1` |

`*` |
multiplication | `2 * 3` |
`6` |

`/` |
division (integer division truncates the result) | `4 / 2` |
`2` |

`%` |
modulo (remainder) | `5 % 4` |
`1` |

`^` |
exponentiation (associates left to right) | `2.0 ^ 3.0` |
`8` |

`|/` |
square root | `|/ 25.0` |
`5` |

`||/` |
cube root | `||/ 27.0` |
`3` |

`!` |
factorial | `5 !` |
`120` |

`!!` |
factorial (prefix operator) | `!! 5` |
`120` |

`@` |
absolute value | `@ -5.0` |
`5` |

`&` |
bitwise AND | `91 & 15` |
`11` |

`|` |
bitwise OR | `32 | 3` |
`35` |

`#` |
bitwise XOR | `17 # 5` |
`20` |

`~` |
bitwise NOT | `~1` |
`-2` |

`<<` |
bitwise shift left | `1 << 4` |
`16` |

`>>` |
bitwise shift right | `8 >> 2` |
`2` |

The bitwise operators work only on integral data types, whereas the others are available for all numeric data types. The bitwise operators are also available for the bit string types `bit`

and `bit varying`

, as shown in Table 9.13.

Table 9.5 shows the available mathematical functions. In the table, `dp`

indicates `double precision`

. Many of these functions are provided in multiple forms with different argument types. Except where noted, any given form of a function returns the same data type as its argument. The functions working with `double precision`

data are mostly implemented on top of the host system's C library; accuracy and behavior in boundary cases can therefore vary depending on the host system.

**Table 9.5. Mathematical Functions**

Table 9.6 shows functions for generating random numbers.

**Table 9.6. Random Functions**

The characteristics of the values returned by

depend on the system implementation. It is not suitable for cryptographic applications; see pgcrypto module for an alternative.`random()`

Finally, Table 9.7 shows the available trigonometric functions. All trigonometric functions take arguments and return values of type `double precision`

. Each of the trigonometric functions comes in two variants, one that measures angles in radians and one that measures angles in degrees.

**Table 9.7. Trigonometric Functions**

Another way to work with angles measured in degrees is to use the unit transformation functions

and `radians()`

shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids round-off error for special cases such as `degrees()`

`sind(30)`

.

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.