pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBest way to delete time stamped data?
8+ messages / 7 participants
[nested] [flat]
* Best way to delete time stamped data?
@ 2003-05-02 22:06 Kyle <kyle@ccidomain.com>
0 siblings, 2 replies; 8+ messages in thread
From: Kyle @ 2003-05-02 22:06 UTC (permalink / raw)
To: pgsql-sql
Hello. I have a table that contains time stamped data. To delete rows
that are over 90 days old, I do this:
DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - interval
'90 days';
This works great. But I was wondering if there is a better or more
compact way to go about it. (this method feels "clunky" to me)
Thanks.
-Kyle
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to delete time stamped data?
@ 2003-05-02 22:51 Steve Crawford <scrawford@pinpointresearch.com>
parent: Kyle <kyle@ccidomain.com>
1 sibling, 0 replies; 8+ messages in thread
From: Steve Crawford @ 2003-05-02 22:51 UTC (permalink / raw)
To: Kyle <kyle@ccidomain.com>; pgsql-sql
I'm usually lazy and let automatic casting work for me:
DELETE FROM ONLY richtable WHERE now()-trxdate > '90 days';
Cheers,
Steve
On Friday 02 May 2003 3:06 pm, Kyle wrote:
> Hello. I have a table that contains time stamped data. To delete rows
> that are over 90 days old, I do this:
>
>
> DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - interval
> '90 days';
>
>
> This works great. But I was wondering if there is a better or more
> compact way to go about it. (this method feels "clunky" to me)
>
> Thanks.
>
> -Kyle
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to delete time stamped data?
@ 2003-05-02 23:19 Josh Berkus <josh@agliodbs.com>
parent: Kyle <kyle@ccidomain.com>
1 sibling, 2 replies; 8+ messages in thread
From: Josh Berkus @ 2003-05-02 23:19 UTC (permalink / raw)
To: Kyle <kyle@ccidomain.com>; pgsql-sql
Kyle,
> DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - interval
> '90 days';
>
> This works great. But I was wondering if there is a better or more
> compact way to go about it. (this method feels "clunky" to me)
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.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Best way to delete time stamped data?
@ 2003-05-04 13:09 Kyle <kyle@ccidomain.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 8+ messages in thread
From: Kyle @ 2003-05-04 13:09 UTC (permalink / raw)
To: Forum - PostGreSQL General <pgsql-general@postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Best way to delete time stamped data?
@ 2003-05-04 16:30 Jim C. Nasby <jim@nasby.net>
parent: Kyle <kyle@ccidomain.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jim C. Nasby @ 2003-05-04 16:30 UTC (permalink / raw)
To: Kyle <kyle@ccidomain.com>; +Cc: Forum - PostGreSQL General <pgsql-general@postgresql.org>
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?"
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Best way to delete time stamped data?
@ 2003-05-04 17:12 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 0 replies; 8+ messages in thread
From: Jean-Luc Lachance @ 2003-05-04 17:12 UTC (permalink / raw)
To: jim@nasby.net; +Cc: Kyle <kyle@ccidomain.com>; Forum - PostGreSQL General <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to delete time stamped data?
@ 2003-05-05 09:56 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 8+ messages in thread
From: Rajesh Kumar Mallah @ 2003-05-05 09:56 UTC (permalink / raw)
To: josh@agliodbs.com, Kyle <kyle@ccidomain.com>; pgsql-sql
an index on age(date(trxdate)) would make it faster ?
regds
mallah.
On Saturday 03 May 2003 4:49 am, Josh Berkus wrote:
> Kyle,
>
> > DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - interval
> > '90 days';
> >
> > This works great. But I was wondering if there is a better or more
> > compact way to go about it. (this method feels "clunky" to me)
>
> 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.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best way to delete time stamped data?
@ 2003-05-05 13:32 Bruno Wolff III <bruno@wolff.to>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 0 replies; 8+ messages in thread
From: Bruno Wolff III @ 2003-05-05 13:32 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: josh@agliodbs.com, Kyle <kyle@ccidomain.com>; pgsql-sql
On Mon, May 05, 2003 at 15:26:21 +0530,
Rajesh Kumar Mallah <mallah@trade-india.com> wrote:
>
>
> an index on age(date(trxdate)) would make it faster ?
That won't work because the value returned by age depends on the time
the transaction started. If you want the delete to be indexed you
need to write it out the longer way.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-05-05 13:32 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-02 22:06 Best way to delete time stamped data? Kyle <kyle@ccidomain.com>
2003-05-02 22:51 ` Steve Crawford <scrawford@pinpointresearch.com>
2003-05-02 23:19 ` Josh Berkus <josh@agliodbs.com>
2003-05-04 13:09 ` Kyle <kyle@ccidomain.com>
2003-05-04 16:30 ` Jim C. Nasby <jim@nasby.net>
2003-05-04 17:12 ` Jean-Luc Lachance <jllachan@nsd.ca>
2003-05-05 09:56 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-05-05 13:32 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox