Re: [h2] Syntax error with ON CONFLICT
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 On Wed, 2024-01-17 at 15:04 -0800, 'Drew Dimanlig' via H2 Database wrote: > 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;I > NIT=RUNSCRIPT FROM 'schema.sql';" > > Is there another setting I'm missing? > > > -- > 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+unsubscr...@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 > . -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/68f5f539bd6c0e7174dbb50851682748ead5f04a.camel%40manticore-projects.com.
Re: [h2] Syntax error with ON CONFLICT
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 -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/043d5749-3bb7-451e-89e8-595ea7e0e8bc%40gmail.com.
[h2] Syntax error with ON CONFLICT
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? -- 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+unsubscr...@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.
