Mathematical functions ​

Mathematical functions cover roots, trigonometry, exact integer operations, numeric scales and bucket assignment. Exact Decimal results and approximate REAL results are stated per function.

Check a function's domain before using it with negative, zero or nonfinite values. A mathematical domain error is distinct from SQL NULL input and numeric overflow.

Example: Exact integer calculations ​

SELECT gcd(24, 36), lcm(4, 6), factorial(5)
FORMAT csv;

12,12,120

cbrt ​

Compute a real cube root, including negative operands.

Syntax ​

cbrt(value)

Arguments ​

Numeric input; native REAL result; NaN maps to SQL NULL.

Returned value ​

REAL; SQL NULL propagates.

Example ​

SELECT cbrt(-64) FORMAT csv;

-4.0

cot ​

Compute cotangent for a finite angle in radians.

Syntax ​

cot(angle)

Arguments ​

Numeric value or whole numeric text. Nonfinite input raises INVALID_ARGUMENT; Zero and floating poles return signed infinity; NaN becomes SQL NULL. SQL NULL propagates.

Returned value ​

Approximate REAL cotangent, including signed infinity at poles.

Example ​

SELECT round(cot(0.5),6) FORMAT csv;

1.830488

div ​

Compute an exact quotient truncated towards zero.

Syntax ​

div(numerator,denominator)

Arguments ​

Whole numeric inputs, including native exact integers/decimals; zero divisor errors.

Returned value ​

Exact native Decimal128/Decimal256 with scale 0, up to 76 digits; SQL NULL propagates.

Example ​

SELECT CAST(div(-9,4) AS TEXT) FORMAT csv;

"-2"

factorial ​

Compute a nonnegative integer factorial exactly within native decimal precision.

Syntax ​

factorial(integer)

Arguments ​

Signed integer from zero through 56; negative values raise INVALID_ARGUMENT and larger values raise NUMERIC_OVERFLOW. SQL NULL propagates.

Returned value ​

Native exact Decimal128/Decimal256; 0! and 1! are one.

Example ​

SELECT factorial(5) FORMAT csv;

120

gcd ​

Compute an exact nonnegative greatest common divisor.

Syntax ​

gcd(a,b)

Arguments ​

Aligns finite decimal coefficients before Euclid; supports fractional operands and negative values. Both zero gives zero. Numeric inputs use existing 1024-digit/exponent limits; nonfinite/nonnumeric inputs error.

Returned value ​

Native exact Decimal retaining maximum input fractional scale; SQL NULL propagates.

Example ​

SELECT CAST(gcd(1071,462) AS TEXT) FORMAT csv;

"21"

lcm ​

Compute an exact nonnegative least common multiple.

Syntax ​

lcm(a,b)

Arguments ​

Aligns finite decimal coefficients; divides before multiplication; either zero gives zero. Native precision/scale overflow errors. Numeric inputs use existing 1024-digit/exponent limits; nonfinite/nonnumeric inputs error.

Returned value ​

Native exact Decimal retaining maximum input fractional scale; SQL NULL propagates.

Example ​

SELECT CAST(lcm(1071,462) AS TEXT) FORMAT csv;

"23562"

min_scale ​

Report the minimum fractional scale needed for an exact number.

Syntax ​

min_scale(value)

Arguments ​

Ignores trailing fractional zeroes; zero and integer-valued numbers give zero. Numeric inputs use existing 1024-digit/exponent limits; nonfinite/nonnumeric inputs error.

Returned value ​

INTEGER minimum scale, or SQL NULL.

Example ​

SELECT min_scale('8.4100') FORMAT csv;

2

pg_size_bytes ​

Parse a human-readable byte size.

Syntax ​

pg_size_bytes(text)

Arguments ​

At most 512 bytes. Decimal/scientific number plus optional case-insensitive bytes/B/kB/MB/GB/TB/PB. Units use powers of 1024. Round half away from zero; signed 64-bit overflow errors. SQL NULL propagates.

Returned value ​

INTEGER bytes.

Example ​

SELECT pg_size_bytes('1.5 MB') FORMAT csv;

1572864

pg_size_pretty ​

Format an exact byte count with binary size units.

Syntax ​

pg_size_pretty(bytes)

Arguments ​

Exact bounded numeric input. Uses PostgreSQL 10 KiB/half-unit thresholds and truncation before half rounding; native precision/scale limits apply. SQL NULL propagates.

Returned value ​

TEXT size with bytes/kB/MB/GB/TB/PB.

Example ​

SELECT pg_size_pretty(1048576) FORMAT csv;

"1024 kB"

random_normal ​

Draw an approximate normally distributed random value.

Syntax ​

random_normal([mean[,stddev]])

Arguments ​

Defaults: mean zero and stddev one. Calls use connection-local random state and are nondeterministic. IEEE nonfinite arithmetic applies and native NaN becomes SQL NULL. SQL NULL arguments propagate.

Returned value ​

Approximate REAL normal sample; no cryptographic or seeded PostgreSQL sequence compatibility.

Example ​

SELECT random_normal(0,1) IS NOT NULL FORMAT csv;

1

scale ​

Report the retained fractional decimal scale.

Syntax ​

scale(value)

Arguments ​

Includes trailing zeroes; native Decimal declarations and textual numeric inputs retain their scale. Numeric inputs use existing 1024-digit/exponent limits; nonfinite/nonnumeric inputs error.

Returned value ​

INTEGER scale, or SQL NULL.

Example ​

SELECT scale('8.4100') FORMAT csv;

4

to_bin ​

Format an integer in binary.

Syntax ​

to_bin(integer)

Arguments ​

Native exact integers through 256 bits. Negative values use the declared integer width, or runtime width when no declaration is bound. Ordinary INTEGER inputs use 64 bits. SQL NULL propagates.

Returned value ​

TEXT two's-complement binary representation.

Example ​

SELECT to_bin(10) FORMAT csv;

"1010"

to_oct ​

Format an integer in octal.

Syntax ​

to_oct(integer)

Arguments ​

Native exact integers through 256 bits. Negative values use the declared integer width, or runtime width when no declaration is bound. Ordinary INTEGER inputs use 64 bits. SQL NULL propagates.

Returned value ​

TEXT two's-complement octal representation.

Example ​

SELECT to_oct(10) FORMAT csv;

"12"

trim_scale ​

Remove trailing fractional decimal zeroes.

Syntax ​

trim_scale(value)

Arguments ​

Returns native exact Decimal128/Decimal256; precision/scale remain bounded to 76 digits. Numeric inputs use existing 1024-digit/exponent limits; nonfinite/nonnumeric inputs error.

Returned value ​

Native exact Decimal, or SQL NULL.

Example ​

SELECT CAST(trim_scale('8.4100') AS TEXT) FORMAT csv;

"8.41"

width_bucket ​

Locate a histogram bucket with exact numeric boundaries or ordered native thresholds.

Syntax ​

width_bucket(value,low,high,count) or width_bucket(value,thresholds)

Arguments ​

Positive Int64 count and distinct finite numeric endpoints; descending ranges supported. Threshold array must be sorted ascending and contain no NULLs; native comparison/collation apply.

Returned value ​

Int64 bucket: 0 below the range, count+1 at/beyond high; threshold form returns matched lower-bound count; SQL NULL propagates.

Example ​

SELECT width_bucket(5.35,0.024,10.06,5) FORMAT csv;

3

Core mathematical functions ​

Unary functions include abs, sign, ceil/ceiling, floor, trunc, exp, ln, log10, log2, sqrt, degrees, radians, sin, cos, tan, asin, acos, atan, sinh, cosh, tanh, asinh, acosh and atanh. pow/power and atan2 take two arguments; pi takes none. log(value) uses base 10; log(base,value) accepts a positive fractional base other than 1. Domain errors that produce NaN return NULL.

SELECT sqrt(9),pow(2,3),log(2,8),sign(-4) FORMAT csv;

3.0,8.0,3.0,-1