Question 281989 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Delete query to find records based on complex conditions not working

I have an postgres table in the below structure

enter image description here

I want to write a query which will Delete all the records from the table which are older than 90 days and is in COMPLETE status. if any row has a root_id or a parent_id and is OPEN, then the root_id and the parent_id rows should not be deleted. In the above tables, the row with id=1 should be deleted. Although, id=2 and id=3 are complete and older than 90 days, but since the id=4 has root_id and parent_id has 2 and 3, all the 3 rows (id=2,id=3 and id=4) should not be deleted.
I tried multiple ways with inner queries but I am not able to write a query for this scenario.

Tags:

2

Answers


  1. 0

    Translating your requirement into SQL, I get

    DELETE FROM tab
WHERE status = 'COMPLETE'
  AND timestamp < current_timestamp - INTERVAL '90 days'
  AND NOT EXISTS (SELECT 1 FROM tab AS t2
                  WHERE t2.status = 'OPEN'
                    AND (tab.id = t2.root_id OR tab.id = t2.parent_id)
                 );
    Login or Signup to reply.
  2. 0

    Step by step – first identify record id-s that are parents or roots of records with ‘OPEN’ status and store them in t CTE, then delete those records that are ‘COMPLETE’, older than 90 days and whose id is not in t. ts = 'older than 90 days' is illustrative.

    with t (id) as 
(
 select unnest(array[root_id, parent_id])
 from the_table 
 where status = 'OPEN' 
  and root_id is not null 
  and parent_id is not null
)
delete from the_table
where status = 'COMPLETE'
  and ts = 'older than 90 days'
  and id not in (select id from t);

    DB-fiddle demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search