Message-ID: <4F0B1CAA.2060604@pinpointresearch.com>
Date: Mon, 09 Jan 2012 08:58:18 -0800
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.23) Gecko/20110922 Lightning/1.0b2 Thunderbird/3.1.15
MIME-Version: 1.0
To: Tony Capobianco <tcapobianco@prospectiv.com>
CC: Adrian Klaver <adrian.klaver@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: compare table names
References: <1326125538.1983.12.camel@tony1.localdomain>	
	<201201090819.48041.adrian.klaver@gmail.com>
	<1326126523.1983.16.camel@tony1.localdomain>
In-Reply-To: <1326126523.1983.16.camel@tony1.localdomain>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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