Multiple inserts from multiple WITH/CTE

Question

I have a table like this:

CREATE TABLE mytable
(
  col1 character varying(50),
  mydate timestamp without time zone
);

I want to insert data to this table, but also I want to store the maximum id from my source:

insert into mytable (select myid, col1, mydate from sourcetable);

I don't have a myid column in mytable, and I can't ask later something like this: select max(myid) from sourcetable because I'm getting a snapshot and the sourcetable is a transactional table (hundreds of new records by second) so I need to get the maximum id from that snapshot

I tried something like this:

with query1 as (select myid, col1, mydate from sourcetable),
query2 as (select max(myid) id from query1)
insert into mytable (select co1, mydate from query1);
update anothertable set value=(select myid from query2) where col2='avalue';

But I get this error:

ERROR:  relation "query2" does not exist
LINE 1: update anothertable set value=(select myid from query2) wher...

Is there a way to solve this?

Answer 1

The problem is that you have two queries after the CTEs. Only one. The CTE is connected to the queries. So, just add another CTE. Something like this:

with query1 as (
      select myid, col1, mydate
      from sourcetable
     ),
     query2 as (
      select max(myid) as id
      from query1
     ),
     i as (
      insert into mytable   -- You should really list the columns here
          select co1, mydate
          from query1
     )
update anothertable
    set value = (select myid from query2)
    where col2 = 'avalue';