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

Re: compare table names

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: compare table names
Date: 2012-01-09 16:51:30
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
Hash: RIPEMD160

> 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?

First, be aware that MMDD alone is a suboptimal choice, for you will get 
burned by year boundaries, unless you go to crazy efforts to look at 
the current year, devine if 1230 should be 'less' than 0102 because 
it's January, etc. Assuming you change it to YYYYMMDD, you could run 
a simple query like this:

SELECT tablename
FROM pg_tables
WHERE tablename ~ '^tmp_staging'
AND substring(tablename from '\d+')::date < now() - '10 days'::interval;

- -- 
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 201201091144


In response to

pgsql-sql by date

Next:From: Steve CrawfordDate: 2012-01-09 16:58:18
Subject: Re: compare table names
Previous:From: Adrian KlaverDate: 2012-01-09 16:33:23
Subject: Re: compare table names

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