PostgreSQL Mathematical Functions and Operators with examples

PosgreSQL provides many mathematical functions and operators for different types

Like any other databases postgresql also support all type mathematical functions and operators like addition, subtraction, division, multiplication, trigonometric functions, logarithmic functions, random function etc.

Mathematical Operators

OperatorExample
Addition (+)select 3+2; → 5
Subtraction (-)select 3-2; → 1
select 2-3 ; → -1
Division (/)select 3.0/2.0; → 1.5000000000000000
select 3/2; → 1
Division by zero is prohibited
Multiplication(*)select 3.0*2.0; → 6.0
select 3*2; → 6
Unary plus (+)select +1; → 1
Negation (-)select -1; → -1
Modulo (%) select 4%3 → 1
Exponentiation(^)select 2^3; → 8
select 2^2^3; → 64
select 2^(2^3); → 256
Square Root (|/)select |/9; → 3
Cube Root (||/)select ||/8; → 2
Absolute Value (@)select @(-8); → 8
select @(8); → 8
Bitwise AND (&)select 3 & 5; → 1
Bitwise OR (|)select 3 | 5; → 7
Bitwise exclusive OR (#)select 3 # 5; → 6
Bitwise NOT (~)select ~1; → -2
Bitwise shift left (<<)select 1 << 4; v 16
Bitwise shift right (>>)select 8 >> 2; → 2

Basic Mathematical Functions

Absolute Value (abs)select abs(-7.5); → 7.5
Cube Root (cbrt)select cbrt(8); → 2
Ceil (ceil)select ceil(7.5); → 8
select ceil(-7.5); → -7
Ceiling (ceiling)select ceiling(7.5); → 8
select ceiling(-7.5); v -7
Degrees (degrees)
Converts radians to degrees
select degrees(1); → 57.29577951308232
select degrees(pi()); v 180
Divisior (div)select div(10,3); → 3
select div(10,2); → 5
Exponential (exp)
values of e to the power
select exp(1); → 2.718281828459045
select exp(2); → 7.38905609893065
Factorial (factorial)select factorial(3); → 6
Floor (floor)select floor(7.5); → 7
select floor(-7.5); → -8
Greatest Common Divisor (gcd)select gcd(12,9); → 3
Least Common Multiple (lcm)select lcm(12,9); → 36
Natural Logarithm (ln)select ln(2.0); → 0.6931471805599453
Base 10 Logarithm (log and log10)select log(100); → 2
Logarithm of x to base b ( log(b,x) )select log(2,64); → 6
Minimum Scale (min_scale)select min_scale(8.4100); → 2
Mod or Remainder (mod)select mod(10,3); → 1
PI (pi)select pi(); → 3.141592653589793
Power (power)select power(2,3); → 8
Radians (radian)select radians(180); → 3.141592653589793 –> pi()
Round (round)select round(42.4); → 42
select round(42.6); → 43
select round(42.635,2); → 42.64
Scale (scale)select scale(8.4100); → 4
Sign (sign)select sign(-2); → -1
Square Root (sqrt)select sqrt(9); → 3
TRIM SCALE (trim_scale)select trim_scale(8.4100); → 4.1
Truncate (trunc)select trunc(42.63); → 42
select trunc(42.63,1); → 42.6
select trunc(42.63,2); → 42.63

Trigonometric and Hyperbolic Functions

acos (in radians)select acos(1); → 0
acosd (in degrees)select acos(0.5); → 60
asin (in radians)select asin(1); → 90
asind (in degrees)select asind(0.5); → 30
atan (in radians)select atan(0); → 90
atand (in degrees)select atand(0); → 0
atan2 (in radians)select atan2(1,0); → 1.5707963267948966
atan2d (in degrees)select atan2(1,0); → 90
cos (in radians)select cos(0); → 1
cosd (in degrees)select cosd(60); → 0.5
cot (in radians)select cot(90); → 0
cotd (in degrees)select cotd(45); → 1
sin (in radians)select sin(90); → 1
sind (in degrees)select sind(0.5); → 30
tan (in radians)select tan(0); → 0
tand (in degrees)select tand(45); → 1
sinhselect sinh(1); → 1.1752011936438014
coshselect cosh(0); → 1
tanhselect tanh(0); → 0
asinhselect asinh(1); → 0.881373587019543
acoshselect acosh(1); → 0
atanhselect atanh(0.5); → 0.5493061443340548

Random Functions

Randomselect random(); → 0.6242074452718818 (changes in each execution)
Setseedselect setseed(1);
select setseed(0);
select setseed(-1);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: