Re: compare table names

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