Hologres:Data type conversion function

Limits

The TRY_CAST function is supported only in Hologres V2.2 and later.

TRY_CAST

• Syntax

  TRY_CAST( <source_string_expr> AS <target_data_type> )

• The following table describes the parameters in the preceding syntax.

  Parameter	Data type	Description
  source_string_expr	TEXT	The column whose data type you want to convert.
  target_data_type	INTEGER, SMALLINT, and BIGINT REAL and DOUBLE PRECISION BOOLEAN DECIMAL JSONB DATE TIMESTAMP TIMESTAMPTZ Note Only Hologres V3.0 and later support the DATE, TIMESTAMP, and TIMESTAMPTZ data types.	The data type into which you want to convert the data. Note If a value in the specified column cannot be converted into the specified data type, NULL is returned.

• Example

  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;

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

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