Bulk inserts with Postgrex

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