3

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]
1
  • 2
    [*] is just a mark that H2 appends to a query to indicate a place in the SQL that contains a first parsing error. You should use the H2 dialect for H2 in jOOQ, the compatibility modes of H2 provide a very limited compatibility, H2 is not an emulator of other databases. But the query that jOOQ produces for H2 is not valid too; the EXCLUDED virtual table is something from PostgreSQL's ON CONFLICT clause, it cannot be used in a standard MERGE command. Commented Jan 10, 2020 at 15:44

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.