Hi team! I've tried to sync a postgres table that contains a column of

geography

(postgis) dataype, using tap-postgres (MeltanoLabs) and target-postgres (MeltanoLabs). • The column is created at target as a string with a warning on the datatype, instead of

geography

. • Is there a recommended way to do this? ◦ I've tried giving target schema hints but looks like Geography datatype is just not handled by the target. I've tried other variants. ◦ I've also tried casting using stream_maps but with no luck. Thank you!

Hi @Marcelo Mallmann! It's not supported by either tap or target at the moment. There are some things that need to happen for that: 1. The tap needs to detect the geography type (https://github.com/MeltanoLabs/tap-postgres/blob/17184d3f79af328a51140277e1fdad26eae45753/tap_postgres/client.py#L94-L100) and emit an appropriate JSON representation 2. We'd need to know the JSON representation of a

geography

value, and make the target aware of it with something like https://sdk.meltano.com/en/v0.49.1/guides/sql-target.html#use-the-x-sql-datatype-json-schema-extension 3. We probably want to make all of this optional, maybe with an extra dependency on geoalchemy.

Thank you Edgar for your answer. I am thinking through a few workarounds, but so far have only considered having a database trigger to convert values from that column (the string at the target can be converted to the geography type using a Postgres function; although we would like to avoid creating a db trigger that could get lost upon table drop). If you or anyone has other ideas, would appreciate it. Thanks!