cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Foreign Catalog with Case Sensitive PostgreSQL

MC8D
New Contributor II

I am trying to query my postgresql read replica as a foreign catalog.

  • I can sucessfuly test the connection.
  • I can see the database names.
  • The table names are auto populated correctly.

However when I try to view or query a table, I get the following error. (Names redacted)

Failed to request /ajax-api/2.1/unity-catalog/tables/<foreign_catalog>.<db>.<table>?include_browse=true: 404 Table '<foreign_catalog>.<db>.<table>' does not exist.

 When I query this database via python, I usually need to use case sensitive names, and I suspect that is the issue.

eg:

  • Foreign Catalog table appears as "table".
  • In PostgreSQL I would usually query it as 'select * from "Table"'

Any ideas of if case sensitive postgresql is already solved with Foreign Catalogs or what the problem might be?

1 REPLY 1

MC8D
New Contributor II

Hi @Retired_mod 

I am able to query the pg_catalog database which has all lower case table names, so the connection is working.

I am unable to query the tables in my "public" schema, as they have capitalization in the table names.

If I query with no backticks or quotes, the lowercase and capitalized table name both give me this error.

If I query with quotes, I get this error

  • [PARSE_SYNTAX_ERROR] Syntax error at or near '"<table>"'.line 1, pos 39

If I query with backticks, I get this error

  • [TABLE_OR_VIEW_NOT_FOUND] The table or view <foreign_catalog>.public.<table> cannot be found. Verify the spelling and correctness of the schema and catalog. If you did not qualify the name with a schema, verify the current_schema() output, or qualify the name with the correct schema and catalog. To tolerate the error on drop use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS.; line 1, pos 14

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group