Help Center/ DataArts Fabric/ Developer Guide/ SQL Syntax Reference/ Functions and Operators/ Numeric Operation Functions and Operators/ Numeric Operators
Updated on 2025-08-25 GMT+08:00
View PDF
Share

Numeric Operators

+

Description: Addition

Example:

1
2
3
4
5
 
postgres=#SELECT 2+3 AS RESULT;
 result 
--------
      5
(1 row)

-

Description: Subtraction

Example:

1
2
3
4
5
 
postgres=#SELECT 2-3 AS RESULT;
 result 
--------
     -1
(1 row)

*

Description: Multiplication

Example:

1
2
3
4
5
 
postgres=#SELECT 2*3 AS RESULT;
 result 
--------
      6
(1 row)

/

Description: Division (Division operator does not round off.)

When the divisor is 0, the result is NULL (compatible with Hive behavior).

Example:

1
2
3
4
5
 
postgres=#SELECT 4/2 AS RESULT;
 result 
--------
      2
(1 row)

1
2
3
4
5
 
postgres=#SELECT 4/3 AS RESULT;
      result      
------------------
 1.33333333333333
(1 row)

+/-

Description: Positive/Negative

Example:

1
2
3
4
5
 
postgres=#SELECT -2 AS RESULT;
 result 
--------
     -2
(1 row)

%

Description: Modulus (Remainder)

Note: When the modulus is 0, the result is NULL (compatible with Hive behavior).

Example:

1
2
3
4
5
 
postgres=#SELECT 5%4 AS RESULT;
 result 
--------
      1
(1 row)

@

Description: Absolute value.

Example:

1
2
3
4
5
 
postgres=#SELECT @ -5.0 AS RESULT;
 result 
--------
    5.0
(1 row)

^

Description: Exponentiation (Power operation)

In MySQL compatibility mode, it functions as an XOR.

Example:

1
2
3
4
5
 
postgres=#SELECT 2.0^3.0 AS RESULT;
       result       
--------------------
 8.0000000000000000
(1 row)

|/

Description: Square root.

Example:

1
2
3
4
5
 
postgres=#SELECT |/ 25.0 AS RESULT;
 result 
--------
      5
(1 row)

||/

Description: Cube root.

Example:

1
2
3
4
5
 
postgres=#SELECT ||/ 27.0 AS RESULT;
 result 
--------
      3
(1 row)

!

Description: Factorial

Example:

1
2
3
4
5
 
postgres=#SELECT 5! AS RESULT;
 result 
--------
    120
(1 row)

!!

Description: Factorial (prefix operator)

Example:

1
2
3
4
5
 
postgres=#SELECT !!5 AS RESULT;
 result 
--------
    120
(1 row)

&

Description: Binary AND

Example:

1
2
3
4
5
 
postgres=#SELECT 91&15  AS RESULT;
 result 
--------
     11
(1 row)

|

Description: Binary OR

Example:

1
2
3
4
5
 
postgres=#SELECT 32|3  AS RESULT;
 result 
--------
     35
(1 row)

#

Description: Binary XOR

Example:

1
2
3
4
5
 
postgres=#SELECT 17#5  AS RESULT;
 result 
--------
     20
(1 row)

~

Description: Binary NOT

Example:

1
2
3
4
5
 
postgres=#SELECT ~1 AS RESULT;
 result 
--------
     -2
(1 row)

<<

Description: Binary left shift

Example:

1
2
3
4
5
 
postgres=#SELECT 1<<4 AS RESULT;
 result 
--------
     16
(1 row)

>>

Description: Binary right shift

Example:

1
2
3
4
5
 
postgres=#SELECT 8>>2 AS RESULT;
 result 
--------
      2
(1 row)