Help Center/ GaussDB/ M-Compatibility Developer Guide(Centralized)/ SQL Reference/ Functions and Operators/ Numeric Operation Functions and Arithmetic Operators

Updated on 2025-10-23 GMT+08:00

View PDF

Numeric Operation Functions and Arithmetic Operators

Arithmetic Operators

Arithmetic operators in M-compatible databases include +, -, *, /, %, - (negative operation), and DIV. For details, see Table 1.

Description: Performs the addition operation.

Example:

/* plus constant */
m_db=# SELECT 2+3;
 ?column? 
----------
        5
(1 row)

m_db=# SELECT +3;
 ?column? 
----------
        3
(1 row)

-
Description: Performs the subtraction operation or represents a negative number.

If both operands are integers and either of them is unsigned, the result is an unsigned integer. For subtraction, if the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is a signed number even if any operand is an unsigned number.

Example:
```
/* minus constant */
m_db=# SELECT 3-2;
 ?column? 
----------
        1
(1 row)

m_db=# SELECT -3;
 ?column? 
----------
       -3
(1 row)
```

Description: Performs the multiplication operation.

Example:

/* multiply constant */
m_db=# SELECT 2*3;
 ?column? 
----------
        6
(1 row)

m_db=# SELECT -2*3;
 ?column? 
----------
       -6
(1 row)

/
Description: Performs the division operation.

When / is used for division, the number of decimal places in the result is the number of decimal places in the first operand plus the value of div_precision_increment. The default value is 4. For example, the result of expression 8.85/0.093 has six decimal places (95.161290).

Example:
```
/* division constant */
m_db=# SELECT 4/2;
 ?column? 
----------
   2.0000
(1 row)

m_db=# SELECT 8.85/0.093;
 ?column?  
-----------
 95.161290
(1 row)
```

% or MOD

Description: Performs the modulo operation.

Example:

/* mod constant */
m_db=# SELECT 7%4;
 ?column? 
----------
        3
(1 row)

m_db=# SELECT 7 MOD 4;
 ?column?
----------
        3
(1 row)

DIV

Description: Performs the exact division operation.

Example:

/* DIV test */
m_db=# SELECT 2.1 DIV 1;
 ?column?
----------
        2
(1 row)

m_db=# SELECT 1 DIV 2;
 ?column?
----------
        0
(1 row)

Specification Constraints of Arithmetic Operators

Operators support operations between different data types. Before calculating results, they first promote data to appropriate types. See the table below for the operand promotion rules and result type specifications associated with operators.

**Table 1** Operand promotion rules for operators
Left and Right Input Parameter Type	Classification Type
TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, BIGINT, and DATE	BIGINT
TINYINT UNSIGNED, SMALLINT UNSIGNED, MEDIUMINT UNSIGNED, INT/INTEGER UNSIGNED, BIGINT UNSIGNED, BIT, and YEAR	BIGINT UNSIGNED
TIME, TIMESTAMP, and DATETIME	If the value does not contain millisecond, this parameter corresponds to BIGINT. If the value contains millisecond, this parameter corresponds to NUMERIC.
UNKNOWN, CHAR, VARCHAR, BINARY, VARBINARY, TINYTEXT TEXT, MEDIUMTEXT, and LONGTEXT TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, FLOAT, FLOAT4, and FLOAT8	FLOAT8
NUMERIC	NUMERIC
Use the default settings of other parameters.	BIGINT

**Table 2** Addition, multiplication, and modulo result types
Classification Type Combination (from Top to Bottom in Descending Order of Priority)	Final Result Type
FLOAT8 + any	FLOAT8
NUMERIC + any	NUMERIC
BIGINT UNSIGNED + any	BIGINT UNSIGNED
Others	BIGINT

**Table 3** Subtraction result types
Classification Type Combination (from Top to Bottom in Descending Order of Priority)	Final Result Type
FLOAT8 + any	FLOAT8
NUMERIC + any	NUMERIC
BIGINT UNSIGNED + any	BIGINT UNSIGNED or BIGINT (For GUC parameter SQL_MODE, if NO_UNSIGNED_SUBTRACTION is enabled, the result type is BIGINT. Otherwise, BIGINT UNSIGNED is returned.)
Others	BIGINT

**Table 4** Division result types
Classification Type Combination (from Top to Bottom in Descending Order of Priority)	Final Result Type
FLOAT8 + any	float8
Others	numeric

**Table 5** Negative result types
Input Parameter Type	Result Type
TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, BIGINT, TINYINT UNSIGNED, SMALLINT UNSIGNED, MEDIUMINT UNSIGNED, INT/INTEGER UNSIGNED, BIGINT UNSIGNED, BIT, and YEAR	BIGINT
UNKNOWN, CHAR, VARCHAR, BINARY, VARBINARY, TINYTEXT TEXT, MEDIUMTEXT, and LONGTEXT TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, DATE, DATETIME, TIMESTAMP, TIME, FLOAT, FLOAT4, and FLOAT8	FLOAT8
NUMERIC	NUMERIC
Use the default settings of other parameters.	FLOAT8

**Table 6** Exact division result types
Input Parameter Type	Result Type
TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, and BIGINT TINYINT UNSIGNED, SMALLINT UNSIGNED, MEDIUMINT UNSIGNED, INT/INTEGER UNSIGNED, BIGINT UNSIGNED, BIT, and YEAR	BIGINT (Convert the input parameter to the BIGINT type, perform the division operation, round the result, and return the BIGINT type.)
UNKNOWN, CHAR, VARCHAR, BINARY, VARBINARY, TINYTEXT TEXT, MEDIUMTEXT, and LONGTEXT TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, DATE, DATETIME, TIMESTAMP, TIME, FLOAT, FLOAT4, FLOAT8, and NUMERIC	BIGINT (Convert the input parameter to the DOUBLE type, perform the division operation, round the result, and return the BIGINT type.)

When the operand of an arithmetic operator is a function whose return value is of the datetime, timestamp, or time type, or a literal value of the datetime or time type, for example, timestamp '2000-01-01 00:00:00' or time '08:00:00', the content after the decimal point is ignored, that is, the millisecond information is ignored.

Numeric Operation Functions

ABS(expr)
Description: Returns the absolute value of a numeric expression.

Parameter: expr, which is a numeric expression.

Return type: BIGINT UNSIGNED, BIGINT, DOUBLE, or DECIMAL. The data type is the same as that of the numeric expression.

Example:
1 2 3 4 5

m_db=# SELECT ABS(-1.3); abs ----- 1.3 (1 row)

ACOS(X)
Description: Returns the arc cosine value of X, that is, the value whose cosine is X.

If the value of parameter X is not in the range [-1, 1], the function returns NULL.

Return type: DOUBLE.

Example:
```
m_db=# SELECT ACOS(0);
        acos        
--------------------
 1.5707963267948966
(1 row)
```

ASIN(X)
Description: Returns the arc sine value of X, that is, the value whose sine is X.

If the value of parameter X is not in the range [-1, 1], the function returns NULL.

Return type: DOUBLE.

Example:
```
m_db=# SELECT ASIN(0);
 asin 
------
    0
(1 row)
```

ATAN(X)
Description: Returns the arc tangent value of X, that is, the value whose tangent value is X.

Return type: DOUBLE.

Example:
```
m_db=# SELECT ATAN(-10);
        atan         
---------------------
 -1.4711276743037347
(1 row)
```
ATAN(Y, X)
Description: Returns the arc tangent value of X and Y. It is similar to calculating the arc tangent value of Y/X. The symbols of the two parameters are used to determine the quadrant where the result is located.

Return type: DOUBLE.

Example:
```
m_db=# SELECT ATAN(-2, 2);
        atan         
---------------------
 -0.7853981633974483
(1 row)
```

ATAN2(Y, X)

Description: This function has the same function and usage as ATAN(Y,X).

Example:

m_db=# SELECT ATAN2(-2, 2);
        atan2        
---------------------
 -0.7853981633974483
(1 row)

CEIL(INT X)
Description: Alias of the CEILING function, which returns the smallest integer not less than X. If the value of X is NULL, this function returns NULL.

Return type: BIGINT, BIGINT UNSIGNED, DOUBLE, or DECIMAL.
- When m_format_dev_version is set to 's2' or later and m_format_behavior_compat_options contains 'enable_conflict_funcs', this function is implemented in M-compatible databases, that is, the behavior described in this section. Other operations are the same as those of the ceil function described in "SQL Reference > Functions and Operators > Arithmetic Functions and Operators" in Developer Guide.
- In a non-M-compatible database implementation, if the input parameter type is unique to M-compatible databases (for example, BIGINT UNSIGNED), using this function may result in an exception.
Example:
```
m_db=# SELECT CEIL(-5.5);
 ceil 
------
   -5
(1 row)
```
CEILING(X)
Description: Returns the smallest integer greater than or equal to X.

Return type: BIGINT, DOUBLE, or DECIMAL.

Example:
```
m_db=# SELECT CEILING(-5.5);
 ceiling 
---------
      -5
(1 row)
```

CONV(TEXT N, INT from_base, INT to_base)
Description: Converts numbers between different number bases, for example, from decimal to binary. The input and output number bases range from 2 to 36, inclusive. If from_base is a negative number, N is input as a signed number. Otherwise, N is input as an unsigned number. If to_base is a negative number, the output is specified as a signed number. Otherwise, the output is specified as an unsigned number.

Return type: TEXT.

Example:
```
m_db=# select CONV(10010011, 2, 4);
 conv 
------
 2103
(1 row)

m_db=# select CONV('111ypf9z', 36, 2);
                 conv                  
---------------------------------------
 1001011000111101100010110111110010111
(1 row)
```
COS(X)
Description: Returns the cosine value of the specified radian X.

Return type: DOUBLE.

Example:
```
m_db=# SELECT COS(0);
 cos 
-----
   1
(1 row)
```

COT(X)
Description: Returns the cotangent value of the specified radian X.

Return type: DOUBLE.

Example:
```
m_db=# SELECT COT(1);
        cot         
--------------------
 0.6420926159343306
(1 row)
```
CRC32(TEXT str)
Description: Calculates the cyclic redundancy. If str is NULL, NULL is returned. Otherwise, a value of the INT UNSIGNED type is returned after the redundancy is calculated.

Return type: INT UNSIGNED.

Example:
```
m_db=# SELECT CRC32('a');
   crc32    
------------
 3904355907
(1 row)
```
DEGREES(X)
Description: Converts parameter X from a radian to a degree and returns the value.

Return type: DOUBLE.

Example:
```
m_db=# SELECT DEGREES(PI());
 degrees 
---------
     180
(1 row)
```

EXP(X)
Description: Returns the X^th power of the natural base e (2.7182818...).

Return type: DOUBLE.
```
m_db=# SELECT EXP(2);
       exp        
------------------
 7.38905609893065
(1 row)
```

FLOOR(X)
Description: Returns the largest integer less than or equal to X.

Return type: BIGINT UNSIGNED, BIGINT, DOUBLE, or DECIMAL.

Example:
```
m_db=# SELECT FLOOR(5.5);
 floor 
-------
     5
(1 row)
```

LN(X)
Description: Returns the natural logarithm of X, that is, the logarithm of X to base e (2.7182818...).

Return type: DOUBLE.

Example:
```
m_db=# SELECT LN(2);
         ln         
--------------------
 0.6931471805599453
(1 row)
```

LOG([B, ]X)

Description: Returns the logarithm of X to base B. When parameter B is set to the default value, e (2.7182818...) is used as the base.

Return type: DOUBLE.

Example:

m_db=# SELECT LOG(2);
        log         
--------------------
 0.6931471805599453
(1 row)

m_db=# SELECT LOG(10, 2);
         log         
---------------------
 0.30102999566398114
(1 row)

LOG10(X)

Description: Returns the logarithm of X to base 10.

Return type: DOUBLE.

m_db=# SELECT LOG10(2);
       log10        
--------------------
 0.3010299956639812
(1 row)

LOG2(X)
Description: Returns the logarithm of X to base 2.

Return type: DOUBLE.
```
m_db=# SELECT LOG2(2);
 log2 
------
    1
(1 row)
```

MOD(X, Y)

Description: Performs the modulo operation.

Return type: INT, DOUBLE, or DECIMAL.

Example:

m_db=# SELECT MOD(5, 3);
 ?column? 
----------
        2
(1 row)

m_db=# SELECT MOD(7, 4);
 ?column?
----------
        3
(1 row)

PI()

Description: Returns the value of π (circumference).

Return type: DOUBLE.

Example:

m_db=# SELECT PI();
        pi         
-------------------
 3.141592653589793
(1 row)

POW(X, Y)
Description: Returns X raised to the power of Y.

Return type: DOUBLE.

Example:
```
m_db=# SELECT POW(2, 3);
 pow 
-----
   8
(1 row)
```

POWER(X, Y)
Description: Returns X raised to the power of Y. This function is the alias of the POW(X, Y) function.

Return type: DOUBLE.

Example:
```
m_db=# SELECT POWER(2, 3);
 power 
-------
     8
(1 row)
```

RADIANS(X)
Description: Converts parameter X from a degree to a radian and returns the result.

Return type: DOUBLE.

Example:
```
m_db=# SELECT RADIANS(180);
      radians      
-------------------
 3.141592653589793
(1 row)
```
RAND([seed])
Description: Returns the random floating-point number. The value range is [0, 1).

Parameter: seed, which is a random number seed. If the same seed value is specified, the function generates the same random number.

Return type: DOUBLE.

Example:
```
m_db=# SELECT RAND();
        rand        
--------------------
 0.5320404642261565
(1 row)

m_db=# SELECT RAND(1);
        rand         
---------------------
 0.40540353712197724
(1 row)
```

SIGN(X)
Description: Returns the value corresponding to the symbol of X. That is, 1 is returned for a positive value, 0 is returned for a zero value, and -1 is returned for a negative value.

Return type: BIGINT.

Example:
```
m_db=# SELECT SIGN(5.2);
 sign 
------
    1
(1 row)

m_db=# SELECT SIGN(0);
 sign 
------
    0
(1 row)

m_db=# SELECT SIGN(-5.2);
 sign 
------
   -1
(1 row)
```

SIN(X)

Description: Returns the sine value of the specified radian X.

Return type: DOUBLE.

Example:

m_db=# SELECT SIN(PI());
          sin           
------------------------
 1.2246467991473532e-16
(1 row)

SQRT(X)
Description: Returns the square root of X.

Return type: DOUBLE.

Example:
```
m_db=# SELECT SQRT(0.64);
 sqrt 
------
  0.8
(1 row)
```

TAN(X)

Description: Returns the tangent value of the specified radian X.

Return type: DOUBLE.

Example:

m_db=# SELECT TAN(PI());
           tan           
-------------------------
 -1.2246467991473532e-16
(1 row)

TRUNCATE(X, D)
Description: Returns the result of X with D decimal places.

Parameter: If parameter D is 0, the integer part of X is returned. If parameter D is a negative number, D places to the left of the decimal point in X will be adjusted to 0.

Return type: BIGINT, DOUBLE, or DECIMAL.

Example:
```
m_db=# SELECT TRUNCATE(3.2312, 1);
 truncate 
----------
      3.2
(1 row)

m_db=# SELECT TRUNCATE(123.2312, -1);
 truncate 
----------
      120
(1 row)
```

Extended Scenarios

Precedence of arithmetic operations: Negation > Multiplication, Division, Modulo, and Exact division > Addition and Subtraction.
Example 1: The multiplication operation takes precedence over the addition operation.
```
m_db=# SELECT 1 + 2 * 3;
 ?column?
----------
        7
(1 row)
```
Example 2: The modulo operator (%) takes precedence over the addition operation.
```
m_db=# SELECT 1 + 2 % 3;
 ?column?
----------
        3
(1 row)
```
Example 3: The modulo operation (MOD) takes precedence over the addition operation.
```
m_db=# SELECT 1 + 2 MOD 3;
 ?column?
----------
        3
(1 row)
```
Example 4: The exact division operation takes precedence over the addition operation.
```
m_db=# SELECT 1 + 7 DIV 3;
 ?column?
----------
        3
(1 row)
```