Ability to insert multiple rows by specifying multiple rows in VALUES?

[carols10cents]

What I'd like to be able to do is something like this:

let categories = vec!["shoes", "socks"];

try!(tx.execute("
    INSERT INTO categories (category)
    VALUES $1",
    &[&&categories[..]]
));

And I expected the SQL generated to be:

INSERT INTO categories (category)
VALUES ('shoes'), ('socks');

The code above compiles, but I get a panic on unwrapping:

Db(DbError { 
    severity: "ERROR", 
    code: SyntaxError, 
    message: "syntax error at or near \"$1\"", 
    detail: None, hint: None, position: Some(Normal(59)), where_: None, schema: None, 
    table: None, column: None, datatype: None, constraint: None, file: Some("scan.l"), 
    line: Some(1087), routine: Some("scanner_yyerror")
})

Which looks to me like the $1 wasn't substituted at all.

Is there a way to do this currently that I'm missing? If not, is this something that you'd want rust-postgres to support, or would it be better as a separate crate, something like activerecord-import?

Thanks!! ❤️

[sfackler]

rust-postgres passes the query string along directly to the postgres backend, which only allows parameters in the place of values, not here where it's looking for a comma-separated list of tuples. You could maybe do something with an array of composite types, but it'd probably be way too complicated to be worth it.

I think an external crate would make sense here, yeah. It'd probably involve generating query strings that look like INSERT INTO categories (category) VALUES ($1), ($2), ($3), ($4), ... for relatively small sets of values, and then switching over to COPY categories (category) FROM STDIN (FORMAT BINARY) using something like https://crates.io/crates/postgres-binary-copy when the value set grows larger.

[carols10cents]

Ok, thank you! I might look into that in my copious free time... thank you for your time! ❤️

[isosphere]

@carols10cents did you find a solution for your needs?

I am doing a row-by-row insert of spreadsheet data into PostgreSQL and I think the overhead of each individual query is slowing me down (~200 million rows). I "think" it would be much faster if I could build larger queries with many rows of data in them but don't currently see a solution for that in rust.