How to: Insert into postgres table with column of type `t[]`

[dangdennis]

I couldn't figure out the "correct" caqti way to encode a sql arg of array type, so here's a workaround.

For those that needs to insert a list of floats (or ints) into a postgres col of type double precision[], you can concat your list of floats into a string. That'll suffice.

Example: I have a [1.0; 5.1; 0.3]. I transform the array into a string value of { 1.0, 5.1, 0.3 }. Use Caqti_type.string as the corresponding type.

Here's some working code:

(* our sql helper *)
let to_sql_array_arg (xs : float list) =
  let stringified_elements = List.map ~f:(fun x -> Float.to_string x) xs in
  "{" ^ (stringified_elements |> String.concat ~sep:", ") ^ "}"
;;

let update_array_col
  ~db:(module Db : Caqti_lwt.CONNECTION)
  ~(id : int)
  ~(arg : float list)
  =
  let query =
    (tup2 string int ->. unit) @@ "UPDATE table SET col = ? WHERE id = ?"
  in
  Db.exec query (arg |> to_sql_array_arg, id)
;;

Creating and closing this issue here in case it'll help others in the future.

[dangdennis]

Now for querying it back out, that's tbd.

[paurkedal]

The short answer is that the PostgreSQL array types are unsupported, since they are not available for other RDBMS. But since PostgreSQL passes parameters as string, your solution is correct under the hod. A string should also work for the returned arrays, so it should be a matter of parsing the result (String.split_on_char ',' and filter out {, , and spaces). It's possible to define a custom type which does this, using either the pre-2.0 custom type or the new product type.