HELP how to fix "Message": "Failure happened on 'Source' side. 'Type=Npgsql.PostgresException,Message=42601: syntax error at or near \"FROMpublic\"\r\n\r\nPOSITION: 10,Source=Npgsql,'"

Question

I created a ADF pipeline to ingest my tables in bulk to azure data lake storage from my postgres database . However while running the pipeline threw me an error : "Message": "Failure happened on 'Source' side. 'Type=Npgsql.PostgresException,Message=42601: syntax error at or near "FROMpublic"\r\n\r\nPOSITION: 10,Source=Npgsql,'"

It seems like this error is pointing to an issue in the query syntax on the source side. I am assuming that source side means the source side of the copy activity within the ForEach activity of my pipeline. Also, when i preview the output of each of the iterations of my ForEach activity, none of the data from the corresponding table in my postgres database was read (0). Granite, some of the tables are unpopulated but the tables are present from a CREATE TABLE statement. Even though some tables are empty, the pipeline shouldn't crash. I am not sure why. I don't see what could be wrong with the query. Does anyone have a solution to fix this?

Answer 1

Hi @Chris

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

Based on your error, it seems like you are facing an issue while running an Azure Data Factory pipeline to ingest tables from your Postgres database to Azure Data Lake Storage. The error message you provided indicates that there is a syntax error in the query on the source side.

To resolve this issue, you may want to check the query syntax in your pipeline and ensure that it is correct. Specifically, you may want to check the query for any syntax errors such as missing or extra characters, incorrect syntax, or incorrect table or column names.

Please try the below query and let me know.

@{concat('SELECT * FROM ', item().Schema_Name, '.', item().Table_Name)}

Additionally, you may want to check the permissions and connectivity settings for your Postgres database to ensure that the pipeline has the necessary permissions to access the database and read the data.

I hope this helps. Please let me know if you have any questions.

Answer 2

Hello @Chris

The error message indicates that there is a syntax error near FROMpublic. This suggests that there might be a missing space between "FROM" and "public" in the query.

To fix this issue, you can try modifying the SQL query used in the copy activity to ensure that it is syntactically correct. You can also try running the query directly in your PostgreSQL database to see if it produces any errors.

Additionally, since you mentioned that some of the tables are unpopulated, you might want to check if the tables have any data in them before running the pipeline.

If the tables are empty, the pipeline might not be able to read any data from them, which could cause the pipeline to fail.

Finally, you might want to check if the connection to your PostgreSQL database is working correctly.