PostgreSQL Math Functions – Complete List with Examples

Integer division truncates (7 / 2 is 3); use a numeric value (7 / 2.0) for decimals. numeric is exact, which makes it right for money; double precision is faster but approximate.

Name	Description	Since
`ABS()`	Return the absolute value of a number, so abs(-7) is 7. The result has the same type as the input.
`ROUND()`	Round a number to a given number of decimal places (0 by default). numeric rounds halves away from zero.
`CEIL()`	Round a number up to the nearest integer: ceil(2.1) is 3 and ceil(-2.1) is -2. ceiling() is a synonym.
`FLOOR()`	Round a number down to the nearest integer: floor(2.9) is 2 and floor(-2.1) is -3.
`TRUNC()`	Truncate a number toward zero, optionally keeping some decimal places: trunc(2.789, 2) is 2.78.
`MOD()`	Return the remainder of dividing one number by another; x % y does the same. The sign follows the dividend.
`DIV()`	Return the integer quotient of y / x, truncated toward zero, for numeric values. Integer / integer already truncates.
`POWER()`	Raise a number to a power, so power(2, 10) is 1024. pow() is another name for the same function.
`SQRT()`	Return the square root of a number, so sqrt(16) is 4. Negative input raises an error; cbrt() gives the cube root.
`LN()`	Return the natural logarithm (base e) of a number. exp() is the inverse.
`LOG()`	Return the base-10 logarithm of a number, or the logarithm in another base with log(b, x). log10() is a synonym.
`RANDOM()`	Return a random number from 0 up to 1, or since PostgreSQL 17 a random integer or numeric in a range.
`GCD()`	Return the greatest common divisor of two integers, the largest number that divides both. lcm() gives the least common multiple.	13
`WIDTH_BUCKET()`	Return which of n equal-width buckets a value falls into between a low and high bound, for building histograms.

Name

Description

Since

ABS()

Return the absolute value of a number, so abs(-7) is 7. The result has the same type as the input.

ROUND()

Round a number to a given number of decimal places (0 by default). numeric rounds halves away from zero.

CEIL()

Round a number up to the nearest integer: ceil(2.1) is 3 and ceil(-2.1) is -2. ceiling() is a synonym.

FLOOR()

Round a number down to the nearest integer: floor(2.9) is 2 and floor(-2.1) is -3.

TRUNC()

Truncate a number toward zero, optionally keeping some decimal places: trunc(2.789, 2) is 2.78.

MOD()

Return the remainder of dividing one number by another; x % y does the same. The sign follows the dividend.

DIV()

Return the integer quotient of y / x, truncated toward zero, for numeric values. Integer / integer already truncates.

POWER()

Raise a number to a power, so power(2, 10) is 1024. pow() is another name for the same function.

SQRT()

Return the square root of a number, so sqrt(16) is 4. Negative input raises an error; cbrt() gives the cube root.

LN()

Return the natural logarithm (base e) of a number. exp() is the inverse.

LOG()

Return the base-10 logarithm of a number, or the logarithm in another base with log(b, x). log10() is a synonym.

RANDOM()

Return a random number from 0 up to 1, or since PostgreSQL 17 a random integer or numeric in a range.

GCD()

Return the greatest common divisor of two integers, the largest number that divides both. lcm() gives the least common multiple.

WIDTH_BUCKET()

Return which of n equal-width buckets a value falls into between a low and high bound, for building histograms.