[SQL] RES: [GENERAL] The best book
Thanks to answer me. You was very kindly to write me a so nice text. :-) E.F.:> The "best book" for you really depends on what you are after! E.F.:> E.F.:> Are you interested in a book which is easy to read, gives good E.F.:> foundational concepts, and gives you decent skills with E.F.:> PostgreSQL, but E.F.:> may not make you a guru? Justin, I am a Oracle DBA and nowadways I need to be a Postgres guru. I love to study all of things about databases. Since I am the only responsible for postgres project in my company (PRODAM), I want and need to know all about postgres. I think I just know the basics. I want know detail about WAL, configuration variables and so on. E.F.:> E.F.:> Or, are you more after a book which is more technically E.F.:> advanced and E.F.:> challenging, and assumes you have already learnt the E.F.:> easier stuff? Yes, I am. E.F.:> There are also reference volumes available (if that's E.F.:> what you're after, E.F.:> but it doesn't sound like it). I have interests about a reference volumes. E.F.:> The online books which are available have a decent E.F.:> amount of material in E.F.:> them too, and you can buy hard copies of them so you E.F.:> have them nearby at E.F.:> all times. Where can I find these online books? E.F.:> So... for us to *really* be able to give you good E.F.:> suggestions, we really E.F.:> need to know more about what you're truly needing. E.F.:> E.F.:> Hope that helps. E.F.:> E.F.:> :-) E.F.:> E.F.:> Regards and best wishes, E.F.:> E.F.:> Justin Clift E.F.:> E.F.:> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] RES: [GENERAL] The best book
E.F.:> E.F.:> Hi, E.F.:> E.F.:> Well depends what are you looking for. E.F.:> E.F.:> I am using a lot PostgreSQL: Introduction and Concepts E.F.:> by Bruce Momjian. E.F.:> Then I have bought "PHP and PostgreSQL Advanced Web E.F.:> Programming" by E.F.:> Ewald Geschwinde and Hans-Juergen Schoenig E.F.:> E.F.:> The second one has as well PHP programming tips and same E.F.:> examples. The E.F.:> book is good and is covering as well PG administration. E.F.:> E.F.:> Mailing list I think is important and has good tips and E.F.:> don't forget the E.F.:> documentation: E.F.:> http://www.us.postgresql.org/users-lounge/docs/#7.2 I've just reada these books. E.F.:> E.F.:> You might find under: E.F.:> http://www.us.postgresql.org/books/index.html more E.F.:> details. E.F.:> E.F.:> stefan E.F.:> Thanks by this last link. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
RES: [SQL] RES: [GENERAL] set DateStyle to 'SQL'
Bom dia Roberto! I have just read these documents. The variables listed in the link below, say about variable that I cannot set from a .profile file. By the way, if I wat to state that all NUMERIC values must follow a format as ###,###.##& these docs do not say how to do that. I think this makes me clear for what I am look for. Thanks! :-) E.F.:> E.F.:> Perhaps you're looking for E.F.:> http://www.postgresql.org/idocs/index.php?runtime-config.html E.F.:> E.F.:> Please trim your replies to the list. E.F.:> E.F.:> -Roberto ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] 1 milion data insertion
> > Ok, this was a test. I'd like to know what would be happen. > But, from you, great PostGres DBA's, what is the best way to > insert a large number of data? > Is there a way to turn off the log? > Is there a way to commit each 100 records? Yes, "COPY" actually does an append. So just do what you do now 10,000 times for 100 records. It's a bit safer. I've done 1M recod COPYsmany times on a low-end PC, no trouble Put the log file someplace with more room. You should be able to run for a month without worrying about log files over filling Logging is controled likely from the startup script. Maybe in /etc/rc.d details depend on your OS. = Chris Albertson Home: 310-376-1029 [EMAIL PROTECTED] Cell: 310-990-7550 Office: 310-336-5189 [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] using LIMIT only on primary table
In article <3D42D7AA.27447.3EE190A0@localhost>, "Dan Langille" wrote: > This is an extension of the problem solved by > http://archives.postgresql.org/pgsql-sql/2002-03/msg00020.php but > with a slightly different complication. > > I want to get the last 100 port commits from the database. Commits > are stored in > commit_log and commit_log_ports relates commits to ports. A given > commit may > affect more than one port (i.e. there is a 1-N relationship between > commit_log and > commit_log_ports). > > So a starting point for the last 100 port commits is: > > explain analyze > SELECT distinct commit_log.* > FROM commit_log_ports, commit_log >WHERE commit_log.id = commit_log_ports.commit_log_id > ORDER BY commit_log.commit_date DESC, commit_log_ports.commit_log_id >LIMIT 100; > I am not sure if this will help, but how about a subselect? SELECT DISTINCT commit_log.* FROM commit_log_ports, (SELECT commit_log.id FROM commit_log ORDER BY commit_log.commit_date DESC LIMIT 100) AS commit_log WHERE commit_log.id = commit_log_ports.commit_log_id ORDER BY commit_log.commit_date DESC, commit_log.id LIMIT 100; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Extremely slow query
Did you know that you can probably change your GROUP BY clause to use a column ref, rather than repeating the CASE statement: GROUP BY gmmid, gmmname, divid, divname, feddept, deptname, fedvend, vendor_name, masterid, master_desc, pageid, oz_description, 13, price_original, price_owned_retail, cur_price, oz_color, oz_size, pageflag, itemnumber, mkd_status, option4_flag Doesn't help performance, but does help clarity :) Chris > query: > SELECT gmmid, gmmname, divid, divname, feddept, deptname, fedvend, > vendor_name, masterid, master_desc, pageid, oz_description, ( > CASE > WHEN (masterid IS NULL) THEN pageid > ELSE masterid END)::character varying(15) AS pagemaster, > CASE > WHEN (masterid IS NULL) THEN oz_description > ELSE master_desc > END AS pagemaster_desc, > CASE > WHEN (masterid IS NULL) THEN price_original > ELSE NULL::float8 > END AS org_price_display, > CASE > WHEN (masterid IS NULL) THEN cur_price > ELSE NULL::float8 > END AS cur_price_display, price_original, price_owned_retail, > cur_price, oz_color, oz_size, pageflag, itemnumber, > sum(cur_demandu + cur_returnu) AS cur_net_units, > sum(cur_demanddol + wtd_returndol) AS cur_net_dollar, > sum(wtd_demandu + wtd_returnu) AS wtd_net_units, > sum(wtd_demanddol + wtd_returndol) AS wtd_net_dollar, > sum(lw_demand + lw_returnu) AS lw_net_units, > sum(lw_demanddollar + lw_returndollar) AS lw_net_dollar, > sum(ptd_demanddollar + ptd_returndollar) AS ptd_net_dollar, > sum(ptd_demand + ptd_returnu) AS ptd_net_units, > sum(std_demanddollar + std_returndollar) AS std_net_dollar, > sum(std_demand + std_returnu) AS std_net_units, > sum(total_curoh) AS total_curoh, > sum(total_curoo) AS total_curoo, > sum((float8(total_curoh) * price_owned_retail)) AS curoh_dollar, > sum((float8(total_curoo) * price_owned_retail)) AS curoo_dollar, > sum(total_oh) AS total_oh, > sum(total_oo) AS total_oo, > sum((float8(total_oh) * price_owned_retail)) AS oh_dollar, > sum((float8(total_oh) * price_owned_retail)) AS oo_dollar, > mkd_status, > option4_flag > FROM tbldetaillevel_report detaillevel_report_v > GROUP BY gmmid, gmmname, divid, divname, feddept, deptname, fedvend, > vendor_name, masterid, master_desc, pageid, oz_description, > CASE > WHEN (masterid IS NULL) THEN pageid > ELSE masterid > END, > CASE > WHEN (masterid IS NULL) THEN oz_description > ELSE master_desc > END, > CASE > WHEN (masterid IS NULL) THEN price_original > ELSE NULL::float8 > END, > CASE > WHEN (masterid IS NULL) THEN cur_price > ELSE NULL::float8 > END, price_original, price_owned_retail, cur_price, > oz_color, oz_size, > pageflag, itemnumber, mkd_status, option4_flag ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Alter column
Hi, I wonder if I can alter the type of a column to a "supertype" of the original column type? Afaik, this was impossible in 7.1.3. However, I use 7.2.1. If yes, what would be the correct syntax? Cheers, Thiemo -- Thiemo Kellner Tösstalstrasse 146 CH-8400 Winterthur http://jermt.sourceforge.net ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: RES: [SQL] RES: [GENERAL] set DateStyle to 'SQL'
On Mon, Jul 29, 2002 at 08:57:12AM -0300, Elielson Fontanezi wrote: > Bom dia Roberto! Bom Dia! :-) > I have just read these documents. > The variables listed in the link below, say about variable that I > cannot > set from a .profile file. > By the way, if I wat to state that all NUMERIC values must follow a > format > as ###,###.##& these docs do not say how to do that. > I think this makes me clear for what I am look for. I don't know either, although I think this has been answered in the postgresql list in portuguese at http://br.groups.yahoo.com/group/postgresql-br/ The archives are searchable, so you could try there. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Advisor: The guy who told you how to screw up ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] contrib/dblink suggestion
Hi, I am using postgresql 7.2.1. I badly require to interconnect between databases. contrib/dblink seems to be handy and ofcourse it well suits my requirement. But while browsing across, i heard that it is not advicable to use it. So i wish to know someone's experience in using dblink and how handy it is. Will contrib/dblink be available with future postgresql releases? Valuable suggestions are very welcome. TIA. regards, bhuvaneswaran ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Returning PK of first insert for second insert use.
All, I have two tables t_proj, t_task see below: CREATE TABLE t_proj ( proj_id SERIAL NOT NULL, PRIMARY KEY (proj_id), task_id integer(12), user_id integer(6), title varchar(35), description varchar(80) ); CREATE TABLE t_task ( task_id SERIAL NOT NULL, PRIMARY KEY (task_id), title varchar(35), description varchar(80) ); When I insert into t_task I need to return the task_id (PK) for that insert to be used for the insert into the t_proj table. I tried using RESULT_OID but I have no idea how to obtain the true PK using this opague id. Below is the procedure I tried to use. CREATE OR REPLACE FUNCTION insertTask (varchar, varchar) RETURNS INTEGER AS ' DECLARE -- local variables oid1 INTEGER; retval INTEGER; BEGIN INSERT INTO t_task (title, description) VALUES ($1, $2); -- Get the oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID; retval := oid1; -- Everything has passed, return id as pk RETURN retval; END; ' LANGUAGE 'plpgsql'; Any help would be great! Thanks Again, -p ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Returning PK of first insert for second insert use.
Thank you for explaining that in detail it makes sense now. I'll give it a try. Thanks again! -p -Original Message- From: Ken Corey [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 1:05 PM To: Peter Atkins Cc: '[EMAIL PROTECTED]' Subject: RE: Returning PK of first insert for second insert use. On Mon, 2002-07-29 at 20:52, Peter Atkins wrote: > Is there a possibility of another application accessing the DB and using the > id before my function has completed the transaction? I'm concerned with the > possibility of cross-over of ID's if the insert hangs. > > There's no way to return the id of that insert inherently, and then use it > for the second insert? I think SQL uses something like ADD_ID, not sure. That's the beauty of the nextval statement. The database internally sequences requests to it so that you're kept out of harm's way. Say process A called the function,and nextval returns 16. The function now continues on its way, but is not finished when process B then calls the function (before A is done), and nextval returns 17. So, then function called by process A returns 16, and the function called by process B returns 17. That means that unless the results of process B depend in some way upon the results of process A, there's no problem. -Ken -- Ken Corey CTO http://www.atomic-interactive.com 07720 440 731 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Decision support query inefficiencies ...
Gunther Schadow <[EMAIL PROTECTED]> writes: > A very frequent decision support query we have to make is to get the > last act of a certain type that occurred before a certain cut-off > date. This seems pretty close to the type of problem DISTINCT ON is intended to solve. Look at the "weather report" example in the SELECT reference page. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Extremely slow query
System: OS: RedHat 7.2 Dual PIII XEON Mem 512 mg PG: 7.2 I have what I think is a fairly simple summary query, but it takes 1:55 to run on just 155k records. The query hits against a single table that I use for reporting purposes. This table is truncated, refreshed, reindexed, and vacuum analysed each night. Other than the initial table update, no other data is added during the day. Any help would be appreciated. My little Win2k with a PIII 500 and 256mgs is out performing this monster machine. query: SELECT gmmid, gmmname, divid, divname, feddept, deptname, fedvend, vendor_name, masterid, master_desc, pageid, oz_description, ( CASE WHEN (masterid IS NULL) THEN pageid ELSE masterid END)::character varying(15) AS pagemaster, CASE WHEN (masterid IS NULL) THEN oz_description ELSE master_desc END AS pagemaster_desc, CASE WHEN (masterid IS NULL) THEN price_original ELSE NULL::float8 END AS org_price_display, CASE WHEN (masterid IS NULL) THEN cur_price ELSE NULL::float8 END AS cur_price_display, price_original, price_owned_retail, cur_price, oz_color, oz_size, pageflag, itemnumber, sum(cur_demandu + cur_returnu) AS cur_net_units, sum(cur_demanddol + wtd_returndol) AS cur_net_dollar, sum(wtd_demandu + wtd_returnu) AS wtd_net_units, sum(wtd_demanddol + wtd_returndol) AS wtd_net_dollar, sum(lw_demand + lw_returnu) AS lw_net_units, sum(lw_demanddollar + lw_returndollar) AS lw_net_dollar, sum(ptd_demanddollar + ptd_returndollar) AS ptd_net_dollar, sum(ptd_demand + ptd_returnu) AS ptd_net_units, sum(std_demanddollar + std_returndollar) AS std_net_dollar, sum(std_demand + std_returnu) AS std_net_units, sum(total_curoh) AS total_curoh, sum(total_curoo) AS total_curoo, sum((float8(total_curoh) * price_owned_retail)) AS curoh_dollar, sum((float8(total_curoo) * price_owned_retail)) AS curoo_dollar, sum(total_oh) AS total_oh, sum(total_oo) AS total_oo, sum((float8(total_oh) * price_owned_retail)) AS oh_dollar, sum((float8(total_oh) * price_owned_retail)) AS oo_dollar, mkd_status, option4_flag FROM tbldetaillevel_report detaillevel_report_v GROUP BY gmmid, gmmname, divid, divname, feddept, deptname, fedvend, vendor_name, masterid, master_desc, pageid, oz_description, CASE WHEN (masterid IS NULL) THEN pageid ELSE masterid END, CASE WHEN (masterid IS NULL) THEN oz_description ELSE master_desc END, CASE WHEN (masterid IS NULL) THEN price_original ELSE NULL::float8 END, CASE WHEN (masterid IS NULL) THEN cur_price ELSE NULL::float8 END, price_original, price_owned_retail, cur_price, oz_color, oz_size, pageflag, itemnumber, mkd_status, option4_flag EXPLAIN ANALYSE results: Aggregate (cost=56487.32..72899.02 rows=15267 width=356) -> Group (cost=56487.32..66029.01 rows=152667 width=356) -> Sort (cost=56487.32..56487.32 rows=152667 width=356) -> Seq Scan on tbldetaillevel_report detaillevel_report_v (cost=0.00..9932.67 rows=152667 width=356) Table Def: CREATE TABLE tbldetaillevel_report ( pageid int4, feddept int4, fedvend int4, oz_description varchar(254), price_owned_retail float8, oz_color varchar(50), oz_size varchar(50), lw_demanddollar float8, ptd_demanddollar float8, std_demanddollar float8, lw_returndollar float8, ptd_returndollar float8, std_returndollar float8, lw_demand int4, ptd_demand int4, std_demand int4, lw_returnu int4, ptd_returnu int4, std_returnu int4, divid int4, divname varchar(35), gmmid int4, gmmname varchar(35), deptname varchar(35), total_oh int4, total_oo int4, vendorname varchar(40), dunsnumber varchar(9), current_week int4, current_period int4, week_end date, varweek int4, varperiod int4, upc int8, pageflag int2, upcflag int2, pid varchar(30), cur_price float8, vendor_name varchar(40), ly_lw_demanddollar float8, ly_ptd_demanddollar float8, ly_std_demanddollar float8, itemnumber varchar(15), mkd_status int2, lw_1_demanddollar float8, lw_2_demanddollar float8, lw_3_demanddollar float8, lw_4_demanddollar float8, masterid int4, master_desc varchar(254), cur_demandu int4, cur_demanddol float8, cur_returnu int4, cur_returndol float8, wtd_demandu int4, wtd_demanddol float8, wtd_returnu int4, wtd_returndol float8, total_curoh int4, total_curoo int4, curr_date date, lw_1_demand int4, lw_2_demand int4, lw_3_demand int4, lw_4_demand int4, option4_flag int2, option3_flag int2, price_original float8, price_ticket float8 ) Patrick Hatcher ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
