Mathematical Functions and Operators
Numeric Operators
- +
Example:
1 2 3 4 5
openGauss=# SELECT 2+3 AS RESULT; result -------- 5 (1 row)
- -
Example:
1 2 3 4 5
openGauss=# SELECT 2-3 AS RESULT; result -------- -1 (1 row)
- *
Example:
1 2 3 4 5
openGauss=# SELECT 2*3 AS RESULT; result -------- 6 (1 row)
- /
Description: Division (The result is not rounded.)
Example:
1 2 3 4 5
openGauss=# SELECT 4/2 AS RESULT; result -------- 2 (1 row)
1 2 3 4 5
openGauss=# SELECT 4/3 AS RESULT; result ------------------ 1.33333333333333 (1 row)
- +/-
Description: Positive/Negative
Example:
1 2 3 4 5
openGauss=# SELECT -2 AS RESULT; result -------- -2 (1 row)
- %
Description: Model (to obtain the remainder)
Example:
1 2 3 4 5
openGauss=# SELECT 5%4 AS RESULT; result -------- 1 (1 row)
- @
Example:
1 2 3 4 5
openGauss=# SELECT @ -5.0 AS RESULT; result -------- 5.0 (1 row)
- ^
Description: Power (exponent calculation)
Example:
1 2 3 4 5
openGauss=# SELECT 2.0^3.0 AS RESULT; result -------------------- 8.0000000000000000 (1 row)
- |/
Example:
1 2 3 4 5
openGauss=# SELECT |/ 25.0 AS RESULT; result -------- 5 (1 row)
- ||/
Example:
1 2 3 4 5
openGauss=# SELECT ||/ 27.0 AS RESULT; result -------- 3 (1 row)
- !
Example:
1 2 3 4 5
openGauss=# SELECT 5! AS RESULT; result -------- 120 (1 row)
- !!
Description: Factorial (prefix operator)
Example:
1 2 3 4 5
openGauss=# SELECT !!5 AS RESULT; result -------- 120 (1 row)
- &
Example:
1 2 3 4 5
openGauss=# SELECT 91&15 AS RESULT; result -------- 11 (1 row)
- |
Example:
1 2 3 4 5
openGauss=# SELECT 32|3 AS RESULT; result -------- 35 (1 row)
- #
Example:
1 2 3 4 5
openGauss=# SELECT 17#5 AS RESULT; result -------- 20 (1 row)
- ~
Example:
1 2 3 4 5
openGauss=# SELECT ~1 AS RESULT; result -------- -2 (1 row)
- <<
Description: Binary shift left
Example:
1 2 3 4 5
openGauss=# SELECT 1<<4 AS RESULT; result -------- 16 (1 row)
- >>
Description: Binary shift right
Example:
1 2 3 4 5
openGauss=# SELECT 8>>2 AS RESULT; result -------- 2 (1 row)
Numeric Operation Functions
- abs(x)
Return type: same as the input
Example:
1 2 3 4 5
openGauss=# SELECT abs(-17.4); abs ------ 17.4 (1 row)
- acos(x)
Return type: double precision
Example:
1 2 3 4 5
openGauss=# SELECT acos(-1); acos ------------------ 3.14159265358979 (1 row)
- asin(x)
Return type: double precision
Example:
1 2 3 4 5
openGauss=# SELECT asin(0.5); asin ------------------ .523598775598299 (1 row)
- atan(x)
Return type: double precision
Example:
1 2 3 4 5
openGauss=# 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
openGauss=# SELECT atan2(2, 1); atan2 ------------------ 1.10714871779409 (1 row)
- bitand(integer, integer)
Description: Performs an AND (&) operation on two integers.
Return type: bigint
Example:
1 2 3 4 5
openGauss=# SELECT bitand(127, 63); bitand -------- 63 (1 row)
- cbrt(dp)
Return type: double precision
Example:
1 2 3 4 5
openGauss=# 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
openGauss=# 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: 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
openGauss=# SELECT ceiling(-95.3); ceiling --------- -95 (1 row)
- cos(x)
Return type: double precision
Example:
1 2 3 4 5
openGauss=# SELECT cos(-3.1415927); cos ------------------- -.999999999999999 (1 row)
- cot(x)
Return type: double precision
Example:
1 2 3 4 5
openGauss=# SELECT cot(1); cot ------------------ .642092615934331 (1 row)
- degrees(dp)
Description: Converts radians to angles.
Return type: double precision
Example:
1 2 3 4 5
openGauss=# 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
openGauss=# SELECT div(9,4); div ----- 2 (1 row)
- exp(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
openGauss=# SELECT exp(1.0); exp -------------------- 2.7182818284590452 (1 row)
- floor(x)
Description: Maximum integer not larger than the parameter
Return type: same as the input
Example:
1 2 3 4 5
openGauss=# 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
openGauss=# select int1('123'); int1 ------ 123 (1 row) openGauss=# select int1('a'); int1 ------ 0 (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 float4, float8, int16, numeric, and text.
Return type: int2
Example:
1 2 3 4 5 6 7 8 9 10
openGauss=# select int2('1234'); int2 ------ 1234 (1 row) openGauss=# 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
openGauss=# select int4('789'); int4 ------ 789 (1 row) openGauss=# select int4(99.9); int4 ------ 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, duoble precision, int16, integer, numeric, smallint, and text.
Return type: float4
Example:
1 2 3 4 5 6 7 8 9 10 11
openGauss=# select float4('789'); float4 -------- 789 (1 row) openGauss=# 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
openGauss=# select float8('789'); float8 -------- 789 (1 row) openGauss=# 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
openGauss=# select int16('789'); int16 -------- 789 (1 row) openGauss=# select int16(99.9); int16 -------- 99 (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
openGauss=# select "numeric"('789'); numeric --------- 789 (1 row) openGauss=# 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
openGauss=# 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
openGauss=# 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
openGauss=# SELECT multiply(9.0, '3.0'); multiply ------------------- 27 (1 row) openGauss=# SELECT multiply('9.0', 3.0); multiply ------------------- 27 (1 row)
- ln(x)
Description: Natural logarithm
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
openGauss=# 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
openGauss=# 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
openGauss=# SELECT log(2.0, 64.0); log -------------------- 6.0000000000000000 (1 row)
- mod(x,y)
Description: Remainder of x/y (model) If x equals to 0, 0 is returned.
Return type: same as the parameter type
Example:
1 2 3 4 5
openGauss=# SELECT mod(9,4); mod ----- 1 (1 row)
1 2 3 4 5
openGauss=# SELECT mod(9,0); mod ----- 9 (1 row)
- pi()
Return type: double precision
Example:
1 2 3 4 5
openGauss=# SELECT pi(); pi ------------------ 3.14159265358979 (1 row)
- power(a double precision, b double precision)
Return type: double precision
Example:
1 2 3 4 5
openGauss=# SELECT power(9.0, 3.0); power ---------------------- 729.0000000000000000 (1 row)
- round(x)
Description: Integer closest to the input parameter
Return type: same as the input
Example:
1 2 3 4 5 6 7 8 9 10 11
openGauss=# SELECT round(42.4); round ------- 42 (1 row) openGauss=# SELECT round(42.6); round ------- 43 (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
openGauss=# SELECT round(42.4382, 2); round ------- 42.44 (1 row)
- setseed(dp)
Description: Sets seed for the following random() invoking (between –1.0 and 1.0, inclusive).
Return type: void
Example:
1 2 3 4 5
openGauss=# SELECT setseed(0.54823); setseed --------- (1 row)
- sign(x)
Description: Returns symbols of this parameter.
Return type: –1 indicates minus. 0 indicates 0, and 1 indicates positive numbers.
Example:
1 2 3 4 5
openGauss=# SELECT sign(-8.4); sign ------ -1 (1 row)
- sin(x)
Return type: double precision
Example:
1 2 3 4 5
openGauss=# SELECT sin(1.57079); sin ------------------ .999999999979986 (1 row)
- sqrt(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
openGauss=# SELECT sqrt(2.0); sqrt ------------------- 1.414213562373095 (1 row)
- tan(x)
Return type: double precision
Example:
1 2 3 4 5
openGauss=# SELECT tan(20); tan ------------------ 2.23716094422474 (1 row)
- trunc(x)
Description: Truncates (the integral part).
Return type: same as the input
Example:
1 2 3 4 5
openGauss=# 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
openGauss=# SELECT trunc(42.4382, 2); trunc ------- 42.43 (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 a greater-than operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: Boolean
- int1larger
Description: Returns the maximum value of an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: tinyint
- int1le
Description: Performs a less-than or an equal-to operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: Boolean
- int1lt
Description: Performs a less-than operation on an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: Boolean
- int1smaller
Description: Calculates the minimum value of an unsigned 1-byte integer.
Parameter: tinyint, tinyint
Return type: tinyint
- int1inc
Description: Unsigned 1-byte integer plus 1.
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 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
- 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
openGauss=# 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
openGauss=# SELECT width_bucket(5.35, 0.024, 10.06, 5); width_bucket -------------- 3 (1 row)
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
