Re: compare table names - Mailing list pgsql-sql

This is an old system that we converted over from Oracle just this past
year.  The first 10 days of this year bit us when needed warehouse
tables were removed erroneously.
I'm going to push uphill against management to try and create the tables
as table_nameYYYYMMDD.
Wish me luck!

Thanks for all your responses.

Tony


On Wed, 2012-01-11 at 07:43 +0100, Brice André wrote:
> 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.klaver@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.klaver@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
>         
>