Preview data of Azure Database for PostgreSQL

Question

I have created an Azure Database for PostgreSQL in Azure Portal. I created a database, a schema and a table. I also created a linked service and a dataset in Azure Datafactory.

If I go on this dataset, enter schema name and table name, and then I click on Preview data, I get this error:

42P01: relation "core.a" does not exist

As you can see here, the schema and table do exist:

What can be the issue here?

Answer 1

@Dirk Sachse

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask: I have created an Azure Database for PostgreSQL in Azure Portal. I created a database, a schema and a table. I also created a linked service and a dataset in Azure Datafactory.

If I go on this dataset, enter schema name and table name, and then I click on Preview data, I get this error:

42P01: relation "core.a" does not exist

As you can see here, the schema and table do exist:

What can be the issue here?

Solution:The funny thing is, I dont know why, I ve changed nothing and now I can see the data etc.

Could that be a caching issue or something? Arent tables/data written immediately?

Possible Reasons for the Delay:

Caching: ADF might cache metadata or query results, which can sometimes lead to discrepancies if changes are made rapidly. This could explain why you initially encountered the error but later saw the data without making any changes. Transaction Commit Delay: While PostgreSQL typically commits changes immediately, there can be brief delays in visibility, especially in cloud environments where multiple services interact. Service Propagation: Sometimes, changes in one service (like your PostgreSQL database) may take a moment to propagate to another service (like ADF). Try

• Refresh Metadata: If you encounter similar issues in the future, refreshing the dataset or linked service in ADF can help ensure you’re working with the latest data.
• Monitor Performance: Keep an eye on performance and connectivity, especially if you’re working in a testing environment.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

PostgreSQL treats unquoted table and schema names as lowercase by default. If your schema or table name was created with uppercase letters or mixed case, you need to ensure that you use quotes around them in your dataset configuration.

For example, if your schema name is Core and table name is A, you should enter it as "Core"."A" in the dataset configuration in ADF.

Double-check that you are using the correct schema and table name in the dataset configuration. Verify the schema and table names are exactly as they appear in PostgreSQL (case-sensitive).

Run a query like SELECT * FROM information_schema.tables WHERE table_name = 'a' AND table_schema = 'core'; to verify that the schema and table exist.

Verify also that the user associated with the linked service in Azure Data Factory has the necessary permissions to access the schema and table. The user should have SELECT privileges on the specified schema and table.

Sometimes tables or schemas may not be fully committed or available immediately. Check if you can query the table directly from a PostgreSQL client (like pgAdmin or psql) to ensure it's accessible.