Обсуждение: Re: [SQL] Best way to delete time stamped data?
Josh, Thanks, even my boss can read the new statement: DELETE FROM ONLY richtable WHERE age(trxdate) > '90 days'; Your disparaging aside, that is exactly the kind of expression compaction I was after! :) And yeah, I have a deep PERL background, but that's not a bad thing. -Kyle Josh Berkus wrote: > More compact than that ?!?!?! > Maybe you should go into Perl, instead of SQL -- I think the one-liners would > suit you. > > You can use the age() function, but all that funciton does is the same > calculation you have above.
On Sun, May 04, 2003 at 09:09:05AM -0400, Kyle wrote: > Josh, > > Thanks, even my boss can read the new statement: > > DELETE FROM ONLY richtable WHERE age(trxdate) > '90 days'; Just remember that that query won't use any index on trxdate, because it has to calculate 'age(trxdate)' for every value in the table. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
The index can be used with a little rewrite (assuming trxdate is indexed): DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - '90 days'::interval; "Jim C. Nasby" wrote: > > On Sun, May 04, 2003 at 09:09:05AM -0400, Kyle wrote: > > Josh, > > > > Thanks, even my boss can read the new statement: > > > > DELETE FROM ONLY richtable WHERE age(trxdate) > '90 days'; > > Just remember that that query won't use any index on trxdate, because > it has to calculate 'age(trxdate)' for every value in the table. > -- > Jim C. Nasby (aka Decibel!) jim@nasby.net > Member: Triangle Fraternity, Sports Car Club of America > Give your computer some brain candy! www.distributed.net Team #1828 > > Windows: "Where do you want to go today?" > Linux: "Where do you want to go tomorrow?" > FreeBSD: "Are you guys coming, or what?" > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html