Foreign Catalog with Case Sensitive PostgreSQL

[MC8D]

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?

[Kaniz]

Hi @MC8D,  When you use double quotes with a PostgreSQL identifier, this makes it case-sensitive in PostgreSQL's eyes. This means you must use the same case when referencing the table in your Foreign Catalog queries or use double quotes.

Could you please try this:

%sql
SELECT * FROM "<foreign_catalog>"."<db>"."<Table>";

[MC8D]

Hi @Kaniz 

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