pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
10+ messages / 3 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-02-03 13:58 "aditsu (@aditsu)" <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: aditsu (@aditsu) @ 2026-02-03 13:58 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**Describe the issue**
ResultSetMetaData.getColumnClassName does not return the correct class name for columns of type jsonb.
Specifically, the javadoc says it should return "the fully-qualified name of the class in the Java programming language that would be used by the method ResultSet.getObject to retrieve the value in the specified column"
The method returns "java.lang.String" in recent driver versions, whereas ResultSet.getObject returns instances of org.postgresql.util.PGobject, and that class does not extend java.lang.String.
This is not a problem for the "json" pg type, which is handled perfectly. Only "jsonb" is broken.
**Driver Version?**
42.7.9 (but I have tested older versions too)
**Java Version?**
17.0.13
**OS Version?**
Linux
**PostgreSQL Version?**
17.7
**To Reproduce**
Here is my table:
```
CREATE TABLE public.jsontest (
id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
fld_json json NOT NULL,
fld_jsonb jsonb NOT NULL,
CONSTRAINT jsontest_pk PRIMARY KEY (id)
);
```
I inserted one row with some dummy data.
kotlin test code:
```
import java.sql.DriverManager
import java.util.Properties
fun main() {
val url = "jdbc:postgresql://localhost/test"
val props = Properties().apply {
setProperty("user", "postgres")
setProperty("password", "")
}
DriverManager.getConnection(url, props).use { conn ->
val sql = "SELECT id, fld_json, fld_jsonb FROM jsontest"
val version = conn.metaData.driverVersion
println("driver version: $version")
conn.createStatement().use { statement ->
statement.executeQuery(sql).use { rs ->
val metaData = rs.metaData
while (rs.next()) {
for (i in 2..metaData.columnCount) {
val name = metaData.getColumnName(i)
val type = metaData.getColumnTypeName(i)
val cl = metaData.getColumnClassName(i)
val value = rs.getObject(i)
println("$name - type $type, metadata class $cl, value ${value.javaClass}")
println("instance test: " + Class.forName(cl).isInstance(value))
}
}
}
}
}
}
```
**Expected behaviour**
I expected the metadata class match the class of the returned value, or at least to be a supertype.
More specifically, I expected getColumnClassName to return "org.postgresql.util.PGobject" like it does for json.
It actually returns "java.lang.String" in recent driver versions, which would still be ok (though very weird and inconsistent with the json type) **IF** the value was actually returned as a String from getObject, but it is not - the actual value is a PGobject.
Actual results:
I tested this with several different driver versions, and got mainly 2 different outputs:
```
driver version: 42.2.0
fld_json - type json, metadata class org.postgresql.util.PGobject, value class org.postgresql.util.PGobject
instance test: true
fld_jsonb - type jsonb, metadata class java.lang.Object, value class org.postgresql.util.PGobject
instance test: true
```
Old versions:
type json - perfect match: PGobject to PGobject
type jsonb - inconsistent but not broken: Object to PGobject
```
driver version: 42.7.9
fld_json - type json, metadata class org.postgresql.util.PGobject, value class org.postgresql.util.PGobject
instance test: true
fld_jsonb - type jsonb, metadata class java.lang.String, value class org.postgresql.util.PGobject
instance test: false
```
New versions:
type json - perfect match: PGobject to PGobject
type jsonb - actually broken: String to PGobject
Proposed solution (preferred): getColumnClassName should return "org.postgresql.util.PGobject" like it does for json.
Alternative solution (inferior): getColumnClassName should return "java.lang.String" for both json and jsonb, but at the same time getObject should be modified to return a String value.
See also: bug #2153 - I don't really understand what happened there, but it seems to me that it was broken on purpose?! Very bizarre.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-03-10 02:49 "dkozlov-de (@dkozlov-de)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: dkozlov-de (@dkozlov-de) @ 2026-03-10 02:49 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I vote for the fix to this problem. This is clearly a bug. Metadata must return correct class data, otherwise, it is absolutely useless. This issue breaks any libraries that provide dynamic data type resolution based on the column metadata. At some point jsonb started being treated as a String. Well, that was a developers' call (even though it is still not clear to me what the reason for that change was). But if that (changing PGobject to String) was done in metadata, retrieving the column data would have to be changed accordingly to return a String instead of PGobject.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-03-10 12:11 "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2026-03-10 12:11 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Well the reason is that it actually does return a string.
Can you help me out, what would your code do if it found jsonb ? How would you use this data ?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-03-10 13:34 "dkozlov-de (@dkozlov-de)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: dkozlov-de (@dkozlov-de) @ 2026-03-10 13:34 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I would like to clarify my point first. I do not have any problems with treating JSONB column data as a String. It is your call and I respect it. The problem is with resultset still returning PGobject.
We use org.apache.commons.beanutils.RowSetDynaClass and related classes to dynamically retrieve typed data from a resultset. This library uses column metadata to figure out what the type of a column is. Then when we ask for the column data, it tries to cast the data in resultset to the type provided by the metadata. At this point it fails because metadata says it is a String, but what the resultset returns is still PGobject.
My point is that if you change the type reported by metadata to String, you should be consistent and change the real type the resultset returns to String as well. Otherwise, the metadata column class information is wrong/misleading and prevents from proper data evaluation.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-03-10 13:37 "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2026-03-10 13:37 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
ah, thanks for the clarification.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-03-10 16:59 "aditsu (@aditsu)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: aditsu (@aditsu) @ 2026-03-10 16:59 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@dkozlov-de I fully agree, I would only add that I think json and jsonb should be handled in a similar fashion by the driver. Either both PGobject (my preference) or both String (also acceptable).
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-03-11 13:02 "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2026-03-11 13:02 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Interestingly, this has been this way for 10 years see https://github.com/pgjdbc/pgjdbc/pull/640
given the comments on PR#640 I'm inclined to return the jsonb type as PGObject
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-03-11 13:45 "dkozlov-de (@dkozlov-de)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: dkozlov-de (@dkozlov-de) @ 2026-03-11 13:45 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@davecramer - thank you very much for the quick response. Yes, this could be a very old issue. We were still using pretty old version of the driver and decided to upgrade it. That's when we got this problem exposed.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-03-11 16:49 "aditsu (@aditsu)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: aditsu (@aditsu) @ 2026-03-11 16:49 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I've used json for a long time and was familiar with how the driver returns PGobject, but only started using jsonb very recently and was shocked to find the discrepancy.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb
@ 2026-03-12 12:49 "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2026-03-12 12:49 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@vlsi what do you think of committing this?
I'm thinking we need to do a Major version bump as this changes the current behaviour
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-03-12 12:49 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-03 13:58 [pgjdbc/pgjdbc] issue #3926: ResultSetMetaData.getColumnClassName is broken for jsonb "aditsu (@aditsu)" <[email protected]>
2026-03-10 02:49 ` "dkozlov-de (@dkozlov-de)" <[email protected]>
2026-03-10 12:11 ` "davecramer (@davecramer)" <[email protected]>
2026-03-10 13:34 ` "dkozlov-de (@dkozlov-de)" <[email protected]>
2026-03-10 13:37 ` "davecramer (@davecramer)" <[email protected]>
2026-03-10 16:59 ` "aditsu (@aditsu)" <[email protected]>
2026-03-11 13:02 ` "davecramer (@davecramer)" <[email protected]>
2026-03-11 13:45 ` "dkozlov-de (@dkozlov-de)" <[email protected]>
2026-03-11 16:49 ` "aditsu (@aditsu)" <[email protected]>
2026-03-12 12:49 ` "davecramer (@davecramer)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox