ktorm-support-postgresql/org.ktorm.support.postgresql/insertOrUpdate

insertOrUpdate

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

Insert a record to the table, determining if there is a key conflict while it's being inserted, and automatically performs an update if any conflict exists.

Usage:

database.insertOrUpdate(Employees) {
    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)
    onConflict {
        set(it.salary, it.salary + 900)
    }
}

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 + ?

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.insertOrUpdate(Employees) {
    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)
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}

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 + ?

Since

2.7

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.~~insertOrUpdate~~(block: InsertOrUpdateStatementBuilder.(T) -> Unit): Int(source)

Deprecated

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

Insert a record to the table, determining if there is a key conflict while it's being inserted, and automatically performs an update if any conflict exists.

Usage:

Employees.insertOrUpdate {
    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)
    onConflict {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

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

Employees.insertOrUpdate {
    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)
    onConflict(it.name, it.job) {
        set(it.salary, it.salary + 900)
    }
}

Generated SQL:

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

Return

the effected row count.

Parameters

block

the DSL block used to construct the expression.