H2 Database Postgres Mode Upsert

Question

When H2 database is in Postgres Mode, how do I check if it supports following statement (upsert / on conflict)

INSERT INTO event_log_poller_state (aggregate_type, consumer_group_id, value) 
VALUES (?, ?, ?) 
on conflict (aggregate_type, consumer_group_id) " +
                    " do update SET VALUE = ? 
WHERE
  event_log_poller_state.aggregate_type = ? AND
  event_log_poller_state.consumer_group_id = ?

I get syntax error 42000

I're written following program to demonstrate

https://github.com/tonymurphy/h2-postgres

Edit Oct 2019: Checkout https://github.com/whisklabs/docker-it-scala or testcontainers.org

seems like it's just not possible.. shame postgres doesn't have an in memory version for test purposes — Tony Murphy, Jul 27 '16 at 07:59
I now use docker and testcontainers.org or https://github.com/whisklabs/docker-it-scala — Tony Murphy, Oct 24 '19 at 10:22
Recently there was a [merge](https://github.com/h2database/h2database/pull/2008) for supporting this in H2, but ON CONFLICT seems to still fail for me on version 1.4.200 — Jethro, Jan 03 '20 at 10:33
That merge is actually just a small change to support ON CONFLICT DO NOTHING. Unfortunately if you try adding the constraint (e.g. on conflict(id)) or adding anything other than 'NOTHING' (e.g. update) it will fail. — Lucas, Jun 23 '20 at 13:20

score 0 · Answer 1 · answered Aug 26 '22 at 14:51

There is merge which should do the same: https://www.tutorialspoint.com/h2_database/h2_database_merge.htm

MERGE INTO tableName [ ( columnName [,...] ) ] 
[ KEY ( columnName [,...] ) ] 
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select }

See also https://github.com/h2database/h2database/issues/2007#issuecomment-1152312049

H2 Database Postgres Mode Upsert

1 Answers1