Hologres:ClickHouse functions

toString(anyelement)

Converts a value of any type to TEXT.

SELECT toString(id) FROM public.tb1;

1234

toInt64(anyelement)

Converts a numeric value (other than BIGINT) to BIGINT.

SELECT toInt64(id_text) FROM public.tb1;

1234

toInt32(anyelement)

Converts a numeric value (other than INT) to INT.

SELECT toInt32(id_text) FROM public.tb1;

123

toDate(text)

Converts a TEXT value to DATE. The input must be in a valid date format; otherwise, an error is returned.

Successful conversion:

SELECT toDate(data_date) FROM public.tb1;

2019-01-02

Failed conversion (incompatible format):

SELECT toDate(data_text) FROM public.tb1;

ERROR: *** invalid input syntax for type date ***

toFloat64(anyelement)

Converts a numeric value (other than DOUBLE PRECISION) to DOUBLE PRECISION.

SELECT toFloat64(data_float) FROM public.tb1;

1.23

toYear, toMonth, and toQuarter

Extracts the year, month, or quarter from a timestamp. Use extract(field FROM timestamp) or date_part(text, timestamp). For details, see Datetime truncation functions.

Extract the month:

SELECT extract(month FROM timestamp '2001-02-16 20:38:40');

2

Extract the quarter:

SELECT extract(quarter FROM timestamp '2001-02-16 20:38:40');

1

addDays, addMonths, and addYears

Adds a time interval to a date or timestamp using the + operator.

SELECT date '2001-09-28' + interval '1 hour';

2001-09-28 01:00:00

subtractDays, subtractMonths, and subtractYears

Subtracts a time interval from a date or timestamp using the - operator.

SELECT date '2001-09-28' - interval '1 day';

2001-09-27 00:00:00

argMin(x, y)

Returns the value of x that corresponds to the minimum value of y. If the minimum y maps to multiple x values, the minimum of those x values is returned.

Requires Hologres V1.3.36 or later. For the sample table definition, see Sample data.

SELECT min_by(name, cost) FROM test;

 min_by
--------
 cc
(1 row)

argMax(x, y)

Returns the value of x that corresponds to the maximum value of y. If the maximum y maps to multiple x values, the maximum of those x values is returned.

Requires Hologres V1.3.36 or later. For the sample table definition, see Sample data.

SELECT id, max_by(name, cost) FROM test GROUP BY id;

 id | max_by
----+--------
  2 | bb
  1 | aaa
  3 | c
(3 rows)

groupArray(anyelement)

Concatenates all values of an expression into an array. Use array_agg(anyelement).

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;

 array_agg
-----------
 {1,2}
(1 row)

arrayJoin(anyarray)

Expands each array element into a separate row. Use unnest(anyarray).

SELECT unnest(ARRAY[1,2]);

1
2

arrayConcat(anyarray, anyarray...)

Concatenates arrays. Use array_cat(anyarray, anyarray). Because array_cat accepts two arrays at a time, chain multiple calls to concatenate more than two arrays.

SELECT array_cat(array_cat(ARRAY[1,2], ARRAY[3,4]), ARRAY[5,6]);

{1,2,3,4,5,6}

arrayDistinct(anyarray)

Returns a new array with duplicate elements removed. Use array_distinct(anyarray).

Requires Hologres V1.3.19 or later. Constants cannot be used as input parameters.

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;

            c1             |  array_distinct
---------------------------+------------------
 {holo,hello,holo,SQL,SQL} | {SQL,hello,holo}
 {}                        | {NULL}
(2 rows)

arrayMin(anyarray)

Returns the minimum value in an array. Use array_min(anyarray).

Requires Hologres 1.3.19 or later. Constants cannot be used as input parameters.

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;

              c1              | array_min
------------------------------+-----------
                              |
 {hello,holo,blackhole,array} | array
(2 rows)

arrayMax(anyarray)

Returns the maximum value in an array. Use array_max(anyarray).

Requires Hologres 1.3.19 or later. Constants cannot be used as input parameters.

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;

        c1        | array_max
------------------+-----------
                  |
 {-2,0,-3,-12,-7} |         0
(2 rows)

arraySum(anyarray)

Returns the sum of all elements in an array. Use unnest to expand the array into rows, then apply sum.

SELECT sum(ele) FROM (SELECT unnest(ARRAY[1,2,4]) AS ele) a;

7

bitmapToArray(roaringbitmap)

Converts a Roaring bitmap to an integer array. Use rb_to_array(roaringbitmap).

SELECT rb_to_array(rb_build('{1,2,3}'));

{1,2,3}

groupBitmapState(integer)

Builds a Roaring bitmap from a group of integer values. Use rb_build_agg(integer).

SELECT rb_build_agg(1);

\x3a3000000100000000000000100000000100

groupBitmap(integer)

Calculates the cardinality of a Roaring bitmap built from a group of integer values. Use rb_cardinality(rb_build_agg(integer))—rb_build_agg first aggregates the values into a Roaring bitmap, then rb_cardinality counts the distinct elements.

SELECT rb_cardinality(rb_build_agg(1));

1

groupBitmapAndState(roaringbitmap)

Performs an AND aggregate operation across Roaring bitmaps and returns the resulting bitmap. Use rb_and_agg(roaringbitmap).

SELECT rb_and_agg(rb_build('{1,2,3}'));

\x3a300000010000000000020010000000010002000300

groupBitmapOrState(roaringbitmap)

Performs an OR aggregate operation across Roaring bitmaps and returns the resulting bitmap. Use rb_or_agg(roaringbitmap).

SELECT rb_or_agg(rb_build('{1,2,3}'));

\x3a300000010000000000020010000000010002000300

groupBitmapAnd(roaringbitmap)

Performs an AND aggregate operation across Roaring bitmaps and returns the cardinality of the resulting bitmap. Use rb_and_cardinality_agg(roaringbitmap).

SELECT rb_and_cardinality_agg(rb_build('{1,2,3}'));

3

groupBitmapOr(roaringbitmap)

Performs an OR aggregate operation across Roaring bitmaps and returns the cardinality of the resulting bitmap. Use rb_or_cardinality_agg(roaringbitmap).

SELECT rb_or_cardinality_agg(rb_build('{1,2,3}'));

3

sipHash64(text)

Returns a 64-bit SipHash value for a text input. In ClickHouse, the return type is UInt64; in Hologres, it is BIGINT. Use hg_sip_hash_64(text).

Requires Hologres V2.0.1 or later. Constants cannot be used as input parameters.

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;

4596069200710135518