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

Re: compare table names

From: Tony Capobianco <tcapobianco(at)prospectiv(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: compare table names
Date: 2012-01-09 16:28:43
Message-ID: 1326126523.1983.16.camel@tony1.localdomain (view raw or flat)
Thread:
Lists: pgsql-sql
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

On Mon, 2012-01-09 at 08:19 -0800, Adrian Klaver wrote:
> On Monday, January 09, 2012 8:12:18 am Tony Capobianco wrote:
> > I have these 3 tables:
> > 
> >      tablename
> > --------------------
> >  tmp_staging0109
> >  tmp_staging1229
> >  tmp_staging0108
> > 
> > 
> > I'd like this query:
> > 
> > select tablename from pg_tables where tablename like 'tmp_staging%' and
> > tablename < 'tmp_staging1230';
> > 
> > To return this result:
> > 
> >      tablename
> > --------------------
> >  tmp_staging1229
> > 
> > However, I'm receiving:
> > 
> >      tablename
> > --------------------
> >  tmp_staging0109
> >  tmp_staging1229
> >  tmp_staging0108
> > 
> > How can I write this correctly?
> 
> As far as I can tell it is correct. 0108,0109 and 1229 are all less than 1230.  
> What happens if you do?:
> 
> select tablename from pg_tables where tablename like 'tmp_staging%' and 
> tablename < 'tmp_staging1230' and tablename > 'tmp_staging1228;
> 
> > 
> > Thanks.
> > Tony
> 



In response to

Responses

pgsql-sql by date

Next:From: Adrian KlaverDate: 2012-01-09 16:33:23
Subject: Re: compare table names
Previous:From: Adrian KlaverDate: 2012-01-09 16:19:47
Subject: Re: compare table names

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