What’s the best way to do bulk inserts with Postgrex?
If I have a list [[1,2],[3,4],[5,6]] what’s the ideal way to handle it in Postgrex.
1 Like
What have you tried so far? Are you using Ecto or just bare Postgrex? What approach would you take with plain SQL?
@benwilson512 I am using Postgrex.I know with Ecto you can do Repo.insert_all .I am trying to insert 50,000 records.So, till now I have
1.50000 |> Stream.map(fn(item) -> [item,item * 2] end) |> Stream.chunk(1000,1000,[]) |> Task.async_stream(fn(list) -> list |> Enum.each(fn(record) -> Postgrex.query!(pid, "INSERT INTO mytable(val1,val2) VALUES($1,$2) ", list) end) end)
It works but I am trying to insert multiple values in a single transaction
2 Likes
Wooo, quite a number, I’d probably use the PostgreSQL COPY command.
Some relevant threads:
7 Likes
So Postgrex can insert multiple records, and this is precisely what Ecto is doing. If you use Ecto’s insert_all, it inserts multiple records using single INSERT command like this:
INSERT INTO "table" ("a","b","c") VALUES ($1,$2,$3),($4,$5,$6), ... ,($97,$98,$99) ["A", "B", "C", "A", "B", "C", ... , "A", "B", "C"]
If you are using Postgrex directly, I think you need to assemble that SQL on your own, I don’t think it gives you any shortcuts.
2 Likes
