Mathematical Operators

+

Addition

Add 3 to 2. 5 is returned.

1
2
3
4
5

SELECT 2+3 AS RESULT;
 result 
--------
      5
(1 row)

-

Subtraction

Subtract 3 from 2. -1 is returned.

1
2
3
4
5

SELECT 2-3 AS RESULT;
 result 
--------
     -1
(1 row)

*

Multiplication

Multiply 2 by 3. 6 is returned.

1
2
3
4
5

SELECT 2*3 AS RESULT;
 result 
--------
      6
(1 row)

/

Division (The result is not rounded.)

Divide 4 by 2. 2 is returned.

1
2
3
4
5

SELECT 4/2 AS RESULT;
 result 
--------
      2
(1 row)

Divide 4 by 3. 1.3333333333333333 is returned.

1
2
3
4
5

SELECT 4/3 AS RESULT;
      result      
-------------------
 1.3333333333333333
(1 row)

+/-

Positive/Negative

Perform operations with negative numbers.

1
2
3
4
5

SELECT -2-5 AS RESULT;
 result 
--------
     -2
(1 row)

%

Modulo (remainder)

Calculate the remainder of two numbers divided by each other. 5%4=1 (remainder when dividing 5 by 4)

1
2
3
4
5

SELECT 5%4 AS RESULT;
 result 
--------
      1
(1 row)

@

Absolute value

Obtain the absolute value of -5.0.

1
2
3
4
5

SELECT @ -5.0 AS RESULT;
 result 
--------
    5.0
(1 row)

^

Power (exponentiation)

In MySQL-compatible mode, this operator means exclusive OR. For details, see the operator # in Bit String Functions and Operators.

Calculate 2 raised to the power of 3. 8 is returned.

1
2
3
4
5

SELECT 2.0^3.0 AS RESULT;
       result       
--------------------
 8.0000000000000000
(1 row)

|/

Square root

Calculate the square root of 25. 5 is returned.

1
2
3
4
5

SELECT |/ 25.0 AS RESULT;
 result 
--------
      5
(1 row)

||/

Cubic root

Calculate the cubic root of 27. 3 is returned.

1
2
3
4
5

SELECT ||/ 27.0 AS RESULT;
 result 
--------
      3
(1 row)

!

Factorial

Calculate the factorial of 5. 120 is returned.

1
2
3
4
5

SELECT 5! AS RESULT;
 result 
--------
    120
(1 row)

!!

Factorial (prefix operator)

Calculate the factorial of 5. 120 is returned.

1
2
3
4
5

SELECT !!5 AS RESULT;
 result 
--------
    120
(1 row)

&

Binary AND

Perform a bitwise AND on 91 and 15. 11 is returned.

1
2
3
4
5

SELECT 91&15  AS RESULT;
 result 
--------
     11
(1 row)

|

Binary OR

Perform a bitwise OR on 32 and 3. 35 is returned.

1
2
3
4
5

SELECT 32|3  AS RESULT;
 result 
--------
     35
(1 row)

#

Binary XOR

Perform a bitwise XOR on 17 and 5. 20 is returned.

1
2
3
4
5

SELECT 17#5  AS RESULT;
 result 
--------
     20
(1 row)

~

Binary NOT

Perform a bitwise NOT on 1. -2 is returned.

1
2
3
4
5

SELECT ~1 AS RESULT;
 result 
--------
     -2
(1 row)

<<

Binary shift left

Shift the binary representation of 1 (for example, 00000001) to the left by four digits and then convert it to a decimal number. 16 is returned.

1
2
3
4
5

SELECT 1<<4 AS RESULT;
 result 
--------
     16
(1 row)

>>

Binary shift right

Shift the binary representation of 8 (for example, 00001000) to the right by two digits and then convert it to a decimal number. 2 is returned.

1
2
3
4
5

SELECT 8>>2 AS RESULT;
 result 
--------
      2
(1 row)