:Supported ClickHouse functions

toString(anyelement)

• Description: converts data of any type into data of the TEXT type.

• Returned data type: TEXT

• Example:

  SELECT toString(id) from public.tb1;
  
  -- The following result is returned:
  1234

toInt64(anyelement)

• Description: converts data of a numeric type other than BIGINT into data of the BIGINT type.

  Note

  If you use this function to convert data of non-numeric types, an error is returned because the data types before and after the conversion are incompatible.

• Returned data type: BIGINT

• Examples:

  SELECT toInt64(id_text) from public.tb1;
  
  -- The following result is returned:
  1234

toInt32(anyelement)

• Description: converts data of a numeric type other than INT into data of the INT type.

  Note

  If you use this function to convert data of non-numeric types, an error is returned because the data types before and after the conversion are incompatible.

• Returned data type: INT

• Examples:

  SELECT toInt32(id_text) from public.tb1;
  
  -- The following result is returned:
  123

toDate(text)

• Description: converts data of the TEXT type into data of the DATE type.

• Returned data type: DATE

• Examples:

  • The conversion is successful.

    SELECT toDate(data_date) from public.tb1;
    
    -- The following result is returned:
    2019-01-02

  • An error is returned because the data types before and after the conversion are incompatible.

    SELECT toDate(data_text) from public.tb1;
    
    -- The following result is returned:
    ERROR: *** invalid input syntax for type date ***

toFloat64(anyelement)

• Description: converts data of a numeric type other than DOUBLE into data of the DOUBLE type.

• Returned data type: DOUBLE PRECISION

• Examples:

  SELECT toFloat64(data_float) from public.tb1;
  
  -- The following result is returned:
  1.23

toYear, toMonth, and toQuarter

• Description: extracts the year, month, or quarter part from a timestamp.

• Implementation in Hologres: extract(field from timestamp) or date_part(text, timestamp). For more information, see Datetime truncation functions.

• Examples:

  • SELECT extract(month FROM timestamp '2001-02-16 20:38:40');
    
    -- The following result is returned:
    2

  • SELECT extract(quarter FROM timestamp '2001-02-16 20:38:40');
    
    -- The following result is returned:
    1

addDays, addMonths, and addYears

• Description: adds a period of time to a timestamp.

• Implementation in Hologres: operator plus sign (+).

• Examples:

  SELECT date '2001-09-28' + interval '1 hour';
  
  -- The following result is returned:
  2001-09-28 01:00:00

subtractDays, subtractMonths, and subtractYears

• Description: subtracts a period of time from a timestamp.

• Implementation in Hologres: operator minus sign (-).

• Examples:

  SELECT date '2001-09-28' - interval '1 day';
  
  -- The following result is returned:
  2001-09-27 00:00:00

argMin(x, y)

• Description: obtains the value of the x column that corresponds to the minimum value of the y column.

• Implementation in Hologres: min_by(x, y).

  Note

  • If the minimum value of the y column corresponds to multiple different values of the x column, the minimum value in the multiple values of the x column is returned.

  • Hologres V1.3.36 and later support this aggregate function.

• Example: For more information about the CREATE TABLE statement for the table test, see Sample data.

  SELECT min_by(name, cost) FROM test;
  
  -- The following result is returned:
   min_by
  --------
   cc
  (1 row)

argMax(x, y)

• Description: obtains the value of the x column that corresponds to the maximum value of the y column.

• Implementation in Hologres: max_by(x, y).

  Note

  • If the maximum value of the y column corresponds to multiple different values of the x column, the maximum value in the multiple values of the x column is returned.

  • Hologres V1.3.36 and later support this aggregate function.

• Example: For more information about the CREATE TABLE statement for the table test, see Sample data.

  SELECT id, max_by(name, cost) FROM test GROUP BY id;
  
  -- The following result is returned:
   id | max_by
  ----+--------
    2 | bb
    1 | aaa
    3 | c
  (3 rows)

groupArray(anyelement)

• Description: concatenates the values of an expression into an array.

• Implementation in Hologres: array_agg(anyelement).

• Examples:

  CREATE TABLE test_array_agg_int (c1 int);
  INSERT INTO test_array_agg_int VALUES (1), (2);
  
  SELECT array_agg (c1) FROM test_array_agg_int;
  
  -- The following result is returned:
   array_agg 
  -----------
   {1,2}
  (1 row)

arrayJoin(anyarray)

• Description: expands each array element in a separate row.

• Implementation in Hologres: unnest(anyarray).

• Examples:

  SELECT unnest(ARRAY[1,2]);
  
  -- The following result is returned:
  1
  2

arrayConcat(anyarray, anyarray...)

• Description: concatenates all arrays.

• Implementation in Hologres: array_cat(anyarray,anyarray). You can concatenate two arrays.

• Examples:

  SELECT array_cat(array_cat(ARRAY[1,2], ARRAY[3,4]), ARRAY[5,6]);
  
  -- The following result is returned:
  {1,2,3,4,5,6}

arrayDistinct(anyarray)

• Description: returns a new array that contains only distinct elements after data in an array is deduplicated.

• Implementation in Hologres: array_distinct(anyarray).

  Note

  Hologres V1.3.19 and later support the array_distinct function. This function does not allow you to use constants as input parameters.

• Examples:

  CREATE TABLE test_array_distinct_text ( c1 text[]);
  INSERT INTO test_array_distinct_text
      VALUES (ARRAY['holo', 'hello', 'holo', 'SQL', 'SQL']), (ARRAY[]::text[]);
  
  SELECT c1, array_distinct (c1) FROM test_array_distinct_text;
  
  -- The following result is returned:
              c1             |  array_distinct
  ---------------------------+------------------
   {holo,hello,holo,SQL,SQL} | {SQL,hello,holo}
   {}                        | {NULL}
  (2 rows)

arrayMin(anyarray)

• Description: returns the minimum value of an array.

• Implementation in Hologres: array_min(anyarray).

  Note

  Hologres 1.3.19 and later support the array_min function. This function does not allow you to use constants as input parameters.

• Examples:

  CREATE TABLE test_array_min_text ( c1 text[]);
  INSERT INTO test_array_min_text
      VALUES (NULL), (ARRAY['hello', 'holo', 'blackhole', 'array']);
  
  SELECT c1, array_min (c1) FROM test_array_min_text;
  
  -- The following result is returned:
                c1              | array_min
  ------------------------------+-----------
                                |
   {hello,holo,blackhole,array} | array
  (2 rows)

arrayMax(anyarray)

• Description: returns the maximum value of an array.

• Implementation in Hologres: array_max(anyarray).

  Note

  Hologres 1.3.19 and later support the array_max function. This function does not allow you to use constants as input parameters.

• Examples:

  CREATE TABLE test_array_max_int ( c1 int[]);
  INSERT INTO test_array_max_int VALUES (NULL), (ARRAY[-2, NULL, -3, -12, -7]);
  
  SELECT c1, array_max (c1)FROM test_array_max_int;
  
  -- The following result is returned:
          c1        | array_max
  ------------------+-----------
                    |
   {-2,0,-3,-12,-7} |         0
  (2 rows)

arraySum(anyarray)

• Description: returns the sum of the elements in an array.

• Implementation in Hologres:

  • uunest function

  • func function

  • sum

• Examples:

  SELECT sum(ele) FROM (SELECT unnest(ARRAY[1,2,4]) AS ele) a;
  
  -- The following result is returned:
  7

bitmapToArray(roaringbitmap)

• Description: returns an integer array from which a Roaring bitmap is created.

• Implementation in Hologres: rb_to_array(roaringbitmap).

• Examples:

  SELECT rb_to_array(rb_build('{1,2,3}'));
  
  -- The following result is returned:
  {1,2,3}

groupBitmapState(integer)

• Description: creates a Roaring bitmap from a group of expression values.

• Implementation in Hologres: rb_build_agg(integer).

• Examples:

  SELECT rb_build_agg(1);
  
  -- The following result is returned:
  \x3a3000000100000000000000100000000100

groupBitmap(integer)

• Description: calculates the cardinality of the Roaring bitmap that is created from a group of expression values.

• Implementation in Hologres: rb_cardinality(rb_build_agg(integer)). This implementation consists of two steps. The first step is to run the rb_build_agg function to aggregate the expression values into a Roaring bitmap. The second step is to run the rb_cardinality function to calculate the cardinality.

• Examples:

  SELECT rb_cardinality(rb_build_agg(1));
  
  -- The following result is returned:
  1

groupBitmapAndState(roaringbitmap)

• Description: performs an AND aggregate operation to generate a new Roaring bitmap.

• Implementation in Hologres: rb_and_agg(roaringbitmap).

• Examples:

  SELECT rb_and_agg(rb_build('{1,2,3}'));
  
  -- The following result is returned:
  \x3a300000010000000000020010000000010002000300

groupBitmapOrState(roaringbitmap)

• Description: performs an OR aggregate operation to generate a new Roaring bitmap.

• Implementation in Hologres: rb_or_agg(roaringbitmap).

• Examples:

  SELECT rb_or_agg(rb_build('{1,2,3}'));
  
  -- The following result is returned:
  \x3a300000010000000000020010000000010002000300

groupBitmapAnd(roaringbitmap)

• Description: performs an AND aggregate operation to generate a new Roaring bitmap and returns the cardinality of the new Roaring bitmap.

• Implementation in Hologres: rb_and_cardinality_agg(roaringbitmap).

• Examples:

  SELECT rb_and_cardinality_agg(rb_build('{1,2,3}'));
  
  -- The following result is returned:
  3

groupBitmapOr(roaringbitmap)

• Description: performs an OR aggregate operation to generate a new Roaring bitmap and returns the cardinality of the new Roaring bitmap.

• Implementation in Hologres: rb_or_cardinality_agg(roaringbitmap).

• Examples:

  SELECT rb_or_cardinality_agg(rb_build('{1,2,3}'));
  
  -- The following result is returned:
  3

sipHash64(text)

• Description: returns a 64-bit SipHash value of a text value. This function returns a value of the UInt64 data type.

• Implementation in Hologres: hg_sip_hash_64(text).

  Note

  • A value of the BIGINT type is returned.

  • Hologres V2.0.1 and later support the sipHash64(text) function.

  • This function does not allow you to use constants as input parameters.

• Examples:

  CREATE TABLE test_hg_sip_hash_64_text (c1 text);
  INSERT INTO test_hg_sip_hash_64_text VALUES ('abc');
  
  SELECT hg_sip_hash_64 (c1) FROM test_hg_sip_hash_64_text;
  
  -- The following result is returned:
  4596069200710135518