Insert into Select from Unnest

Questions / Help
,
1

I have a use case where I want to insert large number of rows in PG Table at once from Ecto. But ecto has a param limit of 65_535, so the max rows I can insert is 65_535 / tot_number_of_fields_in_schema.

To overcome this, I am trying to see if I can to
Repo.insert_all(Schema, query)
query = from(c in Ecto.Query.fragment("SELECT * FROM unnest(? as field1, ? as field2"), ^field1_array, ^field2_array)

The idea here is, the total params will be 2 in this case

-- before
INSERT INTO users
    (id, name)
    VALUES ($1,$2),($3,$4);

-- after
INSERT INTO users
    (id, name)
    (select * from unnest($1::int[], $2::int[]));
2

Take a look at:

Though I also found jsonb_to_recordset quite useful:

2 Likes
3

I think using Fragment directly for inserts does not work, what you have done with join will work in case of updates

4

I have done this:

"d" 
|> with_cte("d", as: fragment("SELECT * FROM unnest(ARRAY[1]::int[], ARRAY[2]::int[]) as x(a, b)"))
|> select([d], %{a: d.a})

But how do I remove prefix from cte query? I have a multi tenant/schema application, so I need prefix when I would try to do
DBWrapper.insert_all(SchemaModule, query)