Inserting multiple values at the same time

[MaikKlein]

    conn.execute("INSERT INTO person (name, data) VALUES ($1, $2)",
                 &[&me.name, &me.data]).unwrap();

This is a single insert. What should I do if I want to insert multiple values into the database at the same time? Until now I just used a simple for loop with a prepared statement but this is relatively slow.

At least from the postgres sql docs this seems possible

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

How should I do it? Should I create a really long sql query or is there a better way to insert multiple values at the same time?

[sfackler]

The simple option is to build up a query string dynamically based on how many things you want to insert that looks like: INSERT INTO person (name, data) VALUES ($1, $2), ($3, $4), ($5, $6).

The more complex option is to use a COPY query. This takes some more work to set up but is great if you need to insert a ton of data: https://docs.rs/postgres/0.15.2/postgres/stmt/struct.Statement.html#method.copy_in. You can use the postgres-binary-copy crate to handle the data encoding for you: https://docs.rs/postgres-binary-copy/0.5.0/postgres_binary_copy/

[MaikKlein]

Thanks!

[oceanlewis]

I'm running into this issue as well - my main painpoint is, as I discovered today, Redshift doesn't support the COPY query, so mass inserts are kind of painful if I have to potentially insert 100s of rows at a time with 10+ fields.

[drewhk]

Hm, so using the dynamic statement approach I cannot create a &[&dyn ToSql] slice no matter how I try from my Vec<Record>. Anyone knows how to do this?

[vdwees]

While not the same as this issue, the answer to @drewhk (and my future self)'s question: #133 (comment)

[michaelmenon]

Sorry to open this again I am using this code to insert multiple values, is there a better option :

let members = &[obj] //obj is a struct
let mut params = Vec::<&(dyn ToSql + Sync)>::new();
let mut i = 1;
let mut qry:String = "insert into tablename(id,userid,usertype) values".to_string();
for column in members{
    if(i ==1){
        qry = format!("{} (${},${},${})",qry,i,i+1,i+2);
    }else{
        qry = format!("{}, (${},${},${})",qry,i,i+1,i+2);

    }
    params.push(&column.id);
    params.push(&column.userid);
    params.push(&column.usertype);
    i = i+3;
               
}
println!("qry : {}",qry);
let result = p.execute(&qry, &params[..]).await; //p is the pool manager

[raine]

FWIW, sqlx has a query builder approach to address this issue.

launchbadge/sqlx#294 (comment)