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

Unprincipled

seems like it's just not possible.. shame postgres doesn't have an in memory version for test purposes – Unprincipled

I now use docker and testcontainers.org or github.com/whisklabs/docker-it-scala – Unprincipled

Recently there was a merge for supporting this in H2, but ON CONFLICT seems to still fail for me on version 1.4.200 – Brachylogy

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. – Dianadiandra

Is this still not supported in 2022? – Tyrant

The two answers below give a good workaround, but unfortunately that won't work if you're trying to use PostgreSQL in production and H2 in tests using the same SQL statement. – Limbate

Answer 1

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

Bogbean

Can you provide a conversion of the SQL in the OP's question into MERGE INTO syntax? – Intake