Syntax error with ON CONFLICT

[Drew Dimanlig]

I'm getting this error trying to execute a query that does ON CONFLICT DO UPDATE:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "INSERT INTO tasks (assignee_id, item_key, title, status, due_date, updated_at, state, company_id) VALUES (?,?,?,?,?,?,?,?) [*]ON CONFLICT (assignee_id, item_key, status) DO UPDATE SET assignee_id = excluded.assignee_id, title = excluded.title, status = excluded.status, item_key = excluded.item_key, status = excluded.status, due_date = excluded.due_date, updated_at = excluded.updated_at, state = excluded.state, company = excluded.company"; SQL statement:
INSERT INTO tasks (assignee_id, item_key, title, status, due_date, updated_at, state, company_id) VALUES (?,?,?,?,?,?,?,?) ON CONFLICT (assignee_id, item_key, status) DO UPDATE SET assignee_id = excluded.assignee_id, title = excluded.title, status = excluded.status, item_key = excluded.item_key, status = excluded.status, due_date = excluded.due_date, updated_at = excluded.updated_at, state = excluded.state, company = excluded.company [42000-214]

Here is my connection string: 

"jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH;INIT=RUNSCRIPT FROM 'schema.sql';"

Is there another setting I'm missing?

[Noel Grandin]

On 1/18/2024 1:04 AM, 'Drew Dimanlig' via H2 Database wrote:
> I'm getting this error trying to execute a query that does ON CONFLICT DO UPDATE:

We don't support that part of the postgres syntax. You are better off using MERGE INTO, which we do support:

https://h2database.com/html/commands.html#merge_into

https://www.postgresql.org/docs/current/sql-merge.html

[Andreas Reichel]

Greetings!

The `ON CONFLICT ...` clause is Postgres specific and certainly not supported in H2.

Please read Postgres' "MODE" as: some specific syntax is supported, but there is no guarantee of full compliance.

Best regards

Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/54ebacf8-dd4f-4132-b332-0d97224596e6n%40googlegroups.com.

[Evgenij Ryazanov]

Hello!

This PostgreSQL-specific clause is not supported by H2. You need to use the MERGE command from the SQL Standard:

https://www.postgresql.org/docs/current/sql-merge.html

https://h2database.com/html/commands.html#merge_using