Arithmetic Functions and Operators

Arithmetic Operators

• +

  Description: Addition

  Example:

  1 2 3 4 5

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

• -

  Description: Subtraction

  Example:

  1 2 3 4 5

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

• *

  Description: Multiplication

  Example:

  1 2 3 4 5

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

• /

  Description: Division (The result is not rounded.)

  Example:

  1 2 3 4 5

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

  1 2 3 4 5

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

• +/-

  Description: Positive/Negative

  Example:

  1 2 3 4 5

  gaussdb=# SELECT -2 AS RESULT; result -------- -2 (1 row)

• %

  Description: Model (to obtain the remainder)

  Example:

  1 2 3 4 5

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

• @

  Description: Absolute value

  Example:

  1 2 3 4 5

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

• ^

  Description: Power (exponent calculation)

  Example:

  1 2 3 4 5

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

• |/

  Description: Square root

  Example:

  1 2 3 4 5

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

• ||/

  Description: Cubic root

  Example:

  1 2 3 4 5

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

• !

  Description: Factorial

  Example:

  1 2 3 4 5

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

• !!

  Description: Factorial (prefix operator)

  Example:

  1 2 3 4 5

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

• &

  Description: Binary AND

  Example:

  1 2 3 4 5

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

• |

  Description: Binary OR

  Example:

  1 2 3 4 5

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

• #

  Description: Binary XOR

  Example:

  1 2 3 4 5

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

• ~

  Description: Binary NOT

  Example:

  1 2 3 4 5

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

• <<

  Description: Binary shift left

  Example:

  1 2 3 4 5

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

• >>

  Description: Binary shift right

  Example:

  1 2 3 4 5

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

Arithmetic Functions

• abs(x)

  Description: Absolute value

  Return type: same as the input

  Example:

  1 2 3 4 5

  gaussdb=# SELECT abs(-17.4); abs ------ 17.4 (1 row)

• acos(x)

  Description: Arc cosine

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT acos(-1); acos ------------------ 3.14159265358979 (1 row)

• asin(x)

  Description: Arc sine

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT asin(0.5); asin ------------------ .523598775598299 (1 row)

• atan(x)

  Description: Arc tangent

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT atan(1); atan ------------------ .785398163397448 (1 row)

• atan2(y, x)

  Description: Arc tangent of y/x

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT atan2(2, 1); atan2 ------------------ 1.10714871779409 (1 row)

• bitand(integer, integer)

  Description: Performs the AND (&) operation on two integers.

  Return type: bigint

  Example:

  1 2 3 4 5

  gaussdb=# SELECT bitand(127, 63); bitand -------- 63 (1 row)

• cbrt(dp)

  Description: Cubic root

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT cbrt(27.0); cbrt ------ 3 (1 row)

• ceil(x)

  Description: Minimum integer greater than or equal to the parameter

  Return type: integer

  Example:

  1 2 3 4 5

  gaussdb=# SELECT ceil(-42.8); ceil ------ -42 (1 row)

• ceiling(dp or numeric)

  Description: Minimum integer (alias of ceil) greater than or equal to the parameter

  Return type: same as the input

  Example:

  1 2 3 4 5

  gaussdb=# SELECT ceiling(-95.3); ceiling --------- -95 (1 row)

• cos(x)

  Description: Cosine

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT cos(-3.1415927); cos ------------------- -.999999999999999 (1 row)

• cosh(x)

  Description: Hyperbolic cosine

  Return type: dp or numeric. If implicit type conversion is not considered, the return type is the same as the input type.

  Example:

  1 2 3 4 5

  gaussdb=# SELECT cosh(4); cosh ------------------- 27.3082328360165 (1 row)

  

  This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.

• cot(x)

  Description: Cotangent

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT cot(1); cot ------------------ .642092615934331 (1 row)

• degrees(dp)

  Description: Converts radians to angles.

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT degrees(0.5); degrees ------------------ 28.6478897565412 (1 row)

• div(y numeric, x numeric)

  Description: Integer part of y/x

  Return type: numeric

  Example:

  1 2 3 4 5

  gaussdb=# SELECT div(9,4); div ----- 2 (1 row)

• exp(x)

  Description: Natural exponent

  Return type: same as the input

  Example:

  1 2 3 4 5

  gaussdb=# SELECT exp(1.0); exp -------------------- 2.7182818284590452 (1 row)

• floor(x)

  Description: Not larger than the maximum integer of the parameter

  Return type: same as the input

  Example:

  1 2 3 4 5

  gaussdb=# SELECT floor(-42.8); floor ------- -43 (1 row)

• int1(in)

  Description: Converts the input text parameter to a value of the int1 type and returns the value.

  Return type: int1

  Example:

  1 2 3 4 5 6 7 8 9 10

  gaussdb=# SELECT int1('123'); int1 ------ 123 (1 row) gaussdb=# SELECT int1('1.1'); int1 ------ 1 (1 row)

• int2(in)

  Description: Converts the input parameter to a value of the int2 type and returns the value. The supported input parameter types include bigint, float4, float8, int16, integer, numeric, real, and text.

  Return type: int2

  Example:

  1 2 3 4 5 6 7 8 9 10

  gaussdb=# SELECT int2('1234'); int2 ------ 1234 (1 row) gaussdb=# SELECT int2(25.3); int2 ------ 25 (1 row)

• int4(in)

  Description: Converts the input parameter to a value of the int4 type and returns the value. The supported input parameter types include bit, boolean, char, double precision, int16, numeric, real, smallint, and text

  Return type: int4

  Example:

  1 2 3 4 5 6 7 8 9 10

  gaussdb=# SELECT int4('789'); int4 ------ 789 (1 row) gaussdb=# SELECT int4(99.9); int4 ------ 100 (1 row)

• int8(in)

  Description: Converts the input parameter to a value of the int8 type and returns the value. The supported input parameter types include bit, double precision, int16, integer, numeric, oid, real, smallint, and text.

  Return type: int8

  Example:

  1 2 3 4 5 6 7 8 9 10

  gaussdb=# SELECT int8('789'); int8 ------ 789 (1 row) gaussdb=# SELECT int8(99.9); int8 ------ 99 (1 row)

• float4(in)

  Description: Converts the input parameter to a value of the float4 type and returns the value. The supported input parameter types include bigint, double precision, int16, integer, numeric, smallint, and text.

  Return type: float4

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  gaussdb=# SELECT float4('789'); float4 -------- 789 (1 row) gaussdb=# SELECT float4(99.9); float4 -------- 99.9 (1 row)

• float8(in)

  Description: Converts the input parameter to a value of the float8 type and returns the value. The supported input parameter types include bigint, int16, integer, numeric, real, smallint, and text.

  Return type: float8

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  gaussdb=# SELECT float8('789'); float8 -------- 789 (1 row) gaussdb=# SELECT float8(99.9); float8 -------- 99.9 (1 row)

• int16(in)

  Description: Converts the input parameter to a value of the int16 type and returns the value. The supported input parameter types include bigint, boolean, double precision, integer, numeric, oid, real, smallint, and tinyint.

  Return type: int16

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  gaussdb=# SELECT int16('789'); int16 -------- 789 (1 row) gaussdb=# SELECT int16(99.9); int16 -------- 100 (1 row)

• numeric(in)

  Description: Converts the input parameter to a value of the numeric type and returns the value. The supported input parameter types include bigint, boolean, double precision, int16, integer, money, real, and smallint.

  Return type: numeric

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  gaussdb=# SELECT "numeric"('789'); numeric --------- 789 (1 row) gaussdb=# SELECT "numeric"(99.9); numeric --------- 99.9 (1 row)

• oid(in)

  Description: Converts the input parameter to a value of the oid type and returns the value. The supported input parameter types include bigint and int16.

  Return type: oid

• radians(dp)

  Description: Converts angles to radians.

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT radians(45.0); radians ------------------ .785398163397448 (1 row)

• random()

  Description: Random number between 0.0 and 1.0.

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT random(); random ------------------ .824823560658842 (1 row)

• rand([seed])

  Description: Returns a random number between 0 and 1. If seed is specified, the random value of seed is returned. There can be no input parameter or a seed input parameter of the bigint type.

  Parameter: a random number seed of the bigint type.

  Return type: double

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  b_compatible_db=# SELECT rand(); rand ------------------ .327476012520492 (1 row) b_compatible_db=# SELECT rand(12321); rand ------------------ .326073104515672 (1 row)

  

  This function takes effect when the database parameter sql_compatibility is set to 'MYSQL'.

• multiply(x double precision or text, y double precision or text)

  Description: product of x and y.

  Return type: double precision

  Example:

  1 2 3 4 5 6 7 8 9 10

  gaussdb=# SELECT multiply(9.0, '3.0'); multiply ------------------- 27 (1 row) gaussdb=# SELECT multiply('9.0', 3.0); multiply ------------------- 27 (1 row)

• ln(x)

  Description: Natural logarithm.

  Return type: same as the input

  Example:

  1 2 3 4 5

  gaussdb=# SELECT ln(2.0); ln ------------------- .6931471805599453 (1 row)

• log(x)

  Description: Logarithm with 10 as the base.

  Return type: same as the input.

  Example:

  1 2 3 4 5

  gaussdb=# SELECT log(100.0); log -------------------- 2.0000000000000000 (1 row)

• log(b numeric, x numeric)

  Description: Logarithm with b as the base.

  Return type: numeric

  Example:

  1 2 3 4 5

  gaussdb=# SELECT log(2.0, 64.0); log -------------------- 6.0000000000000000 (1 row)

• log2(x)

  Description: Logarithm with 2 as the base.

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT log2(2); log2 ------ 1 (1 row)

  

  This function takes effect when the database parameter sql_compatibility is set to 'MYSQL'.

• log10(x)

  Description: Logarithm with 10 as the base.

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT log10(10); log10 ------- 1 (1 row)

  

  This function takes effect when the database parameter sql_compatibility is set to 'MYSQL'.

• mod(x,y)

  Description:

  Remainder of x/y (model).

  If x equals to 0, y is returned.

  Return type: same as the parameter type

  Example:

  1 2 3 4 5

  gaussdb=# SELECT mod(9,4); mod ----- 1 (1 row)

  1 2 3 4 5

  gaussdb=# SELECT mod(9,0); mod ----- 9 (1 row)

• pi()

  Description: π constant value.

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT pi(); pi ------------------ 3.14159265358979 (1 row)

• power(a double precision, b double precision)

  Description: b power of a.

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT power(9.0, 3.0); power ---------------------- 729.0000000000000000 (1 row)

• remainder(x,y)

  Description: Remainder of x/y. If y is 0, an error is reported.

  Return type: same as the input (float4, float8, or numeric)

  Example:

  1 2 3 4 5 6 7 8

  gaussdb=# SELECT remainder(11,4); remainder ---------- -1 (1 row) gaussdb=# SELECT remainder(9,0); ERROR: division by zero CONTEXT: referenced column: remainder

  

  This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.

• round(x)

  Description: Integer closest to the input parameter.

  Return type: same as the input (double precision or numeric).

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  gaussdb=# SELECT round(42.4); round ------- 42 (1 row) gaussdb=# SELECT round(42.6); round ------- 43 (1 row)

  

  The output of the float/double type may be -0. (This also occurs in functions such as trunc and ceil. If the value of a_format_version is 10c and that of a_format_dev_version is s1 in an ORA-compatible database, the returned result is 0. The following is an example:

  1 2 3 4 5

  gaussdb=# SELECT round(-0.2::float8); round ------- -0 (1 row)

• round(v numeric, s int)

  Description: The number of s digits are kept after the decimal point, and the number is rounded off.

  Return type: numeric

  Example:

  1 2 3 4 5

  gaussdb=# SELECT round(42.4382, 2); round ------- 42.44 (1 row)

  

  • When the value of a_format_version is 10c and that of a_format_dev_version is s1 in an ORA-compatible database, s is truncated to an integer. Otherwise, s is rounded off to an integer.
  • If the value of a_format_version is 10c and the value of a_format_dev_version is s1, the round function supports round(timestamp, text) overloading. When (text, text) or (text, '') is used as the input parameter to call the round function, round (timestamp, text) is preferred.

• setseed(dp)

  Description: Sets seed for the following random() invoking (from –1.0 to 1.0, inclusive).

  Return type: void

  Example:

  1 2 3 4 5

  gaussdb=# SELECT setseed(0.54823); setseed --------- (1 row)

• sign(x)

  Description: Returns symbols of this parameter.

  Return type: –1 indicates negative numbers. 0 indicates 0, and 1 indicates positive numbers.

  Example:

  1 2 3 4 5

  gaussdb=# SELECT sign(-8.4); sign ------ -1 (1 row)

• sin(x)

  Description: Sine

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT sin(1.57079); sin ------------------ .999999999979986 (1 row)

• sinh(x)

  Description: Hyperbolic sine

  Return type: dp or numeric. If implicit type conversion is not considered, the return type is the same as the input type.

  Example:

  1 2 3 4 5

  gaussdb=# SELECT sinh(4); sinh ------------------ 27.2899171971277 (1 row)

  

  This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.

• sqrt(x)

  Description: Square root

  Return type: same as the input.

  Example:

  1 2 3 4 5

  gaussdb=# SELECT sqrt(2.0); sqrt ------------------- 1.414213562373095 (1 row)

  

  This function uses the Karatsuba sqrt square root algorithm when the GUC parameter gs_format_behavior_compat_options is set to sqrt_karatsuba. Otherwise, this function uses the Newton iteration algorithm. The performance of the Karatsuba sqrt square root algorithm is better. In a few scenarios, the precision of the Karatsuba sqrt square root algorithm is different from that of the Newton iteration algorithm.

• tan(x)

  Description: Tangent

  Return type: double precision

  Example:

  1 2 3 4 5

  gaussdb=# SELECT tan(20); tan ------------------ 2.23716094422474 (1 row)

• tanh(x)

  Description: Hyperbolic tangent

  Return type: same as the input (double precision or numeric)

  Example:

  1 2 3 4 5

  gaussdb=# SELECT tanh(0.1); tanh ------------------------------------------ 0.0996679946249558171183050836783521835389 (1 row)

  

  This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.

• trunc(x)

  Description: Truncation (the integer part is retained).

  Return type: same as the input.

  Example:

  1 2 3 4 5

  gaussdb=# SELECT trunc(42.8); trunc ------- 42 (1 row)

• trunc(v numeric, s int)

  Description: Truncates the last s digits after a decimal point.

  Return type: numeric

  Example:

  1 2 3 4 5

  gaussdb=# SELECT trunc(42.4382, 2); trunc ------- 42.43 (1 row)

  

  This function is valid only when a_format_version is set to 10c and a_format_dev_version is set to s1 in ORA-compatible mode. If the value of s is a decimal, the value is truncated instead of being rounded off.

• width_bucket(op numeric, b1 numeric, b2 numeric, count int)

  Description: Returns a bucket to which the operand will be assigned in an equi-depth histogram with count buckets, ranging from b1 to b2.

  Return type: int

  Example:

  1 2 3 4 5

  gaussdb=# SELECT width_bucket(5.35, 0.024, 10.06, 5); width_bucket -------------- 3 (1 row)

• width_bucket(op dp, b1 dp, b2 dp, count int)

  Description: Returns a bucket to which the operand will be assigned in an equi-depth histogram with count buckets, ranging from b1 to b2.

  Return type: int

  Example:

  1 2 3 4 5

  gaussdb=# SELECT width_bucket(5.35, 0.024, 10.06, 5); width_bucket -------------- 3 (1 row)

• smgrne(a smgr, b smgr)

  Description: Compares two integers of the smgr type to check whether they are different.

  Return type: Boolean

• smgreq(a smgr, b smgr)

  Description: Compares two integers of the smgr type to check whether they are equivalent.

  Return type: Boolean

• int1abs(tinyint)

  Description: Returns the absolute value of data of the uint8 type.

  Parameter: tinyint

  Return type: tinyint

• int1and(tinyint, tinyint)

  Description: Returns the bitwise AND result of two data records of the uint8 type.

  Parameter: tinyint, tinyint

  Return type: tinyint

• int1cmp(tinyint, tinyint)

  Description: Returns the comparison result of two data records of the uint8 type. If the value of the first parameter is greater, 1 is returned. If the value of the second parameter is greater, –1 is returned. If they are the same, 0 is returned.

  Parameter: tinyint, tinyint

  Return type: integer

• int1div(tinyint, tinyint)

  Description: Returns the result of dividing two data records of the uint8 type. The result is of the float8 type.

  Parameter: tinyint, tinyint

  Return type: tinyint

• int1eq(tinyint, tinyint)

  Description: Compares two pieces of data of the uint8 type to check whether they are the same.

  Parameter: tinyint, tinyint

  Return type: Boolean

• int1ge(tinyint, tinyint)

  Description: Determines whether the value of the first parameter is greater than or equal to the value of the second parameter in two data records of the uint8 type.

  Parameter: tinyint, tinyint

  Return type: Boolean

• int1gt(tinyint, tinyint)

  Description: Performs the greater-than operation on an unsigned 1-byte integer.

  Parameter: tinyint, tinyint

  Return type: Boolean

• int1larger(tinyint, tinyint)

  Description: Returns the larger value of unsigned one-byte integers.

  Parameter: tinyint, tinyint

  Return type: tinyint

• int1le(tinyint, tinyint)

  Description: Determines whether the unsigned 1-byte integer is less than or equal to.

  Parameter: tinyint, tinyint

  Return type: Boolean

• int1lt(tinyint, tinyint)

  Description: Determines whether the unsigned 1-byte integer is less than.

  Parameter: tinyint, tinyint

  Return type: Boolean

• int1smaller(tinyint, tinyint)

  Description: Returns the smaller of two unsigned one-byte integers.

  Parameter: tinyint, tinyint

  Return type: tinyint

• int1inc(tinyint)

  Description: Unsigned 1-byte integer plus 1.

  Parameter: tinyint

  Return type: tinyint

• int1mi(tinyint, tinyint)

  Description: Performs a minus operation on an unsigned 1-byte integer.

  Parameter: tinyint, tinyint

  Return type: tinyint

• int1mod(tinyint, tinyint)

  Description: Performs a reminder operation on an unsigned 1-byte integer.

  Parameter: tinyint, tinyint

  Return type: tinyint

• int1mul(tinyint, tinyint)

  Description: Performs a multiplication operation on an unsigned 1-byte integer.

  Parameter: tinyint, tinyint

  Return type: tinyint

• int1ne(tinyint, tinyint)

  Description: Performs a not-equal-to operation on an unsigned 1-byte integer.

  Parameter: tinyint, tinyint

  Return type: Boolean

• int1pl(tinyint, tinyint)

  Description: Performs an addition operation on an unsigned 1-byte integer.

  Parameter: tinyint, tinyint

  Return type: tinyint

• int1um(tinyint)

  Description: Returns an unsigned 2-byte integer after subtracting the opposite number from the unsigned 1-byte integer.

  Parameter: tinyint

  Return type: smallint

• int1xor(tinyint, tinyint)

  Description: Performs an exclusive OR operation on an unsigned 1-byte integer.

  Parameter: tinyint, tinyint

  Return type: tinyint

• cash_div_int1(money, tinyint)

  Description: Performs a division operation on the money type.

  Parameter: money, tinyint

  Return type: money

• cash_mul_int1(money, tinyint)

  Description: Performs a multiplication operation on the money type.

  Parameter: money, tinyint

  Return type: money

• int1not(tinyint)

  Description: Reverts binary bits of an unsigned 1-byte integer.

  Parameter: tinyint

  Return type: tinyint

• int1or(tinyint, tinyint)

  Description: Performs an OR operation on an unsigned 1-byte integer.

  Parameter: tinyint, tinyint

  Return type: tinyint

• int1shl(tinyint, integer)

  Description: Shifts an unsigned 1-byte integer leftwards by a specified number of bits.

  Parameter: tinyint, integer

  Return type: tinyint

• int1shr(tinyint, integer)

  Description: Shifts an unsigned 1-byte integer rightwards by a specified number of bits.

  Parameter: tinyint, integer

  Return type: tinyint

• analyze_tgtype_for_type(n smallint)

  Description: Parses pg_trigger.tgtype, parses n by bit, and returns one of before each row, after each row, before statement, after statement, and instead of.

  Return type: varchar2(16)

• analyze_tgtype_for_event(n smallint)

  Description: Parses pg_trigger.tgtype, parses n by bit, and returns one or more of insert, update, delete, and truncate.

  Return type: varchar2(246)

• nanvl(n2, n1)

  Description: Two parameters are entered. The parameters must be of the numeric type or a non-numeric type that can be implicitly converted to the numeric type. If the first parameter n2 is NaN, n1 is returned. Otherwise, n2 is returned.

  Return value type: input parameter with a higher priority. The priority is as follows: double precision > float4 > numeric.

  Example:

  gaussdb=# SELECT nanvl('NaN', 1.1);
   nanvl 
  -------
     1.1
  (1 row)

  

  This function is valid only when the value of a_format_version is 10c and that of a_format_dev_version is s2 in an ORA-compatible database.