-----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 g...@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----- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql