Bulk inserts into table

Question

Let's say i have one struct

pub struct Student {
    name: String,
    age: i32,
}

I have list of Student type & i want to insert it into postgres table in single query.

I am following this (UNNEST()) it has egs for primitive type list but not for structs.

My code snippet

sqlx::query!(
    "INSERT INTO students(name, age) SELECT * FROM UNNEST($1::text[], $2::int8[])",
    &students[..]
)
    .execute(&pool)
    .await.unwrap();

What should i change in above code snippet to insert multiple rows at once?

score 0 · Answer 1 · answered Jul 12 '23 at 20:34

Like in the article you linked you have to pass multiple slices of primitives instead of one slice of complex objects:

let (names, ages): (Vec<&String>, Vec<&i32>) =
    students.iter().map(|Student { name, age }| (name, age));
sqlx::query!(
    "INSERT INTO students(name, age) SELECT * FROM UNNEST($1::text[], $2::int8[])",
    &names[..],
    &ages[..]
)
.execute(&pool)
.await
.unwrap();

Bulk inserts into table

1 Answers1