Is there a way to cast an unsupported data type in a data stream to a string type?

[brownbear]

In PostgreSQL, we have tables that use domain types. When we perform CDC on these tables using a data stream, the data of the domain type is being processed as null. We need to somehow retrieve these values. Is it possible to convert these domain types to string types or other types to retrieve the values?

Are there any plans or schedules to release a feature for users to cast unsupported types?

[ms4446 · Accepted solution]

Datastream does not currently offer a built-in mechanism to cast unsupported data types, such as PostgreSQL domain types, within the stream itself. However, there are effective workarounds and potential future enhancements to consider.

One workaround involves using a PostgreSQL function to convert domain type values to strings before Datastream captures the changes. By creating a PostgreSQL function that performs the type conversion and modifying the tables to include a computed column using this function, Datastream can capture the string representation of the domain type values. For example, you can create a casting function as follows:

CREATE OR REPLACE FUNCTION cast_domain_to_text(my_domain_value my_domain_type)
RETURNS TEXT AS $$
BEGIN
    RETURN my_domain_value::TEXT;
END;
$$ LANGUAGE plpgsql;

After defining this function, you can modify your table to add a computed column:

ALTER TABLE your_table
ADD COLUMN domain_value_as_text TEXT GENERATED ALWAYS AS (cast_domain_to_text(your_domain_column)) STORED;

When using BigQuery as the target, additional flexibility is available. Data from Datastream can be loaded into BigQuery staging tables, where SQL functions like CAST and SAFE_CAST can be used to convert the domain types to strings.

Implementing these workarounds, it is important to consider the impact on database performance. Adding computed columns and functions can affect performance, so monitoring the PostgreSQL instance is essential. Additionally, ensure that all possible values of the domain type are correctly handled in the casting function.