Preview data of Azure Database for PostgreSQL

Dirk Sachse 126

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:

User's image

What can be the issue here?

Amira Bedhiafi 25,181 Reputation points

2024-10-11T21:26:17.22+00:00

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.
phemanth 10,735 Reputation points Microsoft Vendor

2024-10-14T04:32:01.2433333+00:00

@Dirk Sachse

Thanks for reaching out to Microsoft Q&A

The error message “42P01: relation ‘core.a’ does not exist” typically indicates that Azure Data Factory (ADF) is unable to find the specified table in the database, even though you confirmed that it exists.

Case Sensitivity: PostgreSQL is case-sensitive when it comes to identifiers. If your schema or table names were created with quotes (e.g., "core" or "a"), you need to reference them in the same case in ADF. Try using the exact case as defined in your database.

Database Context: Ensure that the linked service in ADF is pointing to the correct database where your schema and table reside. Double-check the connection string and database name.

Permissions: Verify that the user account used in the linked service has the necessary permissions to access the schema and table. You may need to grant SELECT privileges on the table to that user.

Schema Name: Make sure you are entering the schema name correctly in ADF. Sometimes, a leading or trailing space can cause issues.

Refresh Metadata: In ADF, try refreshing the dataset or re-creating it to ensure that it picks up the latest schema changes.

Network Issues: Occasionally, network issues can cause temporary connectivity problems. Ensure that your ADF can connect to the Azure Database for PostgreSQL without interruptions.

If you’ve checked all these points and the issue persists, please do let us know
Dirk Sachse 126 Reputation points

2024-10-14T08:38:04.11+00:00

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).

I did that and it was identical. I created table "a", just to make sure.

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.

I did that too. I currently use an admin-user, because I am just testing.

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.

Did that aswell.

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?
phemanth 10,735 Reputation points Microsoft Vendor

2024-10-15T07:46:13.6833333+00:00
@Dirk Sachse

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.
phemanth 10,735 Reputation points Microsoft Vendor

2024-10-15T07:57:47.3333333+00:00

@Dirk Sachse Glad to know your issue has been resolved. 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.

Accepted answer

phemanth 10,735 Reputation points Microsoft Vendor

2024-10-15T07:53:18.38+00:00
@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.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Preview data of Azure Database for PostgreSQL

0 additional answers

Your answer