I'm trying to perform a pretty basic operation with a few steps:

1. SELECT data from table1
2. Use id column from my selected table to remove data from table2
3. Insert the selected table from step 1 into table2

I would imagine that this would work

begin;

with temp as (
  select id
  from table1
)

delete from table2
where id in (select id from temp);

insert into table2 (id)
select id from temp;

commit;

But I'm getting an error saying that temp is not defined during my insert step?

Only other post I found about this is this one but it didn't really answer my question.

Thoughts?

CodePudding user response：

From Postgres documentation:

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

If you need a temp table for more than one query you can do instead:

begin;

create temp table temp_table as (
  select id
  from table1
);

delete from table2
where id in (select id from temp_table);

insert into table2 (id)
select id from temp_table;

commit;