Skip site navigation (1) Skip section navigation (2)

Re: How to delete the oldest X number of rows?

From: "John Roberts" <jsrober(at)hotmail(dot)com>
To: "'A(dot) Kretschmer'" <andreas(dot)kretschmer(at)schollglas(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: How to delete the oldest X number of rows?
Date: 2005-12-13 02:28:04
Message-ID: BAY106-DAV229DB7D1EFA59C49975756BE390@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-novice
Thank you! That works beautifully!

John

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of A. Kretschmer
Sent: Monday, December 12, 2005 11:47 AM
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] How to delete the oldest X number of rows?
Importance: High

am  12.12.2005, um 11:29:58 -0500 mailte John Roberts folgendes:
> Hi,
> 
> I have a table that logs the performance of a system. I get an entry in 
> this table each time the system does something.
> 
> I want to keep the last 50,000 rows in the table. I'd like to run a cron 
> job every 5 minutes that will delete all rows > 50,000, oldest first. 
> What's the most efficient way to do this query/delete?
> 
> DELETE FROM log WHERE timestamp < ????

Something like:

select timestamp from log order by timestamp limit 1 offset 49999;

to get the oldest timestamp to keep and delete the early rows?


*untestet*

delete from fol where timestamp < (select timestamp from log order by
timestamp limit 1 offset 49999);

HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    === 

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

In response to

pgsql-novice by date

Next:From: Agnes BocchinoDate: 2005-12-13 09:34:57
Subject: Re: transaction ID wrap limit is 2147484146 Start Postgresql
Previous:From: Michael FuhrDate: 2005-12-12 17:55:02
Subject: Re: Bind Variables and Quoting / Dequoting Input

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group