From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: compare table names
Date: Mon, 9 Jan 2012 16:51:30 -0000
Message-ID: <a2359afd600c75322c5279728a04b8c3@biglumber.com> (raw)
In-Reply-To: <1326126523.1983.16.camel@tony1.localdomain>
-----BEGIN PGP SIGNED MESSAGE-----
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@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201201091144
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAk8LGuUACgkQvJuQZxSWSshD0QCcDipiHcgchfQMHMC6jC9ExkCv
K44Anjy7eRg0uVNOoZ3AbHecf1nn6TmT
=v/9C
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: greg@turnstep.com
Subject: Re: compare table names
In-Reply-To: <a2359afd600c75322c5279728a04b8c3@biglumber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox