Using try_convert in Yandex MPP Analytics for PostgreSQL

try_convert is a Yandex Cloud extension which works in the same way as the TRY_CONVERT function and converts data from one type to another. This extension enables type conversions when there are errors in the input data. Without try_convert, such conversions are performed in pl/pgsql, resulting in increased CPU consumption.

The extension only supports the standard Greenplum® data types. To use other data types, add them using the add_type_for_try_convert function.

Here is an example:

SELECT add_type_for_try_convert('hstore'::regtype);

For more information about the extension, see this try_convert guide.

Installing try_convert in a Greenplum® clusterInstalling try_convert in a Greenplum® cluster

1. Connect to the database as the owner or a user with the CREATE permission in the database and run this command:

   CREATE EXTENSION try_convert;
   

2. Make sure the extension has been installed:

   SELECT extname FROM pg_extension;
   

   This will bring up the list of extensions installed in the database.

Use casesUse cases

1. Convert text that is convertible to a number:

   SELECT TRY_CONVERT('42'::text, 0::int2);
   

   The returned result is 42.

2. Convert text that is not convertible to a number:

   SELECT TRY_CONVERT('42d'::text, 1234::int2);
   

   The returned result is 1234.