pgocaml

pgocaml copied to clipboard

Hello,

I would like to know if it is possible to insert a list of values in only one query.

Here is a single insert.

let name = "insert" in
let query = "INSERT INTO tag (a, b, c) VALUES ($1, $2, $3) RETURNING id" in
lwt () = PGOCaml.prepare dbh ~query ~name () in
let params = ["a_value"; "b_value"; "c_value"] in
lwt ids = PGOCaml.execute dbh ~name ~params () in

And can we imagine to insert a list of an arbitrary size such :

let name = "insert" in
let query = "INSERT INTO tag (a, b, c) VALUES ($1) RETURNING id" in
lwt () = PGOCaml.prepare dbh ~query ~name () in
let params = [("a_value1", "b_value1", "c_value1"); ("a_value2", "b_value2", "c_value2")] in
lwt ids = PGOCaml.execute dbh ~name ~params () in

The above method does not work, but does it exist any other way ?

Kind regards, Arnaud

How do you think about to add a function or class method which will perform the needed iteration through the provided list so that the execution of the corresponding prepared SQL statement will be a bit more convenient?

It looks like there is still no way to do bulk imports, correct?

I managed to do this but it's obviously hacky and quite limited 😕️

let insert_many_people names_to_split =
    [%pgsql
      db
        "INSERT INTO people (name)
         SELECT x FROM UNNEST(STRING_TO_ARRAY($names_to_split, '---'))x
        "]
in
insert_many_people "John---Mary---Donald---Peter";

This should work:

let insert_many_people names =
  let names = List.map (fun x -> Some x) names in
    [%pgsql
      db
        "INSERT INTO people (name)
         SELECT x FROM UNNEST($names :: text[])
        "]
in
insert_many_people ["John"; "Mary"; "Donald"; "Peter"]

And if you have several columns:

let insert_many_people names =
  let names = List.map (fun x -> Some x) names in
  let ages = List.map (fun x -> Some x) ages in
    [%pgsql
      db
        "INSERT INTO people (name, age)
         SELECT x FROM UNNEST($names :: text[], $ages :: bigint[])
        "]
in
insert_many_people ["John"; "Mary"; "Donald"; "Peter"] [42; 33; 27; 51]

Many thanks @vouillon!

This technique is also mentioned in a similar rust project's FAQ: https://github.com/launchbadge/sqlx/blob/main/FAQ.md#how-can-i-bind-an-array-to-a-values-clause-how-can-i-do-bulk-inserts

It works great!!