Search code examples
javapostgresqljdbcjooq

Why would PostgreSQL return jsonb for "select *" but PGobject for "select data"?

I'm using Jooq (without codegen) to create SQL queries and sending them to the database. The table I am testing with has only 2 columns, an ID which is a string and a data column that is JSONB.

The interesting thing is that if I generate a query in this format:

select * from test_table where id = 'some_id'

The map I get back has a String and a JSONB in it (as expected, I guess).

However, if I change the query to:

select id, data from test_table where id = 'some_id'

Generated by code like this:

select(field(ID_COLUMN), field(DATA_COLUMN))
  .from(DSL.table("test_table"))
  .where(field(ID_COLUMN).eq(id))
  .fetch().intoMaps();

(for the first example, it's the same, but with asterisk() instead of the two fields)

The map I get back has a String for the ID, but now, the data column is a PGobject containing the JSONB data.

I can deal with either of these types just fine, but I'm hoping to understand what triggered the change and if I can rely on it being one type or the other based on my implementation.

Solution

  • You probably have good reasons for using jOOQ without code generator, which would help with all of these data type related problems (the main good reason is a dynamic schema known only at runtime, where code generation doesn't work), but even if you're not using the code generator, it's a good idea to hint types to jOOQ, explicitly, e.g.

    field(DATA_COLUMN, SQLDataType.JSONB)

    Otherwise, jOOQ won't know what PostgreSQL returns, and it can't make decisions for you. I wouldn't rely on any implicit behaviour of the server or the driver.