Subject: [pgjdbc/pgjdbc] issue #3722: spring jdbc causes problems with uppercase db names since 42.7.5

From: "frederikz (@frederikz)" <[email-masked]>
Date: Tue, 08 Jul 2025 10:31:57 +0000

I'm using spring jdbc which lookups up table metadata with ``DatabaseMetaData.getTables`` and then uses the returned information to get column metadata via ``DatabaseMetaData.getColumns``.
Before 42.7.5 the table metadata returned no value for ``TABLE_CAT`` and so the query for column metadata also didn't include a catalog.
Since 42.7.5 the table metadata returns a value for ``TABLE_CAT`` e.g. ``abC``. Spring doesn't use this value directly. It queries ``DatabaseMetaData.storesLowerCaseIdentifiers`` which returns true and as it returns true ``abC`` is transformed to ``abc``. The query for column metadata is now done for a catalog with the name ``abc`` which doesn't return any value as an exact case match is done agains the current database name which is ``abC`` and not ``abc``.

I have also reported the issue to the spring team https://github.com/spring-projects/spring-framework/issues/35064 as I'm unsure which side is causing the problem.
Why does ``DatabaseMetaData.storesLowerCaseIdentifiers`` return true when database names are not treated case insensitive or if it returns true why do methods like ``DatabaseMetaData.getColumns`` not accept lowercase names for a database?
I would expect ``DatabaseMetaData.storesLowerCaseIdentifiers`` to return false or methods like ``DatabaseMetaData.getColumns`` to accecpt a lowercase database name even if the database name was created with uppercase letters.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 09 Jul 2025 12:39:18 +0000

`DatabaseMetaData.storesLowerCaseIdentifiers` refers to normal queries. https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getTables-java.lang.String-java.lang.String-java.lang.String-java.lang.String:A- specifically states that columns are returned in UPPERCASE. I do see an argument where we would lowercase them, but the de facto standard seems to be to return them in UPPERCASE

---

From: "frederikz (@frederikz)" <[email-masked]>
Date: Wed, 09 Jul 2025 13:29:12 +0000

Of course, you have to retrieve the results of ``DatabaseMetaData.getColumns`` with upper case column names like ``TABLE_CAT`` but that wasn't the point. The point is that the value returned by the ``TABLE_CAT`` column is case sensitive, So for my database ``abC`` it returns exactly that value ``abC`` and that value has to be used unchanged to call other methods like ``DatabaseMetaData.getColumns``. 
I would understand that ``DatabaseMetaData.storesLowerCaseIdentifiers`` doesn't apply to these metadata methods but then the spring team is using it wrong.
So your understanding of the API is that the return values of ``DatabaseMetaData.getTables`` like catalog have to be used as they are when then querying other metadata methods like ``DatabaseMetaData.getColumns`` and settings like ``DatabaseMetaData.storesLowerCaseIdentifiers`` do not apply to them? It would be nice if you could state something like that, so I could contact the Spring team again. On the postgres side it would then be only somewhat unfortunate that in a minor release we see a change of the value for ``TABLE_CAT`` (previously returning nothing since 42.7.5 returning a value) that causes a problem in other products.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 31 Jul 2025 10:11:53 +0000

Sorry for my late reply.

As to the change being in a minor release. It's somewhat fuzzy. We considered it a bug as we were just fixing it to adhere to the spec.

As to storesLowerCaseIdentifiers: This is what the javadoc says:
```

boolean | storesLowerCaseIdentifiers() | Retrieves whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in lower case.

boolean | Retrieves whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in lower case.
boolean | Retrieves whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in mixed case.
boolean | Retrieves whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in mixed case.

boolean	[storesLowerCaseIdentifiers](https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#storesLowerCaseIdentifiers--)()	
Retrieves whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in lower case.
boolean	[storesLowerCaseQuotedIdentifiers](https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#storesLowerCaseQuotedIdentifiers--)()	
Retrieves whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in lower case.
boolean	[storesMixedCaseIdentifiers](https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#storesMixedCaseIdentifiers--)()	
Retrieves whether this database treats mixed case unquoted SQL identifiers as case insensitive and stores them in mixed case.
boolean	[storesMixedCaseQuotedIdentifiers](https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#storesMixedCaseQuotedIdentifiers--)()	
Retrieves whether this database treats mixed case quoted SQL identifiers as case insensitive and stores them in mixed case.

AFAICT, we are correct in saying  storesLowerCaseIdentifiers = true as that is what PostgreSQL does.

Seems the problem may be with spring

---

From: "frederikz (@frederikz)" <[email-masked]>
Date: Thu, 31 Jul 2025 10:35:30 +0000

Thanks for your reply and confirming that you think the implementation is according to the spec. Based on your reply the spring team already changed their code and their lastest version works again even with newer pgjdbc drivers.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 31 Jul 2025 22:11:09 +0000

> Thanks for your reply and confirming that you think the implementation is according to the spec. Based on your reply the spring team already changed their code and their lastest version works again…

cool! Thanks for the reply