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
