Re: [SQL] compare table names

2012-01-11 Thread Tony Capobianco
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_nameMMDD. Wish me luck! Thanks for all y

Re: [SQL] compare table names

2012-01-10 Thread Brice André
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(

Re: [SQL] compare table names

2012-01-09 Thread Adrian Klaver
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 > >

Re: [SQL] compare table names

2012-01-09 Thread Steve Crawford
On 01/09/2012 08:28 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

Re: [SQL] compare table names

2012-01-09 Thread Greg Sabino Mullane
-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 > crea

Re: [SQL] compare table names

2012-01-09 Thread Adrian Klaver
On Monday, January 09, 2012 8:12:18 am Tony Capobianco wrote: > tablename > > tmp_staging0109 > tmp_staging1229 > tmp_staging0108 > > How can I write this correctly? Had another idea. If you are looking for the highest numbered table below a certain number then mayb

Re: [SQL] compare table names

2012-01-09 Thread Tony Capobianco
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 se

Re: [SQL] compare table names

2012-01-09 Thread Adrian Klaver
On Monday, January 09, 2012 8:12:18 am Tony Capobianco wrote: > I have these 3 tables: > > tablename > > tmp_staging0109 > tmp_staging1229 > tmp_staging0108 > > > I'd like this query: > > select tablename from pg_tables where tablename like 'tmp_staging%' and > tabl

[SQL] compare table names

2012-01-09 Thread Tony Capobianco
I have these 3 tables: tablename tmp_staging0109 tmp_staging1229 tmp_staging0108 I'd like this query: select tablename from pg_tables where tablename like 'tmp_staging%' and tablename < 'tmp_staging1230'; To return this result: tablename