Transformations: Postgres custom objectid datatype transformation
Answered
My company is currently syncing a postgres rds table and dropping a number of columns because they're unsupported custom datatypes. They are equivalently a MongoDB ObjectId (string + regex), which we've had no issues syncing via our Mongo connector. We still use Mongo as our primary production data store and incrementally transititioning to postgres, so it's important to be able to maintain a compatibility with the old environment (rather than change these columns to a native postgres type). Support here would be much appreciated!
-
Official comment
Hey Isaac, thanks for your question here. Could you elaborate on the custom data type you are using? Are these Postgres User-defined Types (https://www.postgresql.org/docs/13/xtypes.html) that have various subtypes within it?
If so, how would you want this data to land in your destination? Would it be a JSON type with each subtype as a property? Or would you expect each subtype to be parsed out into its own column?
Please let me know! I will be sure to discuss this with our team.
-
Thanks for your response Kevin! These columns are not actually custom data types, but custom domains. I apologize for the confusion -- was conflating what's in the source table with the Fivetran connector error logs.
The table in question doesn't use anything special to create the domain:
`create domain objectid as text check ( value ~ '^[0-9a-f]{24}$' );` We'd expect them to be cast to a text or varchar at the Fivetran sync stage. That's how it loads for our MongoDB connector anyway and has worked fine.
Please sign in to leave a comment.
Comments
2 comments