Foreign Catalog with Case Sensitive PostgreSQL

MC8D — Thu, 19 Oct 2023 03:33:57 GMT

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?

Re: Foreign Catalog with Case Sensitive PostgreSQL

MC8D — Fri, 20 Oct 2023 07:34:51 GMT

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.

Error: [TABLE_OR_VIEW_NOT_FOUND] The table or view <foreign_catalog>.public.<table> cannot be found.

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

topic Re: Foreign Catalog with Case Sensitive PostgreSQL in Data Engineering

Foreign Catalog with Case Sensitive PostgreSQL

Re: Foreign Catalog with Case Sensitive PostgreSQL