Thread: compare table names
I have these 3 tables:
tablename
--------------------tmp_staging0109tmp_staging1229tmp_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_staging0109tmp_staging1229tmp_staging0108
How can I write this correctly?
Thanks.
Tony
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.klaver@gmail.com
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 >
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.klaver@gmail.com
-----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 YYYYMMDD, 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 greg@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-----
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 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 ...Ah, there's the missing part - the 1229 represents a date that is missing year information. If you can change things up a bit, I'd add the year to the name "tmp_stagingYYYYMMDD" which makes the query easy. (We do this in a few cases where we are given blocks of data that are valid through a certain date. Each block of data is a child of the main table and has a name that represents the last date the data is valid. A daily script drops any partition that has expired.) If you can't add the year, you will be stuck with extra work to properly handle the first 10-days of each year. Alternately, you could have a separate table that just tracks the creation dates of the temporary tables and be free from any requirement to have dates be part of the table names. Cheers, Steve
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_staging0109tmp_staging0108tmp_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
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')
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:Well with out a year number(i.e. YYMMDD) that is going to be difficult around the
> 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?
year break.
As an example:
test(5432)aklaver=>select * from name_test;fld_1test(5432)aklaver=>SELECT fld_1 from name_test where fld_1 < 'tmp_staging'||
-----------------
tmp_staging0109
tmp_staging0108
tmp_staging1229
(3 rows)
to_char(current_date-interval '10 days','MMDD') and fld_1 > 'tmp_staging0131';
fld_1
-----------------
tmp_staging1229--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 > >