Kotlin Exposed batch insert not working as documented

Question

I am trying to batch insert records into an SQL table using Kotlin Exposed. I have set up the code as per the Exposed documentation, however, the SQL statements being executed are individual insert statements rather than 1 batch insert statement.

The documentation located here: https://github.com/JetBrains/Exposed/wiki/DSL has the following on Batch Inserting:

Batch Insert

Batch Insert allow mapping a list of entities into DB raws in one sql statement. It is more efficient than inserting one by one as it initiates only one statement. Here is an example:

    val cityNames = listOf("Paris", "Moscow", "Helsinki")
    val allCitiesID = cities.batchInsert(cityNames) { name ->
      this[cities.name] = name
    }

My code is as follows:

    val mappings: List<Triple<String, String, String>> = listOf(triple1, triple2, triple3)
    transaction {
        TableName.batchInsert(mappings) {
            this[TableName.value1] = it.first
            this[TableName.value2] = it.second
            this[TableName.value3] = it.third
        }
    }

What I expect to see printed out is 1 batch insert statement which follows the syntax of

INSERT INTO TableName (value1, value2, value3) values
(triple1value1, triple1value2, triple1value3),
(triple2value1, triple2value2, triple2value3),
(triple3value1, triple3value2, triple3value3), ...

but instead it prints 3 individual insert statements with the following syntax

INSERT INTO TableName (value1, value2, value3) values (triple1value1, triple1value2, triple1value3)
INSERT INTO TableName (value1, value2, value3) values (triple2value1, triple2value2, triple2value3)
INSERT INTO TableName (value1, value2, value3) values (triple3value1, triple3value2, triple3value3)

As this seems like the documented correct way to batch insert, what am I doing incorrectly here?

Tremulous

Please look at smilar question here – Picky

Answer 1

The docs explain:

NOTE: The batchInsert function will still create multiple INSERT statements when interacting with your database. You most likely want to couple this with the rewriteBatchedInserts=true (or rewriteBatchedStatements=true) option of your relevant JDBC driver, which will convert those into a single bulkInsert. You can find the documentation for this option for MySQL here and PostgreSQL here.

https://jetbrains.github.io/Exposed/deep-dive-into-dsl.html#batch-insert

Ranitta

Answer 2

I had experienced the same issue and made rewriteBatchedInserts as true. In order to see the actual logs, you have to pull out raw logs from pgsql as exposed will still show it separately.

Stalactite