On 01/09/2012 08:28 AM, Tony Capobianco wrote:
I see what you're saying:
pg=# select tablename from pg_tables where tablename like 'tmp_staging%' and
tablename< 'tmp_staging1230' and tablename> 'tmp_staging1228';
tablename
--------------------
tmp_staging1229
This query is part of a larger script where I want to dynamically select
tablenames older than 10 days and drop them. The tables are created in
a tmp_stagingMMDD format. I know postgres does not maintain object
create times, how can I write this to select tables from pg_tables that
are older than 10 days?
Thanks.
Tony
...Ah, there's the missing part - the 1229 represents a date that is
missing year information.
If you can change things up a bit, I'd add the year to the name
"tmp_stagingYYYYMMDD" which makes the query easy. (We do this in a few
cases where we are given blocks of data that are valid through a certain
date. Each block of data is a child of the main table and has a name
that represents the last date the data is valid. A daily script drops
any partition that has expired.)
If you can't add the year, you will be stuck with extra work to properly
handle the first 10-days of each year.
Alternately, you could have a separate table that just tracks the
creation dates of the temporary tables and be free from any requirement
to have dates be part of the table names.
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
