jOOQ does not translate SQL properly for H2 database in PostgreSQL mode

Question

The problem is with Postgres ON CONFLICT syntax.

Versions (maven dependencies):

• postgresql: 42.2.9
• jooq: 3.12.3
• h2database: 1.4.200

// mocking connection
final Connection connection = DriverManager.getConnection("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;Mode=PostgreSQL", "sa", "");
final Settings settings = new Settings().withRenderNameStyle(RenderNameStyle.AS_IS);
Mockito.doReturn(DSL.using(connection, SQLDialect.POSTGRES, settings)).when(mockDbConn).getDSLContext();

// java code for upsert:
dc.insertInto(MY_TABLE)
    .columns(MY_TABLE.TOKEN, MY_TABLE.NAME, MY_TABLE.EMAIL)
    .values(token, name, email)
    .onDuplicateKeyUpdate()
    .set(MY_TABLE.EMAIL, email)
    .execute();

getting the following error log (seems that problem could be because of [*] (line 4 ↓). I cannot understand why it appears and how to remove it):

-- Syntax error in SQL statement:
INSERT INTO PUBLIC.MY_TABLE (TOKEN, NAME, EMAIL)
VALUES (?, ?, ?)
ON CONFLICT ([*]TOKEN, NAME) -- line 4
DO UPDATE SET EMAIL = EXCLUDED.EMAIL;

-- expected "DO";
-- SQL statement:
insert into public.my_table (token, name, email)
values (?, ?, ?)
on conflict (token, name)
do update set email = excluded.email;

-- [42001-200]

Here is what happens if I am switching dialect from SQLDialect.POSTGRES to SQLDialect.H2:

-- Column "EXCLUDED.EMAIL" not found; SQL statement:
merge into public.my_table using (select 1 one)
on (public.my_table.token = cast(? as varchar) and public.my_table.name = cast(? as varchar))
when matched then update set public.my_table.email = excluded.email
when not matched then insert (token, name, email)
values (cast(? as varchar), cast(? as varchar), cast(? as varchar))

-- [42122-200]

Answer 1

You're mixing 3 dialects in your jOOQ API usage:

1. The SQLDialect.MYSQL dialect, which is the dialect that produced the onDuplicateKeyUpdate() syntax. This can be emulated on a variety of dialects, but it's usually better to use the native syntax (SQL standard MERGE if available, or ON CONFLICT in PostgreSQL).
2. The SQLDialect.POSTGRES dialect, which is your production target dialect
3. The SQLDialect.H2 dialect, which you're using to integration test things

That's a lot of complexity given the fact that you're probably only targeting PostgreSQL as a production database product. I strongly suggest you use testcontainers for your integration testing, which will allow you to remove H2 from the equation. Also, once you've settled for PostgreSQL only as a target dialect, you could avoid the onDuplicateKeyUpdate() syntax, and use jOOQ's native onConflict() syntax support for more predictable results.

If you continue to mix the above 3 dialects, you will often run into some limitation where either jOOQ or H2 cannot emulate the syntax you're using. This is only an acceptable situation if you actually have to support the 3 dialects in production.