Just my 2 cents... Why don't you use a date column type instead of a string ? In this case, at insertion, you could simply do this :
INERT INTO tablename (insertion_time, ...) VALUES (now(), ...) and, for the select, you could simply write : SELECT * FROM tablename WHERE insertion_time >= (now() - interval '1 day') 2012/1/9 Adrian Klaver <adrian.kla...@gmail.com> > 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 >
