Why do I get "JDBC Driver Error: ERROR: cross-database references are not implemented" with a PostgreSQL database?

I am connecting to a PostgreSQL database named 'myDB' from MATLAB. When I query this database from the command line, I am able to get results:

>> conn = database('myDB', 'myUsername', 'myPass', 'Vendor', 'PostgreSQL');
>> select(conn, 'SELECT * from person')

However, when I try to look at this database in Database Explorer, I get an error:

SELECT * FROM myDB.public.person

"JDBC Driver Error: ERROR: cross-database references are not implemented: "tsdatabase.public.person"

Position: 15"

If I include the database name and schema in my command line query like Database Explorer does, I see the same error:

>> select(conn, 'SELECT * from myDB.public.person')

"Error using database.jdbc.connection/select (line 108)

JDBC Driver Error: ERROR: cross-database references are not implemented: "tsdatabase.public.person"

Position: 15"

I know that this table exists, as I am able to query it without specifying the database name and schema. I also know that the schema is correct, as I have checked the schema using 'sqlfind'. What is causing this error?

Answer 1

[MathWorks Support Team]

Please try renaming your database to use all lower-case letters. The lower-case naming convention is suggested by PostgreSQL, and should resolve the error you are seeing in MATLAB.

This error is due to a difference in naming conventions between SQL and PostgreSQL.

According to PostgreSQL's standards, any unquoted names in queries are converted to lower-case letters, so when myDB is a part of the query, PostgreSQL interprets it as mydb instead. Since Database Explorer prepends the database name and schema name to the table name, and since the database name is not all lowercase, PostgreSQL interprets Database Explorer's query for myDB.public.person as mydb.public.person. This raises an error because you are connected to myDB but PostgreSQL thinks you are trying to query mydb, which it interprets as a different database.

The way to enforce capitalization in PostgreSQL and SQL is to surround each case-sensitive name in quotes. For example, if you change the query from

SELECT * FROM myDB.public.person

to

SELECT * FROM "myDB".public.cats

The query should be successful.

Note that this forcing to lower-case is a PostgreSQL-specific behavior. Database Explorer is generally agnostic to the type of database being used, so this behavior is not being accounted for.

You can read more about this behavior in the PostgreSQL documentation:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html

The relevant section is 4.1.1, particularly the last paragraph.