JOOQ fails with PostgreSQL Custom Type as an Array: ERROR: malformed record literal

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

Recommended topics

Hot tags