H2 support for INSERT IGNORE

[dcapwell]

java.lang.UnsupportedOperationException: There's no generic SQL for INSERT IGNORE. There must be vendor specific implementation

	at org.jetbrains.exposed.sql.vendors.VendorDialect.insert(Default.kt:241)
	at org.jetbrains.exposed.sql.vendors.H2Dialect.insert(H2.kt:44)
	at org.jetbrains.exposed.sql.statements.InsertStatement.prepareSQL(InsertStatement.kt:43)
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_main(Statement.kt:46)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:103)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:97)
	at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:27)
	at org.jetbrains.exposed.sql.QueriesKt.insertIgnore(Queries.kt:72)

I tried out Exposed because I needed to support both H2 and Postgres, and wanted the ability to do a logical insert if not exists, which is different for both DBs; below is the code I tried to write

private val clusterId: Int = transaction {
            Clusters.insertIgnore {
                it[name] = this@ExposedCluster.name
            }

            // name has a unique constraint so first() is safe
            Clusters.slice(Clusters.name).selectAll().first()[Clusters.id]
        }

My guess at what insertIgnore means was insert if not exists so expected the H2 version to produce a MERGE statement as follows

MERGE INTO CLUSTERS(name) KEY(name) VALUES('$name')

[Tapac]

@dcapwell, MERGE has different semantic then INSERT IGNORE and is more like a REPLACE.
ATM replace also doesn't work with H2, but i guess it's possible to fix both cases (insertIgnore = insert..on duplicate key update, replace = merge)

[coderfengyun]

@Tapac how can i config "(insertIgnore = insert..on duplicate key update, replace = merge)"
?

[cdietze]

FWIW, H2 supports INSERT IGNORE since Version 1.4.197 (2018-03-18), see changelog.

[spyro2000]

Still getting error on IGNORE with 1.4.200...

[micHar]

Oh, just came to mention that I also get that on 1.4.199.

Specifically:

org.jetbrains.exposed.exceptions.UnsupportedByDialectException: INSERT IGNORE supported only on H2 v1.4.197+ with MODE=MYSQL., dialect: h2.

Postgre supports insert ignore for some time, is there a reason it's only supported with mysql?

[Rubyj]

@Tapac same for me as the comment above ^ any ideas?