[SQL] How to use the template table in postgresql
Hello: I'm new user of postgresql. My problem is that: 1. How to create template table? Is it right of ' Create temptable test(..)'? 2.My application is Client/Server, can I has some workstations create the same template table name on the same time? If I could, How can I do with it? Thanks for your help.Stone. ---(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] SQL Query question
Hi Whilst I'm not new to SQL I am reasonably new to Postgres and as such I have a question on the following query: SELECT tbl1."TermTypeID", tbl1."ParentID", tbl1."KeywordID", tbl1."Term", tbl2."KeywordID" FROM "Terms" As tbl1 LEFT JOIN "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND tbl2."StockID" = 1 WHERE (tbl1."TermTypeID" >= 200) AND (tbl1."TermTypeID" < 600) AND (tbl1."IsSynonym" = false) AND (tbl1."LanguageID" = 1) ORDER BY tbl1."TermTypeID", tbl1."Term"; Why does the above query work fine and the folowing query not work? And as a additional kind of spanner in the works I've tried the following on MS SQL Server and Oracle both of which produce the correct results (i.e. the same as the above query). NB: the Terms table always has data whereas the SearchStore may or may not have any data. SELECT tbl1."TermTypeID", tbl1."ParentID", tbl1."KeywordID", tbl1."Term", tbl2."KeywordID" FROM "Terms" As tbl1 LEFT JOIN "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" WHERE (tbl1."TermTypeID" >= 200) AND (tbl1."TermTypeID" < 600) AND (tbl1."IsSynonym" = false) AND (tbl1."LanguageID" = 1) AND (tbl2."StockID" = 1) ORDER BY tbl1."TermTypeID", tbl1."Term"; Just to be obvious both queries as far as I can should return everything from Terms and anything if it exists from SearchStore subject to the WHERE clause parameters - obviously! Many thanks in advance Nick ---(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] SQL Query question
Thanks for the reply at least that explains it. Nick -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: 30 June 2005 12:22 To: Nick Stone Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] SQL Query question Nick Stone wrote: > Hi > > Whilst I'm not new to SQL I am reasonably new to Postgres and as such > I have a question on the following query: > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" AND > tbl2."StockID" = 1 Why does the above query work fine and the folowing > query not work? And as a additional kind of spanner in the works I've > tried the following on MS SQL Server and Oracle both of which produce > the correct results > FROM > "Terms" As tbl1 LEFT JOIN > "SearchStore" As tbl2 ON tbl1."KeywordID" = tbl2."KeywordID" > WHERE ... > (tbl2."StockID" = 1) Hmm - I'm not sure that MSSQL/Oracle are giving the "right" answer here. I assume the tbl2.stockid test is the issue here, and we apply the test after the join whereas the others push the condition inside the join. I'm inclined to prefer PG's way of doing things, since it means you get what you explicitly asked for (to my point of view anyway). Not sure what the SQL spec says though, and in the end I suppose that's the only way to decide "right". -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Some help please
Hi, I'm interested in using the connectby() function which I gather from the lists can be used in a similar way to the Oracle connect by ... PRIOR functionality. Does anybody know where there's an example of this in use or better still could somebody post an example. Many thanks Nick ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Some help please
That's great - thanks very much Nick -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: 01 July 2005 18:30 To: Nick Stone Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Some help please Nick, > I'm interested in using the connectby() function which I gather from > the lists can be used in a similar way to the Oracle connect by ... > PRIOR functionality. Does anybody know where there's an example of > this in use or better still could somebody post an example. Examples are in the /contrib directory where the connectby source is: /contrib/tablefunc/README.tablefunc -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(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] Recursive function
Hi, Also you could take a look at the connectby function as this will do what you want with ease and it can be used to sort stuff at the same time, produce tree views of data etc. Nick -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gnanavel Shanmugam Sent: 05 July 2005 06:05 To: pgsql-sql@postgresql.org Subject: [SQL] Recursive function Hi, I have a table with the following details. section_type_id | section_type_name | parent_section_type_id -+---+ 10 | Unit | 20 | Block | 10 30 | Practice | 20 40 | Sub Practice | 30 I've written a function as this CREATE or replace FUNCTION get_child_section_types(int) RETURNS setof int AS $$ declare v_section_type_id alias for $1; v_rec record; begin for v_rec in select section_type_id from master_section_type where parent_section_type_id=v_section_type_id loop return next v_rec.section_type_id; end loop; return; end; $$ LANGUAGE plpgsql; which returns output like, select * from get_child_section_types(10); get_child_section_types - 20 but I need the function to return all section types under the child nodes too. So, how to make the function to be recursive. with thanks & regards, S.Gnanavel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] left joins
I've had exactly yhe same problem - try changing the query to. select count(*) from h left join p using (r,pos) and p.r_order=1 where h.tn > 20 and h.tn < 30 I think that should do it - the syntax you used would work in Oracle and MS SQL but there's a subtle difference with the way Postgres works that means that any NULLS in the right hand side of the join will be ignored effectively making it an inner join Hope this helps Nick -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Grant Morgan Sent: 06 July 2005 11:02 To: pgsql-sql@postgresql.org Subject: [SQL] left joins I am having a problem with left joins in Postgresql.(probably my misunderstanding of left joins) My first Query returns 70,000 select count(*) from h where h.tn > 20 and h.tn < 30 my left join returns only 34,000 select count(*) from h left join p using (r,pos) where h.tn > 20 and h.tn < 30 and p.r_order=1 since it is a left join I though I should get a number no smaller in the left join than the original unjoined query. It seems to be acting like an inner join. Both of these are tables not views and both have hash indices on r column. I have tried left joins, right joins , and both using and on , nothing seems make a difference. Questions 1)should a left join return atleast as many rows as the unjoined left table? 2)am I doing something wrong above? 3)If am not doing anything wrong is this postgresql problem and is there a work around? Cheers, Grant ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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] How to alias table columns in result?
Yes - just alias the columns you need to alias Nick -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of nori Sent: 11 August 2005 10:48 To: Mischa Sandberg Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] How to alias table columns in result? Thanks Sorry, my question was missing one important detail. My tables have quite a lot columns (which unfortunately have same names in both tables) so is it possible to do same as below but without specifying alias for each column. Now my queries are long and they do not look nice. boris On 8/11/05, Mischa Sandberg <[EMAIL PROTECTED]> wrote: > SELECT d.name as "d.name", >d.index as "d.index", >c.name as "c.name", >... ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: 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] SQL Newbie
Hope this helps SELECT * FROM speed_history as outside etc.. WHERE (speed = ( SELECT speed FROM speed_history as inside etc.. WHERE (outside.interface = inside.interface) LIMIT 1 ORDER BY speed DESC ) ) Hopefully you get the idea - basically it's a corelated sub-query - very useful Nick -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lane Van Ingen Sent: 12 August 2005 16:09 To: pgsql-sql@postgresql.org Subject: [SQL] SQL Newbie It seems to me that I should be able to do this, but after 5 hrs of trying, I can't figure this one out. I could do this in two queries, but seems like I should be able to do this in one. What I am trying to do: Find the highest speed at which each interface of a router has run over time. I have three tables, two of which (interface, speed_history) are being used in this query (primary / foreign key fields noted as PK / FK): router-> 1:M -> interface -> 1:M -> speed_history --- --- -- - router_no (int2) PK interface_id (int4) PK interface_id (int4) PK name (varchar) router_no (int2) FK updated_time (timestamp) PK link_description (varchar) speed(int4) Data in speed history looks like this: interface_id updated_time speed 1 2005-08-11 08:10:23 450112 1 2005-08-11 10:53:34 501120 <--- 1 2005-08-11 10:58:11 450112 2 2005-08-11 08:10:23 450112 <--- 2 2005-08-11 11:00:44 350234 3 2005-08-11 08:10:23 450112 <--- The rows of speed_history I want back are marked above with ' <--- '. Query results should look like: interface.interface_id interface.link_description speed_history.updated_time speed_history.speed ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
