Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 1C106AF82E9 for ; Mon, 9 Jan 2012 12:28:58 -0400 (AST) Received: from mail.prospectiv.com ([155.212.59.212] helo=stan.prospectivdirect.com) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1RkI5g-0000hJ-9x for pgsql-sql@postgresql.org; Mon, 09 Jan 2012 16:28:57 +0000 Received: (qmail 29858 invoked from network); 9 Jan 2012 16:28:43 -0000 Received: from unknown (HELO ?192.168.1.15?) (192.168.1.15) by 0 with SMTP; 9 Jan 2012 16:28:43 -0000 Subject: Re: compare table names From: Tony Capobianco To: Adrian Klaver Cc: pgsql-sql@postgresql.org In-Reply-To: <201201090819.48041.adrian.klaver@gmail.com> References: <1326125538.1983.12.camel@tony1.localdomain> <201201090819.48041.adrian.klaver@gmail.com> Content-Type: text/plain; charset="UTF-8" Date: Mon, 09 Jan 2012 11:28:43 -0500 Message-ID: <1326126523.1983.16.camel@tony1.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.32.3 (2.32.3-1.fc14) Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201201/30 X-Sequence-Number: 36201 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 >