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?

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

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


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.

| improve this answer | |
  • 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? – Hassan Baig Mar 1 '16 at 14:30
  • Did you run it in a begin / commit ? or just directly ? – error500 Mar 1 '16 at 23:21
  • I ran it in begin / commit, out of habit. – Hassan Baig Mar 1 '16 at 23:24
  • 1
    You should have a look at this so laurenthinoul.com/… – error500 Mar 2 '16 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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