Re: [SQL] compare table names
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 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.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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
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
[SQL] compare table names
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 tmp_staging1229 However, I'm receiving: tablename tmp_staging0109 tmp_staging1229 tmp_staging0108 How can I write this correctly? Thanks. Tony -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
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 tablename 'tmp_staging1230'; To return this result: tablename tmp_staging1229 However, I'm receiving: tablename tmp_staging0109 tmp_staging1229 tmp_staging0108 How can I write this correctly? As far as I can tell it is correct. 0108,0109 and 1229 are all less than 1230. What happens if you do?: select tablename from pg_tables where tablename like 'tmp_staging%' and tablename 'tmp_staging1230' and tablename 'tmp_staging1228; 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
Re: [SQL] compare table names
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? Thanks. Tony On Mon, 2012-01-09 at 08:19 -0800, Adrian Klaver wrote: 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 tablename 'tmp_staging1230'; To return this result: tablename tmp_staging1229 However, I'm receiving: tablename tmp_staging0109 tmp_staging1229 tmp_staging0108 How can I write this correctly? As far as I can tell it is correct. 0108,0109 and 1229 are all less than 1230. What happens if you do?: select tablename from pg_tables where tablename like 'tmp_staging%' and tablename 'tmp_staging1230' and tablename 'tmp_staging1228; Thanks. Tony -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
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 maybe this: test(5432)aklaver=\d name_test Table public.name_test Column | Type| Modifiers +---+--- fld_1 | character varying | 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 like 'tmp_staging%' and fld_1 'tmp_staging1230' order by fld_1 desc limit 1; 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
Re: [SQL] compare table names
-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 MMDD, 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
Re: [SQL] compare table names
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
