Facing an issue with JDBC sink connector ENUM type

currently I’m trying to synchronise data from one Postgres table to another PG table using Debezium source and jdbc sink connectors. I have column with ENUM data type on both source and sink tables. When ENUM type column data transferred from source database to Kafka via Debezium it has been transformed into string and writing on to the topic.

And then the jdbc sink connector cannot convert them back to enum . So are there any ways to migrate enum type from a postgres to another?

I am using JSON schema converter and I also tried with AVRO .But facing similar issue in both connectors .

Could anyone suggest the workaround?

Thankyou

@raja, first of all, welcome to this community :blush:

I’m afraid the limitation here is in the internal data types supported by Kafka Connect. Regardless of how Debezium reads the data from the Postgres table, it needs to create an internal representation of the schema using what Kafka Connect supports, which is dictated by the Schema class. The following data types are supported:

  • INT8, INT16, INT32, and INT64
  • FLOAT32 and FLOAT64
  • BOOLEAN
  • STRING
  • BYTES
  • ARRAY
  • MAP
  • STRUCT

Debezium is transforming this into a STRING in the first place, according to its basic data types rules. This makes sense as Kafka Connect does not support enums. Therefore, your best bet would be writing a custom converter to handle this edge case along with the JDBC sink connector.

@riferrei

2 Likes

One possible workaround would be to define a DB function to cast string to enum.
That way even if the sink connector passes the field as a string, your function would cast it back to enum. At least that’s the high level idea. I’m not sure whether in practice that will work in the case of string->enum in Postgres. I know a similar problem (string->jsonb) can be solved that way. See https://github.com/confluentinc/kafka-connect-jdbc/issues/643#issuecomment-625638970

1 Like

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.