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

Hi @Vike King ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As per my understanding, you are trying to use postgre linked service in ADF pipeline , but receiving this error: "Can't cast database type time without time zone to NpgsqlInterval".

Yes, from the list of datatypes available for PostGre SQL, it seems 'time without timezone' datatype option is not available for new connector, only 'time with timezone' and 'timestamp with timezone' is available.

You could try altering the table to any of the above datatype or consider casting the column to Date datatype and then try copying the data using copy activity in ADF pipeline.

Reference: How to solve postgresql ERROR: cannot cast type time without timezone to date

Kindly let us know how it goes. Thankyou

Answer 2

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.