Message-ID: <3EB549F3.C7532C26@nsd.ca>
Date: Sun, 04 May 2003 13:12:19 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: jim@nasby.net
Cc: Kyle <kyle@ccidomain.com>,
	Forum - PostGreSQL General <pgsql-general@postgresql.org>
Subject: Re: [SQL] Best way to delete time stamped data?
References: <3EB2EBF0.8010003@ccidomain.com>
	<200305021619.41879.josh@agliodbs.com>
	<3EB510F1.8090708@ccidomain.com>
	<20030504113029.W66185@flake.decibel.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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