0

I am using this query to insert record from one table into another table:

insert into watched_url_queue_work select * from watched_url_queue

on conflict do nothing

The unique constraints on the target table mean not all are inserted.

What I want to now do is delete all of the records that I just inserted but I am not sure of syntax.

I want something like (query not working just my guess at it):

delete from watched_url_queue q
where q.target_domain_record_id in
      (
        insert into watched_url_queue_work select * from watched_url_queue
        on conflict do nothing
        returning watched_url_queue_work.target_domain_record_id
      )
Guerrilla
  • 13,375
  • 31
  • 109
  • 210

1 Answers1

0

You can do this with a CTE:

with inserted as (
  insert into watched_url_queue_work
  select * from watched_url_queue
  on conflict do nothing
  returning watched_url_queue_work.target_domain_record_id
)
delete from watched_url_queue q
using inserted
where q.target_domain_record_id = inserted.target_domain_record_id;

(The q.target_domain_record_id in (select … from inserted) approach works as well.)

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • Thanks. I tried this both ways but the query runs for long time (20 minutes+) and I have to cut it off because this isn't going to work in production if it takes this long. The table is quite large (few million records). Is there any particular setting in postgresql I can increase to make this run better? The server has 60gb free ram while query is running. – Guerrilla Aug 10 '20 at 10:35
  • Maybe you can also try writing a procedure/[`DO` block](https://www.postgresql.org/docs/12/sql-do.html) that does this using a [cursor](https://www.postgresql.org/docs/12/plpgsql-cursors.html), or in batches of 1000, so that the temporary `inserted` table doesn't grow too large. – Bergi Aug 10 '20 at 12:03