ktorm-support-postgresql/org.ktorm.support.postgresql/bulkInsertOrUpdate

bulkInsertOrUpdate

fun <T : BaseTable<*>> Database.bulkInsertOrUpdate(table: T, block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit): Int(source)

Bulk insert records to the table, determining if there is a key conflict while inserting each of them, and automatically performs updates if any conflict exists.

Usage:

database.bulkInsertOrUpdate(Employees) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = t_employee.salary + ?
Content copied to clipboard

By default, the column used in the on conflict statement is the primary key you already defined in the schema definition. If you want, you can specify one or more columns for the on conflict statement as belows:

database.bulkInsertOrUpdate(Employees) {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = t_employee.salary + ?
Content copied to clipboard

Since

3.3.0

Return

the effected row count.

Parameters

table

the table to be inserted.

block

the DSL block used to construct the expression.

fun <T : BaseTable<*>> T.bulkInsertOrUpdate(block: BulkInsertOrUpdateStatementBuilder<T>.(T) -> Unit): Int(source)

Deprecated

ktorm-global will be removed in the future, please migrate to the standard API.

Bulk insert records to the table, determining if there is a key conflict while inserting each of them, and automatically performs updates if any conflict exists.

Usage:

Employees.bulkInsertOrUpdate {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (id) do update set salary = salary + ?
Content copied to clipboard

By default, the column used in the on conflict statement is the primary key you already defined in the schema definition. If you want, you can specify one or more columns for the on conflict statement as belows:

Employees.bulkInsertOrUpdate {
    item {
        set(it.id, 1)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    item {
        set(it.id, 5)
        set(it.name, "vince")
        set(it.job, "engineer")
        set(it.salary, 1000)
        set(it.hireDate, LocalDate.now())
        set(it.departmentId, 1)
    }
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}
Content copied to clipboard

Generated SQL:

insert into t_employee (id, name, job, salary, hire_date, department_id)
values (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)
on conflict (name, job) do update set salary = salary + ?
Content copied to clipboard

Since

3.3.0

Return

the effected row count.

Parameters

block

the DSL block used to construct the expression.

See also