# Support :insert\_all in data-modifying CTE / WITH statements

**URL:** <https://forum.elixirforum.com/t/support-insert-all-in-data-modifying-cte-with-statements/68033>\
**Category:** Proposals: Ideas\
**Tags:** ecto, postgres, ecto\_sql\
**Created:** [December 11, 2024, 12:00pm UTC](https://forum.elixirforum.com/t/support-insert-all-in-data-modifying-cte-with-statements/68033 "2024-12-11T12:00:44Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![dli](https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/d/9de0a6/32.png) [@dli](https://forum.elixirforum.com/u/dli)\
**Post date:** [December 11, 2024, 12:00pm UTC](https://forum.elixirforum.com/t/support-insert-all-in-data-modifying-cte-with-statements/68033/1 "2024-12-11T12:00:44Z")

</div>

Ecto currently [supports](https://hexdocs.pm/ecto/3.12.5/Ecto.Query.html#with_cte/3-expression-examples) **some** data-modifying WITH statements / CTEs for Postgres:

> **Options** : […]  
> `:operation` - one of `:all` , `:update_all` , or `:delete_all` indicating the operation type of the CTE query. If blank, it defaults to `:all` , making the CTE query a SELECT query.  
> […]  
> For Postgres built-in adapter, it is possible to define data-modifying CTE queries:
> 
> ```elixir
> update_categories_query =
> Category
> |> where([c], is_nil(c.parent_id))
> |> update([c], set: [name: "Root category"])
> |> select([c], c)
> 
> {"update_categories", Category}
> |> with_cte("update_categories", as: ^update_categories_query, operation: :update_all)
> |> select([c], c)
> 
> ```

Postgres, however, also supports INSERT statements inside CTEs:

```sql
-- EXAMPLE
with expensive_calc as (
   select customer_id, sum(amount) as total 
   from orders 
   group by customer_id
),
new_segments as (
   insert into segments (customer_id, total)
   select customer_id, total 
   from expensive_calc
   returning id, customer_id
)
insert into segment_history (segment_id, customer_id)
select id, customer_id 
from new_segments;

```

This allows multiple INSERTs to access the same source data, as well as the newly inserted data without crossing the database boundary.

The closest solutions in Ecto are:

1. Create temp table for `expensive_calc` (needs `fragment`)
2. Repeat `expensive_calc` as subquery (likely does the calculation twice)

… then use two sequential `Repo.insert_all` statements. However, this needs serialization of the `RETURNING` data and unnecessarily crosses the database ↔︎ app boundary.

Other examples:

- [https://dba.stackexchange.com/a/139951](https://dba.stackexchange.com/a/139951)
- [https://dba.stackexchange.com/a/315803](https://dba.stackexchange.com/a/315803)

## Ecto syntax

I assume this would require a new macro `insert/3` similar to `update/3` which allows adding `returning: [p.id, p.title, ...]`? Haven’t given this too much thought.

Judging by the `ecto_sql` source and [associated PR](https://github.com/elixir-ecto/ecto_sql/pull/532#issuecomment-1612552365), this was initially planned but not implemented yet:

[https://github.com/elixir-ecto/ecto\_sql/blob/8012355131c64048824a6b0799def36fae208555/lib/ecto/adapters/postgres/connection.ex#L619-L621](https://github.com/elixir-ecto/ecto_sql/blob/8012355131c64048824a6b0799def36fae208555/lib/ecto/adapters/postgres/connection.ex#L619-L621)

Maybe now is the right time?

---

<div class="post-metadata">

**Author:** ![dli](https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/d/9de0a6/32.png) [@dli](https://forum.elixirforum.com/u/dli)\
**Post date:** [September 25, 2025, 2:51pm UTC](https://forum.elixirforum.com/t/support-insert-all-in-data-modifying-cte-with-statements/68033/2 "2025-09-25T14:51:23Z")

</div>

Bump to gather comments from top contributors — wdyt @josevalim @ericmj @michalmuskala?