On Monday, January 09, 2012 8:28:43 am Tony Capobianco wrote: > 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?
Well with out a year number(i.e. YYMMDD) that is going to be difficult around the year break. As an example: test(5432)aklaver=>select * from name_test; fld_1 ----------------- tmp_staging0109 tmp_staging0108 tmp_staging1229 (3 rows) test(5432)aklaver=>SELECT fld_1 from name_test where fld_1 < 'tmp_staging'|| to_char(current_date-interval '10 days','MMDD') and fld_1 > 'tmp_staging0131'; fld_1 ----------------- tmp_staging1229 > > Thanks. > Tony > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql