Postgresql – Deleting aged rows from postgresql table which don’t have related entries in another table

maintenancepostgresqlpostgresql-9.3

I maintain a website where users post interesting links, and other users leave replies under the said links. I want to delete aged link entries under which no one replied.

The postgresql database is like so: there's a table called links_link, and another one called links_publicreply. links_publicreply has a column called answer_to, which is a foreign key to links_link. I only want to delete aged rows in links_link which do NOT have a related entry in links_publicreply.

What would the queries be? Something like the following?

begin;
DELETE FROM links_link WHERE id NOT IN links_publicreply AND "submitted_on" < now() - interval('1 day'); 
commit;

I don't want to run into foreign key violations.

Best Answer

Use a subrequest : this should work

DELETE FROM links_link WHERE id NOT IN (select answer_to from links_publicreply ) AND `submitted_on` < CURRENT_DATE-1 ;

You can have of course multiple subrequest chained.

Related Solutions

How to Delete Aged PostgreSQL Rows with Foreign Key Constraints

First start a transaction:

begin;

Start deleting all from links_groupseen first:

delete 
from links_groupseen 
where which_reply_id in (select myproperid from links_reply where "submitted_on" < now() - interval '30 days')

and then delete from links_reply:

DELETE 
FROM links_reply 
WHERE "submitted_on" < now()- interval '30 days';

and if you're happy with what you see, you can COMMIT. The reason I'm using a transaction is that within a transaction, now() will give you the same values at each call. Otherwise, you would have to replace now with a constant MYDATE value of your choice on both delete statements.

Delete Aged PostgreSQL Rows with Tricky Condition

You need something like this (the table and column names may differ, check the actual SQL CREATE TABLE statements):

DELETE FROM links_group AS g 
WHERE g.id IN 
      ( SELECT r.which_group_id
        FROM links_reply AS r
          -- WHERE r.which_group IS NOT NULL    
          -- not needed, the column is not nullable
        GROUP BY r.which_group_id
        HAVING MAX(r.submitted_on) < NOW() - INTERVAL '14 days'
      ) ;

Since there is a foreign key from links_reply that references links_group and does not have an ON DELETE CASCADE attribute set, the above will of course fail. To delete from both tables, you either need to run two statements or combine them in one with a modifying CTE:

WITH 
  groups_to_delete AS
    ( SELECT r.which_group_id
      FROM links_reply AS r
      GROUP BY r.which_group_id
      HAVING MAX(r.submitted_on) < NOW() - INTERVAL '14 days'
    ),
  delete_replies AS
    ( DELETE FROM links_reply AS r 
      WHERE r.which_group_id IN (TABLE groups_to_delete)
      RETURNING which_group_id
    )
DELETE FROM links_group AS g 
WHERE g.id IN (TABLE delete_replies) ;

Best Answer

Related Solutions

How to Delete Aged PostgreSQL Rows with Foreign Key Constraints

Delete Aged PostgreSQL Rows with Tricky Condition

Related Question