CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
user779159
user779159

Reputation: 9602

Use postgres CTE in multiple queries

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)

Upvotes: 6

Views: 4597

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

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);

Upvotes: 5

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

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.

Upvotes: 0

Related Questions