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]
[*]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; theEXCLUDEDvirtual table is something from PostgreSQL'sON CONFLICTclause, it cannot be used in a standardMERGEcommand.