Functions

The common functions of openGauss are as follows:

Mathematical Functions

  • abs(x)

    Description: Absolute value

    Return type: same as the input

    Example:

    openGauss=# SELECT abs(-17.4);
     abs
    ------
     17.4
    (1 row)
    
  • cbrt(dp)

    Description: Cubic root

    Return type: double precision

    Example:

    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:

    openGauss=# SELECT ceil(-42.8);
     ceil 
    ------
      -42
    (1 row)
    
  • degrees(dp)

    Description: Converts radians to angles.

    Return type: double precision

    Example:

    openGauss=# SELECT degrees(0.5);
         degrees
    ------------------
     28.6478897565412
    (1 row)
    
  • exp(x)

    Description: Natural exponent

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

    Example:

    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:

    openGauss=# SELECT floor(-42.8);
     floor 
    -------
       -43
    (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:

    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:

    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:

    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:

    openGauss=# SELECT mod(9,4);
     mod 
    -----
       1
    (1 row)
    
    openGauss=# SELECT mod(9,0);
     mod 
    -----
       9
    (1 row)
    
  • pi()

    Description: π constant value

    Return type: double precision

    Example:

    openGauss=# SELECT pi();
            pi
    ------------------
     3.14159265358979
    (1 row)
    
  • power(a double precision, b double precision)

    Description: b power of a

    Return type: double precision

    Example:

    openGauss=# SELECT power(9.0, 3.0);
            power         
    ----------------------
     729.0000000000000000
    (1 row)
    
  • radians(dp)

    Description: Converts angles to radians.

    Return type: double precision

    Example:

    openGauss=# SELECT radians(45.0);
         radians
    ------------------
     .785398163397448
    (1 row)
    
  • random()

    Description: Random number between 0.0 and 1.0

    Return type: double precision

    Example:

    openGauss=# SELECT random();
          random
    ------------------
     .824823560658842
    (1 row)
    
  • round(x)

    Description: Integer closest to the input parameter

    Return type: same as the input

    Example:

    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:

    openGauss=# SELECT round(42.4382, 2);
     round
    -------
     42.44
    (1 row)
    
  • sign(x)

    Description: Returns symbols of this parameter.

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

    Example:

    openGauss=# SELECT sign(-8.4);
     sign 
    ------
       -1
    (1 row)
    
  • sqrt(x)

    Description: Square root

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

    Example:

    openGauss=# SELECT sqrt(2.0);
           sqrt        
    -------------------
     1.414213562373095
    (1 row)
    
  • trunc(x)

    Description: Truncates (the integral part).

    Return type: same as the input

    Example:

    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:

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

Trigonometric Functions

  • acos(x)

    Description: Arc cosine

    Return type: double precision

    Example:

    openGauss=# SELECT acos(-1);
           acos       
    ------------------
     3.14159265358979
    (1 row)
    
  • asin(x)

    Description: Arc sine

    Return type: double precision

    Example:

    openGauss=# SELECT asin(0.5);
           asin       
    ------------------
     .523598775598299
    (1 row)
    
  • atan(x)

    Description: Arc tangent

    Return type: double precision

    Example:

    openGauss=# SELECT atan(1);
           atan       
    ------------------
     .785398163397448
    (1 row)
    
  • atan2(y, x)

    Description: Arc tangent of y/x

    Return type: double precision

    Example:

    openGauss=# SELECT atan2(2, 1);
          atan2
    ------------------
     1.10714871779409
    (1 row)
    
  • cos(x)

    Description: Cosine

    Return type: double precision

    Example:

    openGauss=# SELECT cos(-3.1415927);
            cos        
    -------------------
     -.999999999999999
    (1 row)
    
  • cot(x)

    Description: Cotangent

    Return type: double precision

    Example:

    openGauss=# SELECT cot(1);
           cot
    ------------------
     .642092615934331
    (1 row)
    
  • sin(x)

    Description: Sine

    Return type: double precision

    Example:

    openGauss=# SELECT sin(1.57079);
           sin        
    ------------------
     .999999999979986
    (1 row)
    
  • tan(x)

    Description: Tangent

    Return type: double precision

    Example:

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

String Functions and Operators

  • string || string

    Description: Concatenates strings.

    Return type: text

    Example:

    openGauss=# SELECT 'MPP'||'DB' AS RESULT;
     result 
    --------
     MPPDB
    (1 row)
    
  • bit_length(string)

    Description: Specifies the number of bits occupied by a string.

    Return type: int

    Example:

    openGauss=# SELECT bit_length('world');
     bit_length
    ------------
             40
    (1 row)
    
  • convert(string bytea, src_encoding name, dest_encoding name)

    Description: Converts the bytea string to dest_encoding. src_encoding specifies the source code encoding. The string must be valid in this encoding.

    Return type: bytea

    Example:

    openGauss=# SELECT convert('text_in_utf8', 'UTF8', 'GBK');
              convert        
    ----------------------------
     \x746578745f696e5f75746638
    (1 row)
    
  • lower(string)

    Description: Converts the string into the lowercase.

    Return type: varchar

    Example:

    openGauss=# SELECT lower('TOM');
     lower
    -------
     tom
    (1 row)
    
  • octet_length(string)

    Description: Specifies the number of bytes in a string.

    Return type: int

    Example:

    openGauss=# SELECT octet_length('jose');
     octet_length
    --------------
                4
    (1 row)
    
  • overlay(string placing string FROM int [for int])

    Description: Replaces substrings. FROM int indicates the start position of the replacement in the first string. for int indicates the number of characters replaced in the first string.

    Return type: text

    Example:

    openGauss=# SELECT overlay('hello' placing 'world' from 2 for 3 );
     overlay 
    ---------
     hworldo
    (1 row)
    
  • position(substring in string)

    Description: Specifies the position of a substring. Parameters are case-sensitive.

    Return type: int. If the character string does not exist, 0 is returned.

    Example:

    openGauss=# SELECT position('ing' in 'string');
     position
    ----------
            4
    (1 row)
    
  • substring(string [from int] [for int])

    Description: Extracts a substring. from int indicates the start position of the truncation. for int indicates the number of characters truncated.

    Return type: text

    Example:

    openGauss=# SELECT substring('Thomas' from 2 for 3);
     substring
    -----------
     hom
    (1 row)
    
  • substring(string from pattern)

    Description: Extracts substrings matching the POSIX regular expression. It returns the text that matches the pattern. If no match record is found, a null value is returned.

    Return type: text

    Example:

    openGauss=# SELECT substring('Thomas' from '...$');
     substring
    -----------
     mas
    (1 row)
    openGauss=# SELECT substring('foobar' from 'o(.)b');
     result 
    --------
     o
    (1 row)
    openGauss=# SELECT substring('foobar' from '(o(.)b)');
     result 
    --------
     oob
    (1 row)
    
  • trim([leading |trailing |both] [characters] from string)

    Description: Removes the longest string containing only the characters (a space by default) from the start/end/both ends of the string.

    Return type: varchar

    Example:

    openGauss=# SELECT trim(BOTH 'x' FROM 'xTomxx');
     btrim
    -------
     Tom
    (1 row)
    
    openGauss=# SELECT trim(LEADING 'x' FROM 'xTomxx');
     ltrim
    -------
     Tomxx
    (1 row)
    
    openGauss=# SELECT trim(TRAILING 'x' FROM 'xTomxx');
     rtrim
    -------
     xTom
    (1 row)
    
  • upper(string)

    Description: Converts the string into the uppercase.

    Return type: varchar

    Example:

    openGauss=# SELECT upper('tom');
     upper
    -------
     TOM
    (1 row)
    
  • ascii(string)

    Description: Indicates the ASCII code of the first character in the string.

    Return type: integer

    Example:

    openGauss=# SELECT ascii('xyz');
     ascii 
    -------
       120
    (1 row)
    
  • btrim(string text [, characters text])

    Description: Removes the longest string consisting only of characters in characters (a space by default) from the start and end of string.

    Return type: text

    Example:

    openGauss=# SELECT btrim('sring' , 'ing');
     btrim
    -------
     sr
    (1 row)
    
  • chr(integer)

    Description: Specifies the character of the ASCII code.

    Return type: varchar

    Example:

    openGauss=# SELECT chr(65);
     chr
    -----
     A
    (1 row)
    
  • convert(string bytea, src_encoding name, dest_encoding name)

    Description: Converts the bytea string to dest_encoding. src_encoding specifies the source code encoding. The string must be valid in this encoding.

    Return type: bytea

    Example:

    openGauss=# SELECT convert('text_in_utf8', 'UTF8', 'GBK');
              convert        
    ----------------------------
     \x746578745f696e5f75746638
    (1 row)
    
  • initcap(string)

    Description: Converts the first letter of each word in the string into the uppercase and the other letters into the lowercase.

    Return type: text

    Example:

    openGauss=# SELECT initcap('hi THOMAS');
      initcap
    -----------
     Hi Thomas
    (1 row)
    
  • length(string)

    Description: Obtains the number of characters in a string.

    Return type: integer

    Example:

    openGauss=# SELECT length('abcd');
     length 
    --------
          4
    (1 row)
    
  • lpad(string text, length int [, fill text])

    Description: Fills up string to length by appending the characters fill (a space by default). If string is already longer than length, then it is truncated.

    Return type: text

    Example:

    openGauss=# SELECT lpad('hi', 5, 'xyza');
     lpad  
    -------
     xyzhi
    (1 row)
    
  • ltrim(string [, characters])

    Description: Removes the longest string containing only characters from characters (a space by default) from the start of string.

    Return type: varchar

    Example:

    openGauss=# SELECT ltrim('xxxxTRIM','x');
     ltrim
    -------
     TRIM
    (1 row)
    
  • md5(string)

    Description: Encrypts a string in MD5 mode and returns a value in hexadecimal form.

    NOTE: The MD5 encryption algorithm is not recommended because it has lower security and poses security risks.

    Return type: text

    Example:

    openGauss=# SELECT md5('ABC');
                   md5                
    ----------------------------------
     902fbdd2b1df0c4f70b4a5d23525e932
    (1 row)
    
  • repeat(string text, number int )

    Description: Repeats string the specified number of times.

    Return type: text

    Example:

    openGauss=# SELECT repeat('Pg', 4);
      repeat
    ----------
     PgPgPgPg
    (1 row)
    
  • replace(string text, from text, to text)

    Description: Replaces all occurrences in string of substring from with substring to.

    Return type: text

    Example:

    openGauss=# SELECT replace('abcdefabcdef', 'cd', 'XXX');
        replace     
    ----------------
     abXXXefabXXXef
    (1 row)
    
  • rpad(string text, length int [, fill text])

    Description: Fills up string to length by appending the characters fill (a space by default). If string is already longer than length, then it is truncated.

    Return type: text

    Example:

    openGauss=# SELECT rpad('hi', 5, 'xy');
     rpad
    -------
     hixyx
    (1 row)
    
  • rtrim(string text [, characters text])

    Description: Removes the longest string containing only characters from characters (a space by default) from the end of string.

    Return type: text

    Example:

    openGauss=# SELECT rtrim('trimxxxx', 'x');
     rtrim
    -------
     trim
    (1 row)
    
  • split_part(string text, delimiter text, field int)

    Description: Splits string on delimiter and returns the _field_th column (counting from text of the first appeared delimiter).

    Return type: text

    Example:

    openGauss=# SELECT split_part('abc~@~def~@~ghi', '~@~', 2);
     split_part
    ------------
     def
    (1 row)
    
  • strpos(string, substring)

    Description: Specifies the position of a substring. It is the same as position(substring in string). However, the parameter sequences of them are reversed.

    Return type: int

    Example:

    openGauss=# SELECT strpos('source', 'rc');
     strpos
    --------
          4
    (1 row)
    
  • to_hex(number int or bigint)

    Description: Converts a number to a hexadecimal expression.

    Return type: text

    Example:

    openGauss=# SELECT to_hex(2147483647);
      to_hex
    ----------
     7fffffff
    (1 row)
    
  • translate(string text, from text, to text)

    Description: Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. If from is longer than to, extra characters occurred in from are removed.

    Return type: text

    Example:

    openGauss=# SELECT translate('12345', '143', 'ax');
     translate
    -----------
     a2x5
    (1 row)
    
  • to_char(timestamp, text)

    Description: Converts the values of the timestamp type into the strings in the specified format.

    Return type: text

    Example:

    openGauss=# SELECT to_char(current_timestamp, 'HH12:MI:SS');
     to_char
    ----------
     10:55:59
    (1 row)
    
  • to_char(interval, text)

    Description: Converts the values of the time interval type into the strings in the specified format.

    Return type: text

    Example:

    openGauss=# SELECT to_char(interval '15h 2m 12s', 'HH24:MI:SS');
     to_char
    ----------
     15:02:12
    (1 row)
    
  • to_char(int, text)

    Description: Converts the values of the integer type into the strings in the specified format.

    Return type: text

    Example:

    openGauss=# SELECT to_char(125, '999');
     to_char
    ---------
      125
    (1 row)
    
  • to_char(double precision/real, text)

    Description: Converts the values of the floating point type into the strings in the specified format.

    Return type: text

    Example:

    openGauss=# SELECT to_char(125.8::real, '999D99');
     to_char 
    ---------
      125.80
    (1 row)
    
  • to_char(numeric, text)

    Description: Converts the values of the numeric type into the strings in the specified format.

    Return type: text

    Example:

    openGauss=# SELECT to_char(-125.8, '999D99S');
     to_char
    ---------
     125.80-
    (1 row)
    
  • to_date(text, text)

    Description: Converts the values of the string type into the dates in the specified format.

    Return type: timestamp without time zone

    Example:

    openGauss=# SELECT to_date('05 Dec 2000', 'DD Mon YYYY');
           to_date
    ---------------------
     2000-12-05 00:00:00
    (1 row)
    
  • to_number(text, text)

    Description: Converts the values of the string type into the numbers in the specified format.

    Return type: numeric

    Example:

    openGauss=# SELECT to_number('12,454.8-', '99G999D9S');
     to_number
    -----------
      -12454.8
    (1 row)
    
  • to_timestamp(text, text)

    Description: Converts values of the string type into the timestamp of the specified type.

    Return type: timestamp

    Example:

    openGauss=# SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY');
        to_timestamp
    ---------------------
     2000-12-05 00:00:00
    (1 row)
    
  • to_timestamp(double precision)

    Description: Converts a UNIX century into a timestamp.

    Return type: timestamp with time zone

    Example:

    openGauss=# SELECT to_timestamp(1284352323);
          to_timestamp      
    ------------------------
     2010-09-13 12:32:03+08
    (1 row)
    
Feedback
编组 3备份
    openGauss 2024-04-21 00:47:23
    cancel