Hologres is compatible with PostgreSQL and allows you to use the standard PostgreSQL syntax to develop data. This topic describes the data type conversion functions supported by Hologres and sample statements of these functions.

The following table describes the data type conversion functions that are supported by Hologres. Hologres supports some of the PostgreSQL data type conversion functions. For more information about PostgreSQL data type conversion functions, see Data Type Formatting Functions.
Function Return type Description Example Return value Remarks
to_char(timestamp, text) TEXT Converts TIMESTAMP to STRING. 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 run the set hg_experimental_functions_use_pg_implementation = 'to_char'; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp'; command before you invoke this function in an SQL statement. This allows the function to support all time values.
Note If you run the preceding commands, the query performance degrades by about 50%. In Hologres V1.1.42 and later, the query performance degrades by about 20%.
to_char(int, text) TEXT Converts INTEGER to STRING. to_char(125, '999') 125 N/A
to_char(double precision, text) TEXT Converts REAL or DOUBLE PRECISION to STRING. to_char(125.8::real, '999D9') 125.8 N/A
to_date(text, text) DATE Converts STRING to DATE. Only strings that describe dates 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 run the set hg_experimental_functions_use_pg_implementation = 'to_date'; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp'; command before you invoke this function in an SQL statement. This allows the function to support all time values.
Note If you run the preceding commands, the query performance degrades by about 50%. In Hologres V1.1.42 and later, the query performance degrades by about 20%.
to_number(text, text) NUMERIC Converts STRING to NUMERIC. to_number('12,454.8-', '99G999D9S') -12454.8 N/A
to_timestamp(text, text) TIMESTAMP Converts STRING to TIMESTAMP. 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 result does not contain +08.
  • In Hologres V1.1.31 and later, you can run the SET hg_experimental_functions_use_pg_implementation = 'to_timestamp'; command before you invoke this function in an SQL statement. This allows the function to support all time values.
Note If you run the preceding command, the query performance degrades by about 50%. In Hologres V1.1.42 and later, the query performance degrades by about 20%.
array_to_string(anyarray, text [, text]) TEXT Converts ARRAY to STRING. array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') 1,2,3,*,5 N/A
array_agg(expression) ARRAY Concatenates input values into an array. This function can be used to convert STRING to ARRAY and column-oriented data to row-oriented data.
  • array_agg(123)
  • array_agg(colname)
  • {123}
  • {1,2}
 N/A
string_agg(expression) TEXT Concatenates non-null input values into a string, separated by a specified separator. This function can be used to convert column-oriented data to row-oriented data. string_agg(colname, '-') a-b-c N/A
regexp_split_to_table(string text, pattern text ) TEXT Splits STRING by using a POSIX regular expression as the delimiter. This function can be used to convert row-oriented data to column-oriented data. regexp_split_to_table('hello world', '\s+')

hello

world

 N/A
isnumeric(text) BOOLEAN Checks whether all characters in a string are numeric characters. isnumeric('95.5') true This function is supported as of Hologres V1.1.