Jooq INSERT...ON CONFLICT DO UPDATE...RETURNING doesn't return value or catch the database exception

Question

Using Jooq 3.13.5, Postgresql 10.

The expectation is that the insert query will insert the row into the database table if it doesn't already exist and return the id column regardless.

Table:

CREATE descriptor_table ( id serial primary key, name text unique );

Working SQL query:

INSERT INTO descriptor_table (name)
  VALUES('a name')
ON CONFLICT("name")
  DO UPDATE SET name=EXCLUDED.name
RETURNING id;

Java code:

DSLContext dsl = jooqProvider.getDSLContext();
dsl.transaction(configuration -> {
  MyRecord                                                             
      idValue =                                                              
      DSL.using(configuration)                                                 
         .insertInto(DESCRIPTOR_TABLE)                                               
         .set(DESCRIPTOR_TABLE.NAME, "a name")                            
         .onConflict(Keys.DESCRIPTOR_TABLE_NAME_KEY.getFieldsArray())                
         .doUpdate()                                                           
         .set(DESCRIPTOR_TABLE.NAME, "EXCLUDED.name")                                
         .where(DESCRIPTOR_TABLE.NAME.eq("a name")))                       
         .returning(DESCRIPTOR_TABLE.ID)                                             
         .fetchOne();
}

The resulting MyRecord value is null.

score 3 · Answer 1 · answered Oct 23 '20 at 06:38

Your string "EXCLUDED.name" is just a string bind variable, not an expression. It's no different from setting the NAME column to e.g. "Mike" or like your other bind variable "a name".

If you want a plain SQL expression, use DSL.field(String, DataType) or a similar overload:

.set(DESCRIPTOR_TABLE.NAME, field("EXCLUDED.name", DESCRIPTOR_TABLE.NAME.getDataType()))

Note that your jOOQ query has a where() clause, unlike your SQL query...

Jooq INSERT...ON CONFLICT DO UPDATE...RETURNING doesn't return value or catch the database exception

1 Answers1