Arithmetic Functions and Operators
Arithmetic Operators
- +
Example:
1 2 3 4 5
gaussdb=# SELECT 2+3 AS RESULT; result -------- 5 (1 row)
- -
Example:
1 2 3 4 5
gaussdb=# SELECT 2-3 AS RESULT; result -------- -1 (1 row)
- *
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)
- @
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)
- |/
Example:
1 2 3 4 5
gaussdb=# SELECT |/ 25.0 AS RESULT; result -------- 5 (1 row)
- ||/
Example:
1 2 3 4 5
gaussdb=# SELECT ||/ 27.0 AS RESULT; result -------- 3 (1 row)
- !
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)
- &
Example:
1 2 3 4 5
gaussdb=# SELECT 91&15 AS RESULT; result -------- 11 (1 row)
- |
Example:
1 2 3 4 5
gaussdb=# SELECT 32|3 AS RESULT; result -------- 35 (1 row)
- #
Example:
1 2 3 4 5
gaussdb=# SELECT 17#5 AS RESULT; result -------- 20 (1 row)
- ~
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)
Return type: same as the input
Example:
1 2 3 4 5
gaussdb=# SELECT abs(-17.4); abs ------ 17.4 (1 row)
- acos(x)
Return type: double precision
Example:
1 2 3 4 5
gaussdb=# SELECT acos(-1); acos ------------------ 3.14159265358979 (1 row)
- asin(x)
Return type: double precision
Example:
1 2 3 4 5
gaussdb=# SELECT asin(0.5); asin ------------------ .523598775598299 (1 row)
- atan(x)
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)
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)
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)
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)
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)
- When sql_compatibility is set to 'MYSQL', non-integer characters are automatically truncated or the value 0 is returned.
- When sql_compatibility is not set to 'MYSQL', an error message is displayed for non-integer characters, indicating that the input is invalid.
- 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)
- When sql_compatibility is set to 'MYSQL', non-integer characters are automatically truncated or the value 0 is returned.
- When sql_compatibility is not set to 'MYSQL', an error message is displayed for non-integer characters, indicating that the input is invalid.
- 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)
- When sql_compatibility is set to 'MYSQL', non-integer characters are automatically truncated or the value 0 is returned.
- When sql_compatibility is not set to 'MYSQL', an error message is displayed for non-integer characters, indicating that the input is invalid.
- 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)
- When sql_compatibility is set to 'MYSQL', non-integer characters are automatically truncated or the value 0 is returned.
- When sql_compatibility is not set to 'MYSQL', an error message is displayed for non-integer characters, indicating that the input is invalid.
- 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)
- 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)
- mod(x,y)
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()
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)
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: s digits are kept after the decimal point.
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.
When the value of a_format_version is 10c and the value of a_format_dev_version is s1 in an ORA-compatible database, 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() calling (between –1.0 and 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)
Return type: double precision
Example:
1 2 3 4 5
gaussdb=# SELECT sin(1.57079); sin ------------------ .999999999979986 (1 row)
- sinh(x)
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)
Return type: same as the input
Example:
1 2 3 4 5
gaussdb=# SELECT sqrt(2.0); sqrt ------------------- 1.414213562373095 (1 row)
- tan(x)
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: Truncates (the integral part).
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 a number with s digits after the decimal point.
Return type: numeric
Example:
1 2 3 4 5
gaussdb=# SELECT trunc(42.4382, 2); trunc ------- 42.43 (1 row)
In an ORA-compatible database, this function is valid only when a_format_version is set to 10c and a_format_dev_version is a valid value under the compatible configuration item. 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
Description: Returns the absolute value of data of the uint8 type.
Parameter: tinyint
Return type: tinyint
- int1and
Description: Returns the bitwise AND result of two data records of the uint8 type.
Parameter: tinyint, tinyint
Return type: tinyint
- int1cmp
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
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
Description: Compares two pieces of data of the uint8 type to check whether they are the same.
Parameter: tinyint, tinyint
Return type: Boolean
- int1ge
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
Description: Performs the greater-than operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: Boolean
- int1larger
Description: Returns the larger value of unsigned one-byte integers.
Parameter: tinyint, tinyint
Return type: tinyint
- int1le
Description: Determines whether the unsigned 1-byte integer is less than or equal to.
Parameter: tinyint, tinyint
Return type: Boolean
- int1lt
Description: Determines whether the unsigned 1-byte integer is less than.
Parameter: tinyint, tinyint
Return type: Boolean
- int1smaller
Description: Returns the smaller of two unsigned one-byte integers.
Parameter: tinyint, tinyint
Return type: tinyint
- int1inc
Description: Performs an addition operation on an unsigned 1-byte integer.
Parameter: tinyint
Return type: tinyint
- int1mi
Description: Performs a minus operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: tinyint
- int1mod
Description: Performs a reminder operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: tinyint
- int1mul
Description: Performs a multiplication operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: tinyint
- int1ne
Description: Performs a not-equal-to operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: Boolean
- int1pl
Description: Performs an addition operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: tinyint
- int1um
Description: Returns an unsigned 2-byte integer after subtracting the opposite number from the unsigned 1-byte integer.
Parameter: tinyint
Return type: smallint
- int1xor
Description: Performs an exclusive OR operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: tinyint
- cash_div_int1
Description: Performs a division operation on the money type.
Parameter: money, tinyint
Return type: money
- cash_mul_int1
Description: Performs a multiplication operation on the money type.
Parameter: money, tinyint
Return type: money
- int1not
Description: Reverts binary bits of an unsigned 1-byte integer.
Parameter: tinyint
Return type: tinyint
- int1or
Description: Performs an OR operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: tinyint
- int1shl
Description: Shifts an unsigned 1-byte integer leftwards by a specified number of bits.
Parameter: tinyint, integer
Return type: tinyint
- int1shr
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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
