Data type conversion functions

Function

Return value type

Description

Example

Returned result

Remarks

TRY_CAST

• INTEGER, SMALLINT, and BIGINT

• REAL and DOUBLE PRECISION

• BOOLEAN

• DECIMAL

• JSONB

Converts data of the TEXT type into another data type. If the data cannot be converted into the desired data type, this function does not return an error message but returns NULL.

1. Create a table named try_cast_test and write test data to the table.

   -- Create a table named try_cast_test. 
   CREATE TABLE IF NOT EXISTS  try_cast_test (
    key int,
    value text
   );
   
   -- Write test data to the table. 
   INSERT INTO try_cast_test VALUES (1, '1'), (2, 'abc');

2. Use the TRY_CAST function to convert data of the TEXT type into the INT type.

-- Convert data of the TEXT type in the value column into the INT type and display the converted data in the value_int column. 
SELECT
 key,
 value,
 try_cast (value AS INT) as value_int
FROM
 try_cast_test;

 key | value | value_int 
-----+-------+-----------
    1|1      |         1
    2|abc    |\N 
(2 rows)

The data abc in the value column cannot be converted into the INT type. The TRY_CAST function returns NULL.

This function is an additional function of Hologres. The TRY_CAST function can be used only in Hologres V2.2. If the version of your Hologres instance is V2.1 or earlier, contact the Hologres technical support to upgrade your Hologres instance.

to_char(timestamp, text)

TEXT

Converts a timestamp into a string. By default, only timestamps that describe time points in a year from 1925 to 2282 can be converted.

to_char(current_timestamp, 'HH12:MI:SS')

06:26:33

In Hologres V1.1.31 and later, you can execute the set hg_experimental_functions_use_pg_implementation = 'to_char'; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp'; statement before you call this function in an SQL statement. This allows the function to support time points in any year.

to_char(int, text)

TEXT

Converts an integer into a string.

to_char(125, '999')

125

None.

to_char(double precision, text)

TEXT

Converts a real number or a double-precision number into a string.

to_char(125.8::real, '999D9')

125.8

None.

to_date(text, text)

DATE

Converts a string into a date. By default, only strings that describe time points in a year from 1925 to 2282 can be converted.

• to_date('05 Dec 2000', 'DD Mon YYYY')

• to_date('2000 12 05', 'YYYY MM DD')

2000-12-05

In Hologres V1.1.31 and later, you can execute the set hg_experimental_functions_use_pg_implementation = 'to_date' ; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp'; statement before you call this function in an SQL statement. This allows the function to support time points in any year.

to_number(text, text)

NUMERIC

Converts a string into a number.

to_number('12,454.8-', '99G999D9S')

-12454.8

None.

to_timestamp(text, text)

TIMESTAMP

Converts a string into a timestamp. By default, only strings that describe time points in a year from 1925 to 2282 can be converted.

to_timestamp('05 Dec 2000', 'DD Mon YYYY')

2000-12-05 00:00:00

• The return value does not contain +08.

• In Hologres V1.1.31 and later, you can execute the set hg_experimental_functions_use_pg_implementation = 'to_timestamp'; statement before you call this function in an SQL statement. This allows the function to support time points in any year.

array_to_string(anyarray, text [, text])

TEXT

Converts an array into a string.

array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')

1,2,3,*,5

None.

array_agg(expression)

ARRAY

Aggregates input values into an array. This function can be used to convert a string into an array or convert column-oriented data into row-oriented data.

• array_agg(123)

• array_agg(colname)

• {123}

• {1,2}

None.

array_agg (expression[ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

ARRAY

Aggregates input values into an array. This function can be used to convert a string into an array or convert column-oriented data into row-oriented data. You can use a FILTER clause in the function expression.

array_agg(name) filter (where id >3) from f_test

{a5,a6,a7,a7,"{namea,nameb,namec}",a9,a4}

You can use a FILTER clause in the function expression only if the version of your Hologres instance is V1.3 or later.

string_agg(expression)

TEXT

Aggregates non-null input values into a string, separated by a specified delimiter. This function can be used to convert column-oriented data into row-oriented data.

string_agg(colname, '-')

a-b-c

None.

string_agg (expression [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

TEXT

Aggregates non-null input values into a string, separated by a specified delimiter. This function can be used to convert column-oriented data into row-oriented data. You can use a FILTER clause in the function expression.

string_agg(name,',') filter (where id >3) from f_test

a7,a7,a5,a4,{namea,nameb,namec},a9,a6

You can use a FILTER clause in the function expression only if the version of your Hologres instance is V1.3 or later.

regexp_split_to_table(string text, pattern text )

TEXT

Splits a string by using a Portable Operating System Interface (POSIX) regular expression. This function can be used to convert row-oriented data into column-oriented data.

regexp_split_to_table('hello world', '\s+')

hello

world

None.

isnumeric(text)

BOOLEAN

Checks whether all characters in a string are numeric characters.

isnumeric('95.5')

true

This function is supported in Hologres V1.1 and later.

row_to_json

JSON

Converts data in a row into the JSON format.

row_to_json(t) from (select col1,col2 ,...from table) as t

"{"f1":"1","f2":"0"}""{"f1":"0","f2":"0"}""{"f1":"fa","f2":"fb"}""{"f1":"fa","f2":"fb"}"

This function is supported in Hologres V1.3 and later.

row()

ARRAY or TEXT

Returns the number of the row in which a formula is located.

select a,b from row_table where row(a,b) =row(b,a) ;

a    b
0    0

This function is supported in Hologres V1.3 and later.