0

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.

1 Answer 1

1

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.

4
  • Hmm, so I tried DELETE FROM links_link WHERE id NOT IN (select "answer_to" from links_publicreply) AND "submitted_on" < now() - interval '1 hour'; which is more or less what you suggested, but I'm getting the error: current transaction is aborted, commands ignored until end of transaction block. Any ideas? Mar 1, 2016 at 14:30
  • Did you run it in a begin / commit ? or just directly ?
    – error500
    Mar 1, 2016 at 23:21
  • I ran it in begin / commit, out of habit. Mar 1, 2016 at 23:24
  • 1
    You should have a look at this so laurenthinoul.com/…
    – error500
    Mar 2, 2016 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.