Use postgres CTE in multiple queries

Question

I can use a CTE in a single query like this

with mycte as (...)
  insert into table1 (col1) select col1 from mycte where col1 in
    (select col1 from mycte)

But what if I want to use mycte in multiple queries? How can I make something like this work?

with mycte as (...)
  insert into table1 (col1) select col1 from mycte where col1 in
    (select col1 from mycte),
  insert into table2 (col1) select col1 from mycte where col1 in
    (select col1 from mycte)

If you want the data to persist across a statement, I think you will need to use a temporary table — Corion
– Corion, Commented Oct 22, 2018 at 8:50
A cte is one-time and local to a query. Create a view instead. — jarlh
– jarlh, Commented Oct 22, 2018 at 8:50
Could you give an example of this case with a temporary table or a view? — user779159
– user779159, Commented Oct 22, 2018 at 9:16

Gordon Linoff · Accepted Answer · 2018-10-22 11:09:30Z

5

For multiple inserts, you can put them into the same query:

with mycte as (...),
     i1 as (
      insert into table1 (col1)
          select col1
          from mycte
          where col1 in (select col1 from mycte)
          returning *
     )
insert into table2 (col1)
    select col1
    from mycte
    where col1 in (select col1 from mycte);

answered Oct 22, 2018 at 11:09

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2018-10-22 08:51:06Z

0

A CTE is an ad-hoc view. If you want a permanent view that you can use in multiple queries, then use CREATE VIEW instead.

answered Oct 22, 2018 at 8:51

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

2 Comments

user779159 Over a year ago

Is it possible to make a temporary view that doesn't persist outside of a postgres function? So I can use it in multiple queries in that function/transaction but not outside.

Thorsten Kettner Over a year ago

I think in PostgreSQL you can select into an array and then select from this array.

Collectives™ on Stack Overflow

Use postgres CTE in multiple queries

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related