JOOQ fails with PostgreSQL Custom Type as an Array

Question

I have the following custom type on Postgres:

CREATE TYPE my_custom_type AS (
 field_a VARCHAR,
 field_b NUMERIC(10,3)
 );

and the following table:

CREATE TABLE my_table
(
 COL1 VARCHAR(120) NOT NULL,
 CUSTOM_COLUMN  my_custom_type,
 CUSTOM_COLUMN_ARRAY my_custom_type[]
);

Everything works fine when I use my custom type with JOOQ:

@Test
public void testWithoutArray(){
    MyTableRecord record = dsl.newRecord(MyTable.MY_TABLE);

    record.setCol1("My Col1");
    MyCustomType customType = new MyCustomType();
    customType.setFieldA("Field A Val");
    customType.setFieldB(BigDecimal.ONE);
    record.setCustomColumn(customType);

    record.store();
}

However, when I try to set some value in the field mapped to a custom type array, I have the following error:

@Test
public void testWithArray(){
    MyTableRecord record = dsl.newRecord(MyTable.MY_TABLE);

    record.setCol1("My Col1");
    MyCustomTypeRecord customType = new MyCustomTypeRecord();
    customType.setFieldA("Field A Val 1");
    customType.setFieldB(BigDecimal.ONE);

    MyCustomTypeRecord customType2 = new MyCustomTypeRecord();
    customType2.setFieldA("Field A Val 2");
    customType2.setFieldB(BigDecimal.TEN);

    record.setCustomColumnArray(new MyCustomTypeRecord[]{customType, customType2});
    record.store();
}

org.jooq.exception.DataAccessException: SQL [insert into "my_table" ("col1", "custom_column_array") values (?, ?::my_custom_type[]) returning "my_table"."col1"]; ERROR: malformed record literal: "my_custom_type"(Field A Val 1, 1)"
 Detail: Missing left parenthesis.
    at org.jooq.impl.Utils.translate(Utils.java:1553)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:571)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:347)
    at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:176)
    at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:142)
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:123)
    at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:137)
    at org.jooq.impl.UpdatableRecordImpl.store0(UpdatableRecordImpl.java:185)
    at org.jooq.impl.UpdatableRecordImpl.access$000(UpdatableRecordImpl.java:85)
    at org.jooq.impl.UpdatableRecordImpl$1.operate(UpdatableRecordImpl.java:135)
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:123)
    at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:130)
    at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:123)

The query generated by JOOQ debugg is the following:

DEBUG [main] org.jooq.tools.LoggerListener#debug:255 - Executing query          : insert into "my_table" ("col1", "custom_column_array") values (?, ?::my_custom_type[]) returning "my_table"."col1"
DEBUG [main] org.jooq.tools.LoggerListener#debug:255 - -> with bind values      : insert into "my_table" ("col1", "custom_column_array") values ('My Col1', array[[UDT], [UDT]]) returning "my_table"."col1"

Am I missing some configuration or is it a bug?

Cheers

Answer 1

As stated in the relevant issue (https://github.com/jOOQ/jOOQ/issues/4162), this is a missing piece of support for this kind of PostgreSQL functionality. The answer given in the issue so far is:

Unfortunately, this is an area where we have to work around a couple of limitations of the PostgreSQL JDBC driver, which doesn't implement SQLData and other API (see also pgjdbc/pgjdbc#63).

Currently, jOOQ binds arrays and UDTs as strings. It seems that this particular combination is not yet supported. You will probably be able to work around this limitation by implementing your own custom data type Binding:

http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/