Azure datafactory copy activity fails when using postgresql geometry data type

Question

I'm trying to copy an Azure Database for PostgreSQL table from one schema to another using azure data factory copy activity. But I'm getting the following error. This table has a "geometry" data type column. Then I tried the same thing with other tables with "geometry" data columns and still got the same error. But when I try to copy data from my local PostgreSQL database to Azure Database for PostgreSQL it works without any issue. This problem only occurs when an Azure Database for PostgreSQL table is used as the source in the copy activity.

Azure Database for PostgreSQL database has PostGIS extension installed.
The table preview option is also not working with "geometry" data. But again this error only occurs with Azure Database for PostgreSQL. My local PostgreSQL database works with ADF without any issue.

What could be the reason for this? Does ADF not support "geometry" data type when using Azure Database for PostgreSQL database?

Answer 1

Hello @72617270 ,

Thanks for the question and using MS Q&A platform.

Looks like by default, copy does not support PostGis types: NpgsqlTypes.PostgisPoint/NpgsqlTypes.PostgisLineString/NpgsqlTypes.Postgis….

Workaround:

1. But as an alternative, we could try converting those Geometry columns as text by selecting the Query option in your source instead of Table.
   For example: geom is of geometry data type column in your table, then please try writing query in Copy activity source by adding additional ::text to column of geometry type.
   Sample query looks like: SELECT name, geom::text FROM geometries;
2. Another alternative I could think of enabling a Staged copy in your copy activity. When you activate the staging feature, first the data is copied from the source data store to the staging storage (bring your own Azure Blob or Azure Data Lake Storage Gen2). Next, the data is copied from the staging to the sink data store. The copy activity automatically manages the two-stage flow for you, and also cleans up temporary data from the staging storage after the data movement is complete.

Hope this info helps. Do let us know how it goes.

Thank you

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators