Foreign Catalog with Case Sensitive PostgreSQL

MC8D
New Contributor II

‎10-18-2023 08:33 PM

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?

0 Kudos

MC8D
New Contributor II

‎10-20-2023 12:34 AM

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