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 > 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 > [email protected] > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] amount of join's and sequential access to the tables involved
Hi all. Im working on a 'simple' query with 7, 8 left joins. After the 9nth join or so, explain analyze became to show the plan with many tables being read in sequential fashion. Of course, this slows down the query response in a factor on 10. I can alter the order in wich the join's are appended, allways with the same result. So,it looks like a resources config situation. Wich config variables are involved in the use of hash joins? (I dont have access right now to the server running pg, so i cannot show query plan nor pg configuration) Thanks! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] amount of join's and sequential access to the tables involved
Gerardo Herzig writes: > Hi all. Im working on a 'simple' query with 7, 8 left joins. After the > 9nth join or so, explain analyze became to show the plan with many > tables being read in sequential fashion. Of course, this slows down the > query response in a factor on 10. increase join_collapse_limit, perhaps? regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] amount of join's and sequential access to the tables involved
El mié, 11-01-2012 a las 10:40 -0500, Tom Lane escribió: > Gerardo Herzig writes: > > Hi all. Im working on a 'simple' query with 7, 8 left joins. After the > > 9nth join or so, explain analyze became to show the plan with many > > tables being read in sequential fashion. Of course, this slows down the > > query response in a factor on 10. > > increase join_collapse_limit, perhaps? > > regards, tom lane > Crap, yes. That was it. Just curious about one thing: That query is part of a function. If i raise set_join_collapse in a psql bash session, and then excecute the function, there is no changes. But if i excecute the query directly in the psql session, it flys. So, what happens, when a plsql function is excecuted, it takes is own enviroment variables, or something like that? Thanks again, Tom! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] amount of join's and sequential access to the tables involved
Gerardo Herzig writes: > So, what happens, when a plsql function is excecuted, it takes is own > enviroment variables, or something like that? No, but it probably cached a plan from an execution before you changed join_collapse_limit ... regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Unable To Alter Data Type
I have an issue I can't figure out. I have the following TABLE: tysql=# \d customers Table "public.customers" Column| Type | Modifiers --++--- cust_id | character(10) | not null cust_name| character(50) | not null cust_address | character(50) | cust_city| character(50) | cust_state | character(5) | cust_zip | character(10) | cust_country | character(50) | cust_contact | character(50) | cust_email | character(255) | Indexes: "customers_pkey" PRIMARY KEY, btree (cust_id) Referenced by: TABLE "orders" CONSTRAINT "fk_orders_customers" FOREIGN KEY (cust_id) REFERENCES customers(cust_id) Now I'm attempting to ALTER the field 'cust_zip' TYPE from character to integer however I'm getting the following error: tysql=# ALTER TABLE customers ALTER COLUMN cust_zip TYPE bigint; ERROR: column "cust_zip" cannot be cast to type bigint I thought perhaps the stored data in the field conflicted with the data type but I can't see why: tysql=# SELECT cust_zip FROM customers; cust_zip 4 4 4 8 54545 32765 (6 rows) -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Alter Data Type
-Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of Carlos Mennens Sent: Wednesday, January 11, 2012 6:53 PM To: PostgreSQL (SQL) Subject: [SQL] Unable To Alter Data Type Now I'm attempting to ALTER the field 'cust_zip' TYPE from character to integer however I'm getting the following error: I think there is a rule in PostgreSQL that says that any field with "zip" in the name MUST BE of a character type :) You seriously do not want to turn this into an integer (big or otherwise) - ZIP (POSTAL) CODES ARE NOT NUMERIC; THEY ARE TEXT! If someone is telling you to make this change then tell them they are wrong and whatever requirement are in place on the other end need to be modified - not the table you indicate. I do confirm, however, that a direct cast from character to bigint works and thus you SHOULD be able to perform the TYPE alteration without adding the USING (expression) clause. So either this is a bug OR, more likely, character-to-bigint casting is not implicit (or an assignment cast, whatever that is) and so you must resort to the USING clause - which does work. [ ... ALTER COLUMN zip_code TYPE bigint USING (zip_code::bigint) ] http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html This is on 9.0 for me. However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS! The specific issue is that some US Postal Code begin with a zero ( 0 ) and so whenever you want to the zip_code value you need to pad leading zeros if the length is less than 5. Now consider that a full zip_code can be in 5+4 format with an embedded hyphen and you no longer can even store it as numeric. If you deal with Canada (and maybe Mexico) at all then spaces and letters become acceptable characters within the zip_code. David J. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Alter Data Type
On Wed, Jan 11, 2012 at 7:13 PM, David Johnston wrote: > However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS! > > The specific issue is that some US Postal Code begin with a zero ( 0 ) and > so whenever you want to the zip_code value you need to pad leading zeros if > the length is less than 5. Now consider that a full zip_code can be in 5+4 > format with an embedded hyphen and you no longer can even store it as > numeric. If you deal with Canada (and maybe Mexico) at all then spaces and > letters become acceptable characters within the zip_code. David - Thank you for that great info / explanation. Very informative and helpful. I was not required to make this change but rather just goofing off attempting to learn SQL as I'm rather terrible at it. Can you tell me if there's an organized cheat sheet or something documented in regards to data types commonly used for commonly used field association? I think that's great for people who can't look at the documentation and clearly understand specific definitions or assumed categorization based on the type definition. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Alter Data Type
On Jan 11, 2012, at 19:30, Carlos Mennens wrote: > On Wed, Jan 11, 2012 at 7:13 PM, David Johnston wrote: >> However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS! >> >> The specific issue is that some US Postal Code begin with a zero ( 0 ) and >> so whenever you want to the zip_code value you need to pad leading zeros if >> the length is less than 5. Now consider that a full zip_code can be in 5+4 >> format with an embedded hyphen and you no longer can even store it as >> numeric. If you deal with Canada (and maybe Mexico) at all then spaces and >> letters become acceptable characters within the zip_code. > > David - Thank you for that great info / explanation. Very informative > and helpful. I was not required to make this change but rather just > goofing off attempting to learn SQL as I'm rather terrible at it. Can > you tell me if there's an organized cheat sheet or something > documented in regards to data types commonly used for commonly used > field association? I think that's great for people who can't look at > the documentation and clearly understand specific definitions or > assumed categorization based on the type definition. If you can perform reasonable arithmetic on the field value you encode it as a number otherwise you should use text; even if the only possibly valid values are numbers. David J. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
