[SQL] How to join from two tables at once?
How can I join on one table with join conditions refering to two tables? In this example p is missing an entry that corresponds to u. I want to select from u and p, but have entries in u that don't have an entry in p. The problem is I need to go through table a to get the corresponding value in table p, and a LEFT JOIN only operates on two tables. The subselect works, but in real life turns out to be a big performance drain. - example: begin; create table u (uid int, aid int, txt text); create table a (id int, pkey int); create table p (uid int, pkey int, val text); insert into u VALUES(1,1,'one'); insert into u VALUES(2,1,'two'); insert into u VALUES(3,1,'three'); insert into a VALUES(1, 9); insert into p VALUES(1,9,'ONE'); insert into p VALUES(3,9,'THREE'); -- doesn't get 2, because there is no entry in p for it SELECT u.uid, u.txt, p.val FROM u,a,p WHERE u.uid = p.uid and a.id = u.aid and p.pkey = a.pkey; -- works, but uses a subselect SELECT u.uid, u.txt, (SELECT p.val FROM p WHERE p.uid = u.uid AND p.pkey = a.pkey) FROM u,a WHERE a.id = u.aid; --doesn't work: ERROR: JOIN/ON clause refers to "u", which is not part of JOIN SELECT u.uid, u.txt, p.val FROM u,a LEFT JOIN p ON p.uid = u.uid AND p.pkey = a.pkey WHERE a.id = u.aid; abort; ---(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 join from two tables at once?
On Tuesday 26 August 2003 02:58, Joseph Shraibman wrote: > How can I join on one table with join conditions refering to two tables? > In this example p is missing an entry that corresponds to u. I want to > select from u and p, but have entries in u that don't have an entry in p. > The problem is I need to go through table a to get the corresponding value > in table p, and a LEFT JOIN only operates on two tables. The subselect > works, but in real life turns out to be a big performance drain. (...) > -- doesn't get 2, because there is no entry in p for it > SELECT u.uid, u.txt, p.val FROM u,a,p WHERE u.uid = p.uid and a.id = u.aid > and p.pkey = a.pkey; > > -- works, but uses a subselect > SELECT u.uid, u.txt, (SELECT p.val FROM p WHERE p.uid = u.uid AND p.pkey = > a.pkey) FROM u,a WHERE a.id = u.aid; > > --doesn't work: ERROR: JOIN/ON clause refers to "u", which is not part of > JOIN SELECT u.uid, u.txt, p.val FROM u,a LEFT JOIN p ON p.uid = u.uid AND > p.pkey = a.pkey WHERE a.id = u.aid; Try: SELECT u.uid, u.txt, p.val FROM u INNER JOIN a ON (a.id=u.aid) LEFT JOIN p ON (p.pkey=a.pkey AND p.uid=u.uid) Ian Barwick [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] postgres 7.1.3: why does the query plan ignore indexes?
Should we consider adding some warning when someone creates an index on an int2 column? --- Tom Lane wrote: > Marco Vezzoli <[EMAIL PROTECTED]> writes: > > ewsweb_test=> \d measures_product > > Index "measures_product" > > Attribute | Type > > +-- > > product_id | smallint > > > > ewsweb_test=> explain select zero_yield, gross from measures where > > product_id=29 and date between '2003-03-12' and '2003-08-14'; > ^ > > "29" is taken as an integer (int4). To get an indexscan on an int2 > column, you need to explicitly cast it to int2: > product_id = 29::smallint > or you can put it in quotes and let the parser figure out the right > type: > product_id = '29' > > Yes, we'd like to make this better, but there are surprisingly many > pitfalls in tinkering with the assignment of datatypes to constants... > > regards, tom lane > > PS: you could also consider whether it's really saving you any space to > store product_id as a smallint instead of int. Because of alignment > considerations, it very possibly isn't. > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How to join from two tables at once?
On Mon, 25 Aug 2003, Joseph Shraibman wrote: > How can I join on one table with join conditions refering to two tables? In this > example > p is missing an entry that corresponds to u. I want to select from u and p, but have > entries in u that don't have an entry in p. The problem is I need to go through > table a > to get the corresponding value in table p, and a LEFT JOIN only operates on two > tables. > The subselect works, but in real life turns out to be a big performance drain. > > - > example: > > > begin; > > create table u (uid int, aid int, txt text); > create table a (id int, pkey int); > create table p (uid int, pkey int, val text); > > insert into u VALUES(1,1,'one'); > insert into u VALUES(2,1,'two'); > insert into u VALUES(3,1,'three'); > > insert into a VALUES(1, 9); > > insert into p VALUES(1,9,'ONE'); > insert into p VALUES(3,9,'THREE'); > > -- doesn't get 2, because there is no entry in p for it > SELECT u.uid, u.txt, p.val FROM u,a,p WHERE u.uid = p.uid and a.id = u.aid and > p.pkey = > a.pkey; > > -- works, but uses a subselect > SELECT u.uid, u.txt, (SELECT p.val FROM p WHERE p.uid = u.uid AND p.pkey = a.pkey) > FROM > u,a WHERE a.id = u.aid; > > --doesn't work: ERROR: JOIN/ON clause refers to "u", which is not part of JOIN > SELECT u.uid, u.txt, p.val FROM u,a LEFT JOIN p ON p.uid = u.uid AND p.pkey = a.pkey > WHERE a.id = u.aid; Probably you want something like: SELECT u.uid, u.txt, p.val FROM u INNER JOIN a ON (a.id=u.aid) LEFT JOIN p ON (p.uid=u.uid AND p.pkey=a.pkey); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Strange behavior with timestamptz
Hi Tom, I have written both the application and the PostgreSQL procedures (still in development). All the timestamps I referred to are created by default when the stored procedure is invoked. The problem may be due to some inconsistency in how PostgreSQL is interpreting what the operating system (Windows XP) is supplying. While I have not been able to recreate the problem directly, another aspect of the process does show an inconsistency. In the application the user enters the date the product was received (which may be different from the date the record is created). This is passed to the stored procedure as a parameter of type date, and is inserted into a date field (datereceived). By changing the system date and running the application the following occurred: Date received entered as August 11, 2003: ? me.datetimepicker1.value #8/11/2003 8:18:28 PM# base=# select datereceived, created from receiving where receivingid=56; datereceived | created --+-- 2003-11-08 | 2003-08-25 20:20:55.41425-05 (1 row) Date received entered as August 25, 2003: ? me.datetimepicker1.value #8/25/2003 8:22:37 PM# base=# select datereceived, created from receiving where receivingid=57; datereceived | created --+-- 2003-08-25 | 2003-08-25 20:22:39.68625-05 (1 row) base=# show datestyle; DateStyle --- ISO with US (NonEuropean) conventions (1 row) In both cases the month is being sent to the stored procedure first, but in the first instance (month < 13) it is being interpreted as the day. George - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "George Weaver" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 25, 2003 3:59 PM Subject: Re: [SQL] Strange behavior with timestamptz > "George Weaver" <[EMAIL PROTECTED]> writes: > > Does anyone have any idea why the default for seedlot recorded the time wit= > > h the day and month switched, resulting in the seedlot record being stamped= > > Nov 8, 2003 while the transaction was stamped correctly as Aug 11, 2003? > > It's really not possible to believe that both of those were loaded from > the defaults you show. now() doesn't ever break down the system clock > value into day/month/year --- it just takes the system clock time in > seconds-since-epoch and adds a constant to get the right zero offset. > So there's no credible mechanism for now() to make such a mistake. > > I think that your client software supplied a value for one field and > didn't supply a value for the other, and the supplied value was provided > in the wrong DateStyle. > > regards, tom lane > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Strange behavior with timestamptz
"George Weaver" <[EMAIL PROTECTED]> writes: > Does anyone have any idea why the default for seedlot recorded the time wit= > h the day and month switched, resulting in the seedlot record being stamped= > Nov 8, 2003 while the transaction was stamped correctly as Aug 11, 2003? It's really not possible to believe that both of those were loaded from the defaults you show. now() doesn't ever break down the system clock value into day/month/year --- it just takes the system clock time in seconds-since-epoch and adds a constant to get the right zero offset. So there's no credible mechanism for now() to make such a mistake. I think that your client software supplied a value for one field and didn't supply a value for the other, and the supplied value was provided in the wrong DateStyle. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [ZODB-Dev] What are the ZopeDB limit?
just one question You use zope with postgresql ?? No problem of connection ? Michele Bendazzoli wrote: On Mon, 2003-08-25 at 09:12, Michele Bendazzoli wrote: ops ... apologies for the message. Michele ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [ZODB-Dev] What are the ZopeDB limit?
On Tue, 2003-08-26 at 08:50, BenLaKnet wrote: > just one question > > You use zope with postgresql ?? yes. > No problem of connection ? Until now no. I use debian unstable. ciao, Michele ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] One-2-many relation - need distinct counts
Hi gurus, I have table A, B and need a distinct count of Accounts from A. There is a one-2-many relation between A and B. Accounts # in A are all unique. However, in B there will be duplicates. So the problem I have is, when I join as follows: Select Count(Distinct(account_no)) from A, B where A.Account_no = B.Account_no I get the correct count. If I do this: Select Count(Distinct(account_no)), B.Account_type from A, B where A.Account_no = B.Account_no group by B.Account_type I get wrong counts because there some are duplicated. I tried everything that I can think of - subquery, sub table etc. I would appreciate some help in writing the query. Thanks in advance. PS __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] postgres 7.1.3: why does the query plan ignore indexes?
Bruce Momjian <[EMAIL PROTECTED]> writes: > Should we consider adding some warning when someone creates an index on > an int2 column? I don't think so. Better to expend our energy on solving the fundamental problem. 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
Re: [SQL] Strange behavior with timestamptz
> ... When the datereceived parameter was defined > as type Date, it was actually arriving at the procedure as "11-08-2003" and > "25-08-2003" in spite of showing up as indicated below. When I redefined > the datereceived parameter as type OdbcDate, it arrived correctly at the > procedure as "2003-08-11" and "2003-08-25". Yeah. The first two formats are ambiguous, in Postgres' mind anyway. With the US datestyle setting, it will first attempt to parse as mm-dd- and if that fails try dd-mm-. So you'd need to change the datestyle to Euro to get dd-mm- input to be parsed reliably. As of 7.4 this is being tightened up, btw --- it'll be mm-dd- or error. But AFAICS this has nothing to do with a default now() insertion, because that value is never converted to a string before it gets into the stored column. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Strange behavior with timestamptz
Hi Tom, Not believing that PostgreSQL would be less consistent than Microsoft :-), I spent some time looking at how the application actually was transferring the date to the database procedure. When the datereceived parameter was defined as type Date, it was actually arriving at the procedure as "11-08-2003" and "25-08-2003" in spite of showing up as indicated below. When I redefined the datereceived parameter as type OdbcDate, it arrived correctly at the procedure as "2003-08-11" and "2003-08-25". Checking further, this appears to result from the computer's time settings (English - Canada) which I know realize use the European format. So it looks like this problem arose due to my inexperience. (Nonetheless I am still perplexed by the fact that the default values were assigned inconsistently as explained earlier and will try to puzzle this one through as well). Sorry for the confusion. George - Original Message - From: "George Weaver" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 25, 2003 9:51 PM Subject: Re: [SQL] Strange behavior with timestamptz > Hi Tom, > > I have written both the application and the PostgreSQL procedures (still in > development). All the timestamps I referred to are created by default when > the stored procedure is invoked. The problem may be due to some > inconsistency in how PostgreSQL is interpreting what the operating system > (Windows XP) is supplying. While I have not been able to recreate the > problem directly, another aspect of the process does show an inconsistency. > > In the application the user enters the date the product was received (which > may be different from the date the record is created). This is passed to > the stored procedure as a parameter of type date, and is inserted into a > date field (datereceived). By changing the system date and running the > application the following occurred: > > Date received entered as August 11, 2003: > > ? me.datetimepicker1.value > #8/11/2003 8:18:28 PM# > > base=# select datereceived, created from receiving where receivingid=56; > datereceived | created > --+-- > 2003-11-08 | 2003-08-25 20:20:55.41425-05 > (1 row) > > Date received entered as August 25, 2003: > > ? me.datetimepicker1.value > #8/25/2003 8:22:37 PM# > > base=# select datereceived, created from receiving where receivingid=57; > datereceived | created > --+-- > 2003-08-25 | 2003-08-25 20:22:39.68625-05 > (1 row) > > base=# show datestyle; >DateStyle > --- > ISO with US (NonEuropean) conventions > (1 row) > > In both cases the month is being sent to the stored procedure first, but in > the first instance (month < 13) it is being interpreted as the day. > > George > > - Original Message - > From: "Tom Lane" <[EMAIL PROTECTED]> > To: "George Weaver" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Monday, August 25, 2003 3:59 PM > Subject: Re: [SQL] Strange behavior with timestamptz > > > > "George Weaver" <[EMAIL PROTECTED]> writes: > > > Does anyone have any idea why the default for seedlot recorded the time > wit= > > > h the day and month switched, resulting in the seedlot record being > stamped= > > > Nov 8, 2003 while the transaction was stamped correctly as Aug 11, > 2003? > > > > It's really not possible to believe that both of those were loaded from > > the defaults you show. now() doesn't ever break down the system clock > > value into day/month/year --- it just takes the system clock time in > > seconds-since-epoch and adds a constant to get the right zero offset. > > So there's no credible mechanism for now() to make such a mistake. > > > > I think that your client software supplied a value for one field and > > didn't supply a value for the other, and the supplied value was provided > > in the wrong DateStyle. > > > > regards, tom lane > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Eliminating duplicate lists
TIA all... Four tables: content: content_id, content_name, ... content_features: content_id, feature_id device_features: device_id, feature_id device: device_id, device_name, ... One item of content can run on many devices, providing all the required features in "content_features" correspond to those in "device_features". One device can run many content items. Now - I have functions to return compatibility for a single piece of content, but I also need to build static compatibility lists, something of the form: content_compat: content_id, compat_list_id compat_list_info: compat_list_id, compat_list_name, ... compat_list: compat_list_id, device_id What I don't want are any duplicate lists. By which, I mean if list "A" contains devices 1,2,3 then there should be no list "B" which contains 1,2,3 (and no others). Of course, new content items and devices are added regularly and shouldn't require rebuilding the entire table (just to make life interesting). Solution 1 Introduce a "compat_uniq_code" into table "compat_list_info". This would be composed of all the features supported by this list, built via plpgsql, something of the form "content-type:7:8:9" for features 7,8,9. I can then use this as a key and checking for duplicates is easy. Note that the feature ids will have to be sorted. Solution 2 Have a temporary table - build each list there and then join against compat_list and make sure that for any given compat_list_id there are either: 1. items in temp_compat_list but not in compat_list 2. items in compat_list but not in compat_list You could avoid the temporary table with a temporary compat_list_id and a self-join on the compat_list table. Solution 1 is a somewhat ugly procedural hack, and 2 isn't going to be a simple query and is probably going to be slow. Anyone got any better ideas? -- Richard Huxton Archonet Ltd ---(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] Restore deleted records
Hello.I accidentally deleted some recordes from my table. How can I restorethem? Is it possible in 7.3.3?ThanksKonstantin Petrenko.
[SQL] Fetch the required rows
August 26th 2003 11:30p How do I get the required number of rows thru a query.. I mean...If I want to show 10 tuples per page.. instead of getting all the tuples from the relation and do some front end manipulation, is there any way by which I can get only 10 required tuples from the relation Have a grate day - Warm Regards Shÿam Peri II Floor, Punja Building, M.G.Road, Ballalbagh, Mangalore-575003 Ph : 91-824-2451001/5 Fax : 91-824-2451050 DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.August 26th 200311:30pHow do I get the required number of rows thru a query..I mean...If I want to show 10 tuples per page.. instead of getting all the tuples from the relation and do some front end manipulation, is there any way by which I can get only 10 required tuples from the relationHave a grate day- Warm Regards Shÿam Peri II Floor, Punja Building, M.G.Road, Ballalbagh, Mangalore-575003 Ph : 91-824-2451001/5 Fax : 91-824-2451050 DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Fetch the required rows
On Tue, Aug 26, 2003 at 23:15:48 +0530, [EMAIL PROTECTED] wrote: > August 26th 2003 > 11:30p > How do I get the required number of rows thru a query.. > I mean...If I want to show 10 tuples per page.. instead of getting all the tuples > from the relation and do some front end manipulation, is there any way by which I > can get only 10 required tuples from the relation You can use limit and offset to do this. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] regarding the same in db2 - fetch tuples
August 26th 2003 12:31p Thank you for the quick response.., can u plz tell me how can I do that in db2 also.. Have a grate day - Warm Regards Shÿam Peri II Floor, Punja Building, M.G.Road, Ballalbagh, Mangalore-575003 Ph : 91-824-2451001/5 Fax : 91-824-2451050 DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. August 26th 2003 12:31pThank you for the quick response.., can u plz tell me how can I do that in db2 also.. Have a grate day- Warm Regards Shÿam Peri II Floor, Punja Building, M.G.Road, Ballalbagh, Mangalore-575003 Ph : 91-824-2451001/5 Fax : 91-824-2451050 DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Restore deleted records
Hi, Konstantin Petrenko wrote, On 8/26/2003 11:37 AM: Hello. I accidentally deleted some recordes from my table. How can I restore them? Is it possible in 7.3.3? if you have a dump, or you are still in a transaction, you can rollback. C. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
