How can I use aws glue to insert data into a custom data type column in rds postgres

Question

I'm using aws glue's visual etl builder to extract data from one rds postgres DB, transform it, then insert it into another rds postgres db (similar business entities but different schema).

The destination db has a custom enum data type. I'm trying to fill it in the glue job using a derived column transformation that sets a string value. Glue isn't supporting filling this string into the custom data type column and is giving me the following error:

(for privacy, I replaced column and type names with ...)

```
An error occurred while calling o234.pyWriteDynamicFrame. ERROR: column "..." is of type "enum_..." but expression is of type character varying

```

What's the best way to fill this custom type? Is using a derived column transformation the right way?

Answer

That error is really coming from the Postgres JDBC driver, not Glue. 
By default it will send string columns as VARCHAR and that's what the error is rejecting.  
You can change that behavior if you set in the connection properties (or in the JDBC url) stringtype=unspecified that means that the server will try to figure out how to use the string. Check the documentation for for information: https://jdbc.postgresql.org/documentation/use/

Answer

you can use **write_dynamic_frame.from_jdbc_conf** with **connection_options** to specify **stringtype": "unspecified"**

Sample_Node1 = glueContext.write_dynamic_frame.from_jdbc_conf(
    frame=Sample_Node2,
    transformation_ctx="Sample_Node1",
    catalog_connection = '{jdbc_connection_name}',
    connection_options = {"dbtable": "{data table path}", "database": "{database name}", **"stringtype": "unspecified"**},
)

aws docs: https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-crawler-pyspark-extensions-dynamic-frame-writer.html

How can I use aws glue to insert data into a custom data type column in rds postgres

Relevant content