New Synapse/ADF Postgres Linked Service does not work with 'time without time zone' datatype.

Question

The current legacy linked service driver supports tables that use the 'time without timezone' datatype.

The new linked service driver gives the following error when trying to read tables with this datatype:

''Type=System.InvalidCastException,Message=Can't cast database type time without time zone to NpgsqlInterval,Source=Npgsql,'

Switching back to the 'Legacy' driver that is no longer available as of Oct. 2024 solves the issue.

This happens when using a 'Copy Activity' in a normal pipeline.

Answer 1

Hello Vike King,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that your new Synapse/ADF Postgres Linked Service does not work with 'time without time zone' datatype.

I have a few potential solutions to address this problem:

1. Modify your query to cast the 'time without time zone' datatype to a compatible format before it reaches the Copy Activity. For example, you can cast it to a string or another time-related datatype that the new connector can handle.

    SELECT column_name::text AS column_name FROM your_table;

2. Use a Data Flow in Azure Data Factory or Synapse to transform the data before it is copied. This allows you to handle the datatype conversion within the data flow.
3. If the issue persists, consider using a self-hosted integration runtime where you can have more control over the drivers and their configurations. This might allow you to use a different driver that handles the 'time without time zone' datatype correctly.
4. Since this is a known issue with the new driver, raising a support ticket with Microsoft might help. They can provide a more tailored solution or a potential fix in future updates.
5. Alternatively, if feasible, consider using other tools or services that might offer better compatibility with your specific PostgreSQL setup.

I hope these steps are helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.