Обсуждение: 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