Execute an insert or update using Diesel

Question

I am trying to execute an insert or update using Diesel with PostgreSQL.

I have tried:

diesel::insert_into($table::table).values(&objects).on_conflict($table::id).do_update().set(&objects).execute(conn).unwrap();

where objects is a std::vec::Vec<Struct> - which results in the compiler error:

^^^ the trait 'diesel::query_builder::AsChangeset' is not implemented for '&std::vec::Vec<Struct>'

There is a on_conflict_do_nothing() in the query builder but I can't seem to find something like on_conflict_do_update() or on_conflict_do_replace().

Shepmaster · Accepted Answer · 2019-01-06T01:55:38.377

31

Diesel 1.3.3's documentation already has examples for using an upsert:

Set specific value on conflict

diesel::insert_into(users)
    .values(&user2)
    .on_conflict(id)
    .do_update()
    .set(name.eq("I DONT KNOW ANYMORE"))
    .execute(&conn);

Set AsChangeset struct on conflict

diesel::insert_into(users)
    .values(&user2)
    .on_conflict(id)
    .do_update()
    .set(&user2)
    .execute(&conn);

Use excluded to get the rejected value

diesel::insert_into(users)
    .values(&vec![user2, user3])
    .on_conflict(id)
    .do_update()
    .set(name.eq(excluded(name)))
    .execute(&conn)

IncompleteDoUpdate::set takes any value that implements AsChangeset, which &Vec<T> does not. Thus it is invalid to pass it as an argument to set.

edited Jan 06 '19 at 01:55

answered Dec 04 '17 at 03:29

Shepmaster

388,571
95
1,107
1,366

1

Thanks a lot! Took me a while to realize that I could use `excluded` using the `id` - this works: `diesel::insert_into($table::table).values(&objects).on_confl‌ict($table::id).do_u‌pdate().set($table::‌id.eq(excluded($tabl‌e::id))).execute(con‌n).unwrap` – Ronny Dec 04 '17 at 09:14
hey, and do you know how to make this works for the sqlite? – privalou Aug 01 '21 at 21:48

score 0 · Answer 2 · answered Jan 26 '22 at 02:17

In case you need to specify how to update multiple columns on conflict, the set function accepts a tuple.

For example:

use diesel::pg::upsert::excluded;

let user = User { id: 1, name: "Pascal", age: 18 };
let user2 = User { id: 1, name: "Sean", age: 21 };
let user3 = User { id: 2, name: "Tess", age: 25 };

assert_eq!(Ok(1), diesel::insert_into(users).values(&user).execute(&conn));

let insert_count = diesel::insert_into(users)
    .values(&vec![user2, user3])
    .on_conflict(id)
    .do_update()
    .set((
        name.eq(excluded(name)),
        age.eq(excluded(age)),
    ))
    .execute(&conn);

See: https://docs.diesel.rs/diesel/fn.update.html#examples

Execute an insert or update using Diesel

2 Answers2