Thread: Deleting orphan records
Does anybody have a method for deleting orphan (unreferenced) records? Is it possible to set up some sort of constraint or trigger (that performs well when lots of records are deleted ;) )?
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Does ON DELETE CASCADE not work for you?
Other than that you would have do do outer join acrobatics ;-)
Best Regards;
Chris Travers
----- Original Message -----From: John SmithSent: Wednesday, February 05, 2003 12:55 PMSubject: [GENERAL] Deleting orphan recordsDoes anybody have a method for deleting orphan (unreferenced) records? Is it possible to set up some sort of constraint or trigger (that performs well when lots of records are deleted ;) )?
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
I was referring to parent records with no children ;).
John
Chris Travers <chris@travelamericas.com> wrote:
Does ON DELETE CASCADE not work for you?
Other than that you would have do do outer join acrobatics ;-)
Best Regards;
Chris Travers
----- Original Message -----From: John SmithSent: Wednesday, February 05, 2003 12:55 PMSubject: [GENERAL] Deleting orphan recordsDoes anybody have a method for deleting orphan (unreferenced) records? Is it possible to set up some sort of constraint or trigger (that performs well when lots of records are deleted ;) )?
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
On Wed, 5 Feb 2003, John Smith wrote: > > I was referring to parent records with no children ;). > John > Chris Travers <chris@travelamericas.com> wrote:Does ON DELETE CASCADE not work for you? Other than that you would havedo do outer join acrobatics ;-) Best Regards;Chris Travers----- Original Message ----- From: John Smith To: pgsql-general@postgresql.orgSent: Wednesday, February 05, 2003 12:55 PMSubject: [GENERAL] Deleting orphan records > Does anybody have a method for deleting orphan (unreferenced) records? Is it possible to set up some sort of constraintor trigger (that performs well when lots of records are deleted ;) )? > I'm was sure I'd seen mention of using an extra table in the delete statement in the docs. However, I can't see it in 7.4dev. Going by that short description though the following might be possible: DELETE FROM table1 WHERE NOT EXISTS ( SELECT FROM table2 WHERE table2.forkeycol = table1.forkeycol ) According the the docs the reader is directed to the SELECT page for details of the WHERE clause and the above would be valid for a select. -- Nigel J. Andrews
On Thu, Feb 06, 2003 at 07:36:47 +0000, "Nigel J. Andrews" <nandrews@investsystems.co.uk> wrote: > > I'm was sure I'd seen mention of using an extra table in the delete statement > in the docs. However, I can't see it in 7.4dev. Going by that short description I was wondering about that. When you mentioned it I went and checked and didn't find it so I thought maybe the extra tables were getting included automatically which think select will do. There is an extension to the update command to reference additional tables and that might be what you were thinking of.
John Smith wrote: > I was referring to parent records with no children ;). > > John I think this sort of reference requirement is meant by the SQL keyword PENDANT. Not sure what PENDANT ON DELETE CASCADE then is supposed to do, but removing parents that became childless would be one plausible interpretation. PENDANT is not yet implemented in PostgreSQL. And please, John, can you stop sending your plain text messages as multipart mime encoded? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Are there plans to implement PENDANT?
http://www.ca.postgresql.org/projects/devel-refint.html
http://www.dbazine.com/mullins_triggers.html
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/doc/TODO.detail/foreign.diff?r1=1.1&r2=1.2
Sorry about the MIME emails - can't figure out how to get Yahoo to send text (I see you use Yahoo - how's it done? ;) ).
John
Jan Wieck <JanWieck@Yahoo.com> wrote:
John Smith wrote:
> I was referring to parent records with no children ;).
>
> John
I think this sort of reference requirement is meant by the SQL keyword
PENDANT. Not sure what PENDANT ON DELETE CASCADE then is supposed to do,
but removing parents that became childless would be one plausible
interpretation.
PENDANT is not yet implemented in PostgreSQL.
And please, John, can you stop sending your plain text messages as
multipart mime encoded?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
John Smith wrote: > Are there plans to implement PENDANT? Not that I know of. I think you can work around it with a custom triggers on UPDATE and DELETE on the child rows, that then do a DELETE on the parent table using OLD.fkey (if OLD.fkey != NEW.fkey in the UPDATE case of course). > Sorry about the MIME emails - can't figure out how to get Yahoo to > send text (I see you use Yahoo - how's it done? ;) ). By having your own IMAP server on a Linux box at home, polling the mail from Yahoo! and other accounts with fetchmail, filtering it for SPAM with procmail and some Tcl scripts and then using Netscape with roaming profile and SSL IMAP connections to access your mailboxes from wherever you are. Since you need Apache for the roaming profile server anyway, you may also install SquirrelMail so you can get at you mail from any browser via https. Sorry, you asked the wrong guy for this ;-) Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #