[GENERAL] sql question
Hi all My table definition : id | fref | mref --+---+-- 1 | 23 | 25 2 | 24 | 28 3 | 25 | 31 4 | 26 | 34 My problem : i need a query that results in this : id | ref --+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34 Do I need a crosstab-query ? Who can help me ? -- A computer is like an airconditioner. When windows open, it stops working ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Steven Verhoeven, ICT Support Engineer Department for Molecular Biomedical Research (DMBR) VIB - Ghent University 'Fiers-Schell-Van Montagu' building Technologiepark 927B - 9052 Ghent (Zwijnaarde) Belgium Tel : +32-(0)9-33-13.606 Fax : +32-(0)9-33-13.609 E-mail : [EMAIL PROTECTED] URL : http://www.dmbr.UGent.be
[GENERAL] sql question
My table definition : id | fref | mref --+---+-- 1 | 23| 25 2 | 24| 28 3 | 25| 31 4 | 26| 34 i need a query that results in this : id |ref --+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34 Do I need a crosstab-query ? Who can help me ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] SQL Question
Hi, i have a table ProdId | LastUpdate ---+ 100| 2005-04-01 100| 2005-03-01 100| 2005-02-01 200| 2005-04-01 200| 2005-03-01 200| 2005-02-01 - How can i select only the newest record for each ProdId ? 100| 2005-04-01 200| 2005-04-01 - How can i select to retrieve the last 2 dates in record 100| 2005-04-01 | 2005-03-01 200| 2005-04-01 | 2005-03-01 Thanks Alex ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] SQL question.
Hello SQL Aces ! I want to do a select on a table distinct on linkid and sorted by date. I have try this SELECT DISTINCT ON (linkid) * FROM all_links WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day', TIMESTAMP '2005-06-01') ORDER BY linkid, read_date; With this request "works" but the raws are sorted as I want. To have the rows sorted by date I have done this using a temporary table and that works exactly as I want. CREATE LOCAL TEMP TABLE temp_links WITHOUT OIDS AS SELECT DISTINCT ON (linkid) * FROM all_links WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day', TIMESTAMP '2005-06-01') ORDER BY linkid; SELECT * from temp_links ORDER BY read_date DESC limit 100 My question is... How can I do the same thing in the more efficient way and without using a temporary table. Since I am using PHP and the table is not deleted at the end of the program because PHP keeps the connection to the database open. Thanks for any suggestions. Fred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Here's a question for the SQL guru's out there, which I've been trying to solve for the last couple of hours. There's got to be a solution to this, but somehow I can't find it. Tables: table1 ( uid int PK, uname varchar(64) ) table2 ( uid int FK to table1, xuid int FK to table 1 ) table3 ( uid int FK to table1, yuid int FK to table1 ) There might be more tables of the type like table2 and table3, but I'd already be happy to solve the puzzle with the 3 tables above. Ok, assume table1 is the master table - in my case a table used for login authentication (some columns removed above) table2 and table3 are tables where the uid always references to the uid in table1. The second "uid" (xuid and yuid in this example) references to another uid record in table1. The problem is that there may or may not be entries in table2 (or table3) referencing a specific uid in their second uid field. Maybe some data: table1: 1 test1 2 test2 3 test3 table2: 1 2 1 3 3 1 table3: 1 2 2 3 3 2 What I want to do in a view is the following resultset: uid uname xuid yuid 1test1 2 2 1test1 3 2test2 3 3test3 1 3test3 2 So basically I want to know which uid is connected to which uid, one relationship per row. So xuid and yuid shall be identical if records exist in both table2 and table3 or the value shall be NULL if a corresponding record can't be found in either table2 or table3. Can anyone here help me out? Thanks a lot UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjVqVjqGXBvRToM4RAhk5AKCjjGc4VilA45PnPZoKluTNYUP6FACgrRQq XFIvkCIJHyz7TvvV/XxL4Lk= =/vxG -END PGP SIGNATURE- ---(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
[GENERAL] SQL question
Hi List; I have a table that has 3 date columns : create table xyz ( xyz_id integer, date1 timestamp, date2 timestamp, date3 timestamp ) I want to select in a query the xyz_id and the max date column for each row something like : create table temp2 as select xyz_id (max date?) where ... Is this - the (max date?) part a case scenario or is there a better, more efficient method ? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQL Question
I am using RH5.2 / Postgre 6.3.2 I need a query that has a having clause. In 6.3.2 it says that having is not supported yet. I looked at the changes in 6.4 and it appears that 6.4.2 supports this. Is this true? Also I have found 6.4.2 rpms but no data rpm so now when I try to use psql it says pgReadData() -- backend closed the channel unexpectedly. Please help. Matt
[GENERAL] sql question
I have a sql problem which I can't solve. The following table is defined create table AdressGroup ( AdrGroup_Id INTEGER NOT NULL DEFAULT NEXTVAL('adrverw_id_seq'), ZeitDATETIME NOT NULL, Group_IdINTEGER NOT NULL, Adress_Id INTEGER NOT NULL ); The table assigns adresses to adressgroups. I'd like to select the adresses which belongs to one specific adressGroup and to no other group. If an adress has more than one entry in the AdressGroup table it should not be in the projection. I tried the following: SELECT * FROM adressGroup WHERE Group_Id = 6 AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id GROUP BY AdrGroup_Id HAVING COUNT(*) == 1 ); When I do this I get the following error: ERROR: rewrite: aggregate column of view must be at rigth side in qual ERROR: rewrite: aggregate column of view must be at rigth side in qual Does anybody knows the solution? Thanks -- ~~~ Herbert LiechtiE-Mail: [EMAIL PROTECTED] ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~
[GENERAL] sql question
Please help, I am trying to write an SQL statement but with no success as I am just starting out with sql. I have a table with 3 columns: Account# ,OrderType and date example of data: Account#¦ Ordertype ¦ Date 1 ¦ A ¦ April 1 ¦ B ¦ May 1 ¦ B ¦ May 2 ¦ B ¦ April 2 ¦ B ¦ May 2 ¦ C ¦ May 3 ¦ C ¦ May I need to write a select that will show me the totals of EACH type for EACH account AND total ordersplaced for a SPECIFIC month eg..Show me the results for May... account ¦ TotA ¦ TotB ¦ TotC ¦ Total 1 ¦ 0 ¦ 2 ¦ 0 ¦ 2 2 ¦ 0 ¦ 1 ¦ 1 ¦ 2 3 ¦ 0 ¦ 0 ¦ 1 ¦ 1 I can use temp tables, but need a solution written as basic as pssible so I can understand it (all in the form select this from that) any help would be fantastic as I am completely stuck and have been trying for about a week thanks [EMAIL PROTECTED] -- Sent by sgebbie from ciaoweb piece from it This is a spam protected message. Please answer with reference header. Posted via http://www.usenet-replayer.com/cgi/content/new ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] sql question
SELECT t1.id , t1.fref FROM t1 UNION ALL SELECT t2.id , t2.mref FROM t2 - Original Message - From: Steven Verhoeven To: pgsql-general@postgresql.org ; [EMAIL PROTECTED] Sent: Friday, March 11, 2005 4:36 AM Subject: [GENERAL] sql question Hi all My table definition : id | fref | mref--+---+-- 1 | 23 | 25 2 | 24 | 28 3 | 25 | 31 4 | 26 | 34My problem : i need a query that results in this : id | ref--+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34Do I need a crosstab-query ?Who can help me ? -- A computer is like an airconditioner. When windows open, it stops working ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Steven Verhoeven, ICT Support Engineer Department for Molecular Biomedical Research (DMBR)VIB - Ghent University 'Fiers-Schell-Van Montagu' buildingTechnologiepark 927B - 9052 Ghent (Zwijnaarde)Belgium Tel : +32-(0)9-33-13.606 Fax : +32-(0)9-33-13.609 E-mail : [EMAIL PROTECTED] URL : http://www.dmbr.UGent.be
Re: [GENERAL] sql question
On Fri, 11 Mar 2005 11:36 pm, Steven Verhoeven wrote: > Hi all > > My table definition : > >id | fref | mref > --+---+-- > 1 | 23| 25 > 2 | 24| 28 > 3 | 25| 31 > 4 | 26| 34 > > My problem : > i need a query that results in this : > >id |ref > --+-- > 1 | 23 > 1 | 25 > 2 | 24 > 2 | 28 > 3 | 25 > 3 | 31 > 4 | 26 > 4 | 34 > SELECT id, fref as ref FROM table UNION ALL SELECT id, mref as ref FROM table; Should do the trick. > > Do I need a crosstab-query ? > Who can help me ? > > ---(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
Re: [GENERAL] sql question
On Fri, 11 Mar 2005 13:36:17 +0100, Steven Verhoeven <[EMAIL PROTECTED]> wrote: > Hi all > > My table definition : > >id | fref | mref > --+---+-- > 1 | 23| 25 > 2 | 24| 28 > 3 | 25| 31 > 4 | 26| 34 > > My problem : > i need a query that results in this : > >id |ref > --+-- > 1 | 23 > 1 | 25 > 2 | 24 > 2 | 28 > 3 | 25 > 3 | 31 > 4 | 26 > 4 | 34 > > > Do I need a crosstab-query ? select id, fref from mytable union all select id, mref from mytable order by 1,2 klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] sql question
On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven <[EMAIL PROTECTED]> wrote: > My table definition : > >id | fref | mref > --+---+-- > 1 | 23| 25 > 2 | 24| 28 > 3 | 25| 31 > 4 | 26| 34 > > i need a query that results in this : > >id |ref > --+-- > 1 | 23 > 1 | 25 > 2 | 24 > 2 | 28 > 3 | 25 > 3 | 31 > 4 | 26 > 4 | 34 > > Do I need a crosstab-query ? > Who can help me ? select id, fref as ref from my_table union select id, mref as ref from my_table; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] sql question
On Sun, 2005-03-13 at 23:13 -0600, George Essig wrote: > On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven > <[EMAIL PROTECTED]> wrote: > [snip problem] > select id, fref as ref from my_table > union > select id, mref as ref from my_table; union ALL (see other replies) gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] sql question
Steven Verhoeven wrote: Hi all My table definition : id | fref | mref --+---+-- 1 | 23| 25 2 | 24| 28 3 | 25| 31 4 | 26| 34 My problem : i need a query that results in this : id |ref --+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34 Do I need a crosstab-query ? Who can help me ? How about select id, mref AS ref from table UNION select id, fref AS ref from table Or is that not what you want? Best Wishes, Chris Travers Metatron Technology Consulting -- /A computer is like an airconditioner. When windows open, it stops working ! / -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Steven Verhoeven, ICT Support Engineer Department for Molecular Biomedical Research (DMBR) VIB - Ghent University 'Fiers-Schell-Van Montagu' building Technologiepark 927B - 9052 Ghent (Zwijnaarde) Belgium Tel : +32-(0)9-33-13.606 Fax : +32-(0)9-33-13.609E-mail : [EMAIL PROTECTED] URL : http://www.dmbr.UGent.be ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SQL Question
On 15.04.2005 13:58 Alex wrote: > Hi, > > i have a table > > ProdId | LastUpdate > ---+ > 100| 2005-04-01 > 100| 2005-03-01 > 100| 2005-02-01 > 200| 2005-04-01 > 200| 2005-03-01 > 200| 2005-02-01 > > - How can i select only the newest record for each ProdId ? > > 100| 2005-04-01 > 200| 2005-04-01 > SELECT prodid, lastupdate FROM produpdate p1 WHERE lastupdate = (SELECT MAX (lastupdate) FROM produpdate p2 WHERE p2.prodid = p1.prodid) Thomas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SQL Question
For the first request (How can i select only the newest record for each ProdId ?), you can do : select * from test."tableProd" u where u."LastUpdate" = (select max(t."LastUpdate") from test."tableProd" t where u."ProdId" = t."ProdId") -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] la part de Alex Envoyé : vendredi 15 avril 2005 13:59 À : pgsql-general@postgresql.org Objet : [GENERAL] SQL Question Hi, i have a table ProdId | LastUpdate ---+ 100| 2005-04-01 100| 2005-03-01 100| 2005-02-01 200| 2005-04-01 200| 2005-03-01 200| 2005-02-01 - How can i select only the newest record for each ProdId ? 100| 2005-04-01 200| 2005-04-01 - How can i select to retrieve the last 2 dates in record 100| 2005-04-01 | 2005-03-01 200| 2005-04-01 | 2005-03-01 Thanks Alex ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message. This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. Security Notice: all e-mail, sent to or from this address, may be accessed by someone other than the recipient, for system management and security reasons. This access is controlled under Regulation of Investigatory Powers Act 2000, Lawful Business Practises. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SQL Question
GIROIRE, Nicolas (COFRAMI) wrote: For the first request (How can i select only the newest record for each ProdId ?), you can do : select * from test."tableProd" u where u."LastUpdate" = (select max(t."LastUpdate") from test."tableProd" t where u."ProdId" = t."ProdId") Although this only guarantees one row if LastUpdate is unique for every ProdId. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SQL Question
Ok but you haven't specify that and in your example there is no similar use one solution is to change LastUpdate type and use timestamp. to insert you make : insert into test."tableProd" values (100, '2004-05-01 02:52:12') but it exists other format for timesee postgresql doc for different type (here the french version http://traduc.postgresqlfr.org/pgsql-fr/datatype-datetime.html) With timestamp the accuracy is better, you have until second. -Message d'origine- De : Richard Huxton [mailto:[EMAIL PROTECTED] Envoyé : vendredi 15 avril 2005 15:42 À : GIROIRE, Nicolas (COFRAMI) Cc : Alex; pgsql-general@postgresql.org Objet : Re: [GENERAL] SQL Question GIROIRE, Nicolas (COFRAMI) wrote: > For the first request (How can i select only the newest record for each > ProdId ?), you can do : > > select * from test."tableProd" u > where u."LastUpdate" = (select max(t."LastUpdate") > from test."tableProd" t > where u."ProdId" = t."ProdId") Although this only guarantees one row if LastUpdate is unique for every ProdId. -- Richard Huxton Archonet Ltd This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message. This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. Security Notice: all e-mail, sent to or from this address, may be accessed by someone other than the recipient, for system management and security reasons. This access is controlled under Regulation of Investigatory Powers Act 2000, Lawful Business Practises. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL Question
select max(lastupdate),prodid from tablename group by prodid -Original Message- From: Thomas Kellerer <[EMAIL PROTECTED]> Subj: Re: [GENERAL] SQL Question Date: Fri Apr 15, 2005 7:24 am Size: 621 bytes To: pgsql-general@postgresql.org On 15.04.2005 13:58 Alex wrote: > Hi, > > i have a table > > ProdId | LastUpdate > ---+ > 100| 2005-04-01 > 100| 2005-03-01 > 100| 2005-02-01 > 200| 2005-04-01 > 200| 2005-03-01 > 200| 2005-02-01 > > - How can i select only the newest record for each ProdId ? > > 100| 2005-04-01 > 200| 2005-04-01 > SELECT prodid, lastupdate FROM produpdate p1 WHERE lastupdate = (SELECT MAX (lastupdate) FROM produpdate p2 WHERE p2.prodid = p1.prodid) Thomas ---(end of broadcast)--- TIP 8: explain analyze is your friend Joseph M Day Global Innovative Solutions 651 W Washington Blvd Chicago, IL 60661 D: (312) 371-3054 F: (312) 421-8557 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SQL Question
[EMAIL PROTECTED] wrote on 15.04.2005 16:42: select max(lastupdate),prodid from tablename group by prodid Even better :) Thomas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SQL Question
On Fri, Apr 15, 2005 at 21:58:31 +1000, Alex <[EMAIL PROTECTED]> wrote: > Hi, > > i have a table > > ProdId | LastUpdate > ---+ > 100| 2005-04-01 > 100| 2005-03-01 > 100| 2005-02-01 > 200| 2005-04-01 > 200| 2005-03-01 > 200| 2005-02-01 > > - How can i select only the newest record for each ProdId ? > > 100| 2005-04-01 > 200| 2005-04-01 You can also use the Postgres specific DISTINCT ON clause. SELECT DISTINCT ON (prodid) prodid, lastupdate ORDER BY prodid, lastupdate DESC; ---(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
Re: [GENERAL] SQL Question
From: "Alex" <[EMAIL PROTECTED]> - How can i select only the newest record for each ProdId ? 100| 2005-04-01 200| 2005-04-01 DISTINCT ON was made for this and on the similar tables I have performs rather more efficiently than using a subquery. select distinct on (ProdId) ProdId , LastUpdate from produpdate order by ProdId , LastUpdate desc; - How can i select to retrieve the last 2 dates in record 100| 2005-04-01 | 2005-03-01 200| 2005-04-01 | 2005-03-01 To get the previous one, my first thought is something like: select distinct on (ProdId) ProdId , LastUpdate from produpdate p1 where LastUpdate <> ( select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId ) order by ProdId , LastUpdate desc ; but there may be a much more efficient way of getting the nth result in general. Julian Scarfe ---(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: [GENERAL] SQL Question
Julian Scarfe wrote: From: "Alex" <[EMAIL PROTECTED]> - How can i select only the newest record for each ProdId ? 100| 2005-04-01 200| 2005-04-01 DISTINCT ON was made for this and on the similar tables I have performs rather more efficiently than using a subquery. select distinct on (ProdId) ProdId , LastUpdate from produpdate order by ProdId , LastUpdate desc; - How can i select to retrieve the last 2 dates in record 100| 2005-04-01 | 2005-03-01 200| 2005-04-01 | 2005-03-01 To get the previous one, my first thought is something like: select distinct on (ProdId) ProdId , LastUpdate from produpdate p1 where LastUpdate <> ( select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId ) order by ProdId , LastUpdate desc ; but there may be a much more efficient way of getting the nth result in general. Julian Scarfe ---(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 Thanks for the help. will give it a try. Alex ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL question.
how about (untested) select * from ( SELECT DISTINCT ON (linkid) * FROM all_links WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',TIMESTAMP '2005-06-01') ORDER BY linkid ) A ORDER BY read_date DESC limit 100 -- Original Message --- From: FC <[EMAIL PROTECTED]> To: pgsql-general@postgresql.org Sent: Wed, 1 Jun 2005 16:40:48 +0200 Subject: [GENERAL] SQL question. > Hello SQL Aces ! > > I want to do a select on a table distinct on linkid and sorted by > date. I have try this > > SELECT DISTINCT ON (linkid) * FROM all_links >WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day', > TIMESTAMP '2005-06-01') ORDER BY linkid, read_date; > > With this request "works" but the raws are sorted as I want. To have > the rows sorted by date I have done this using a temporary table and > that works exactly as I want. > > CREATE LOCAL TEMP TABLE temp_links WITHOUT OIDS AS >SELECT DISTINCT ON (linkid) * FROM all_links >WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day', > TIMESTAMP '2005-06-01') ORDER BY linkid; > > SELECT * from temp_links ORDER BY read_date DESC limit 100 > > My question is... How can I do the same thing in the more efficient > way and without using a temporary table. Since I am using PHP and the > table is not deleted at the end of the program because PHP keeps the > connection to the database open. > > Thanks for any suggestions. > Fred > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) --- End of Original Message --- ---(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: [GENERAL] SQL question.
On Wed, Jun 01, 2005 at 04:40:48PM +0200, FC wrote: > > Hello SQL Aces ! > > I want to do a select on a table distinct on linkid and sorted by > date. I have try this How about a subquery?: SELECT * FROM ( SELECT DISTINCT ON (linkid) * FROM all_links WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day', TIMESTAMP '2005-06-01') ORDER BY linkid, read_date) AS sub ORDER BY read_date DESC limit 100; Hope this helps, > My question is... How can I do the same thing in the more efficient > way and without using a temporary table. Since I am using PHP and the > table is not deleted at the end of the program because PHP keeps the > connection to the database open. -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp0eOwCUcCBC.pgp Description: PGP signature
Re: [GENERAL] SQL question.
I need to remember to keep things simple... Works fine, thanks. Fred On Jun 1, 2005, at 5:20 PM, Jim Buttafuoco wrote: how about (untested) select * from ( SELECT DISTINCT ON (linkid) * FROM all_links WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC ('day',TIMESTAMP '2005-06-01') ORDER BY linkid ) A ORDER BY read_date DESC limit 100 -- Original Message --- From: FC <[EMAIL PROTECTED]> To: pgsql-general@postgresql.org Sent: Wed, 1 Jun 2005 16:40:48 +0200 Subject: [GENERAL] SQL question. Hello SQL Aces ! I want to do a select on a table distinct on linkid and sorted by date. I have try this SELECT DISTINCT ON (linkid) * FROM all_links WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day', TIMESTAMP '2005-06-01') ORDER BY linkid, read_date; With this request "works" but the raws are sorted as I want. To have the rows sorted by date I have done this using a temporary table and that works exactly as I want. CREATE LOCAL TEMP TABLE temp_links WITHOUT OIDS AS SELECT DISTINCT ON (linkid) * FROM all_links WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day', TIMESTAMP '2005-06-01') ORDER BY linkid; SELECT * from temp_links ORDER BY read_date DESC limit 100 My question is... How can I do the same thing in the more efficient way and without using a temporary table. Since I am using PHP and the table is not deleted at the end of the program because PHP keeps the connection to the database open. Thanks for any suggestions. Fred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) --- End of Original Message --- ---(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: [GENERAL] [SQL] question
The issue has been solved thanks to a custom nullif_int() function. Which if anyone has the same issue, it was solved with... CREATE FUNCTION nullif_int(text) RETURNS integer AS 'SELECT nullif($1,)::int;' LANGUAGE SQL; SELECTS were not the issue; INSERT INTO a non-text column was the issue. Thanks anyway, Matthew --- "Thomas F. O'Connell" <[EMAIL PROTECTED]> wrote: > Matt, > > In PostgreSQL 8.0.3, I see: > > postgres=# select nullif( '1', '' ); > nullif > > 1 > (1 row) > > postgres=# select nullif( '', '' ) is null; > ?column? > -- > t > (1 row) > > What behavior are you expecting? > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > > Strategic Open Source: Open Your i > > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-469-5150 > 615-469-5151 (fax) > > On Aug 24, 2005, at 12:05 AM, Matt A. wrote: > > > I have a rating section on a website. It has radio > > buttons with values 1-5 according to the rating > level. > > Lastly there is a null option for n/a. We use null > for > > n/a so it's excluded from the AVG() calculations. > > > > We used nullif('$value','') on inserts in mssql. > We > > moved to postgres and love it but the nullif() > doesn't > > match empty strings to each other to return null > other > > than a text type, causing an error. This is a > major > > part of our application. > > > > AKA nullif('1','') would insert 1 as integer even > > though wrapped in ''. Also nullif('','') would > > evaluate NULL and insert the "not a value" > > accordingly. > > > > Is there a workaround for this so it doesn't break > our > > rating system? We cannot always enter a value for > a > > integer column. Is there any other way to > accomplish > > this? Please help. > > > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! Mail - You care about security. So do we. > > http://promotions.yahoo.com/new_mail > > > > ---(end of > > broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If someone knows this it would be great - because I'm still curious how to solve it. However I just remodelled my db structure to eliminate the problem (basically I pulled the several tables into one since each of the table2/table3 tables only has 3 fields) so now I do: table2 ( uid int FK to table1, luid int FK to table1, is_in_table3 boolean, is_in_table4 boolean, . ) this just needs a simple select with a join against table1. UC On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote: > Here's a question for the SQL guru's out there, which I've been trying to > solve for the last couple of hours. There's got to be a solution to this, > but somehow I can't find it. > > Tables: > > table1 ( > uid int PK, > uname varchar(64) > ) > > table2 ( > uid int FK to table1, > xuid int FK to table 1 > ) > > table3 ( > uid int FK to table1, > yuid int FK to table1 > ) > > There might be more tables of the type like table2 and table3, but I'd > already be happy to solve the puzzle with the 3 tables above. > Ok, assume table1 is the master table - in my case a table used for login > authentication (some columns removed above) > table2 and table3 are tables where the uid always references to the uid in > table1. The second "uid" (xuid and yuid in this example) references to > another uid record in table1. The problem is that there may or may not be > entries in table2 (or table3) referencing a specific uid in their second > uid field. > Maybe some data: > > table1: > 1 test1 > 2 test2 > 3 test3 > > table2: > 1 2 > 1 3 > 3 1 > > table3: > 1 2 > 2 3 > 3 2 > > What I want to do in a view is the following resultset: > > uid uname xuid yuid > 1test1 2 2 > 1test1 3 > 2test2 3 > 3test3 1 > 3test3 2 > > > So basically I want to know which uid is connected to which uid, one > relationship per row. So xuid and yuid shall be identical if records exist > in both table2 and table3 or the value shall be NULL if a corresponding > record can't be found in either table2 or table3. > > Can anyone here help me out? > > Thanks a lot > > UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjXebjqGXBvRToM4RAq+kAKCyK1bwfRBdVoAvoi0boELr90wSmwCdHUEf p9L9Z1OSHwqvYn+ZnDWSTQw= =Ih7b -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SQL question
I'm afraid, I'm not used to SQL92 join syntax and almost all my experience is in Oracle but how about: SELECT t1.uid , t1.xname , t2.uid , t3.uid FROM table1 t1 INNER JOIN table2 t2 ON t1.uid = t2.uid INNER JOIN table3 t3 ON t2.uid = t3.uid UNION SELECT t1.uid , t1.xname , t2.uid , NULL FROM table1 t1 INNER JOIN table2 t2 ON t1.uid = t2.uid WHERE NOT EXISTS (SELECT NULL FROM table3 t3 WHERE t3.uid = t1.uid ) UNION SELECT t1.uid , t1.xname , NULL , t3.uid FROM table1 t1 INNER JOIN table3 t3 ON t1.uid = t3.uid WHERE NOT EXISTS (SELECT NULL FROM table2 t2 WHERE t2.uid = t3.uid ) Perhaps there was a solution using outer joins and case statements within the SELECT clause. Perhaps there is also a solution using subselects in the SELECT clause. However, this is all I can do for tonight. Vincent - Original Message - From: "Uwe C. Schroeder" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, November 06, 2004 3:13 PM Subject: [GENERAL] SQL question -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Here's a question for the SQL guru's out there, which I've been trying to solve for the last couple of hours. There's got to be a solution to this, but somehow I can't find it. Tables: table1 ( uid int PK, uname varchar(64) ) table2 ( uid int FK to table1, xuid int FK to table 1 ) table3 ( uid int FK to table1, yuid int FK to table1 ) There might be more tables of the type like table2 and table3, but I'd already be happy to solve the puzzle with the 3 tables above. Ok, assume table1 is the master table - in my case a table used for login authentication (some columns removed above) table2 and table3 are tables where the uid always references to the uid in table1. The second "uid" (xuid and yuid in this example) references to another uid record in table1. The problem is that there may or may not be entries in table2 (or table3) referencing a specific uid in their second uid field. Maybe some data: table1: 1 test1 2 test2 3 test3 table2: 1 2 1 3 3 1 table3: 1 2 2 3 3 2 What I want to do in a view is the following resultset: uid uname xuid yuid 1test1 2 2 1test1 3 2test2 3 3test3 1 3test3 2 So basically I want to know which uid is connected to which uid, one relationship per row. So xuid and yuid shall be identical if records exist in both table2 and table3 or the value shall be NULL if a corresponding record can't be found in either table2 or table3. Can anyone here help me out? Thanks a lot UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjVqVjqGXBvRToM4RAhk5AKCjjGc4VilA45PnPZoKluTNYUP6FACgrRQq XFIvkCIJHyz7TvvV/XxL4Lk= =/vxG -END PGP SIGNATURE- ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SQL question
"Uwe C. Schroeder" <[EMAIL PROTECTED]> writes: > Here's a question for the SQL guru's out there, which I've been trying to > solve for the last couple of hours. There's got to be a solution to this, but > somehow I can't find it. > > Tables: > > table1 ( > uid int PK, > uname varchar(64) > ) > > table2 ( > uid int FK to table1, > xuid int FK to table 1 > ) > > table3 ( > uid int FK to table1, > yuid int FK to table1 > ) SELECT * FROM table2 FULL OUTER JOIN table3 ON (table2.uid = tabletable3.uid AND xuid = yuid) JOIN table1 USING (uid) Or if you find it clearer SELECT * FROM table1 JOIN (table2 FULL OUTER JOIN table 3 ON (table2.uid = tabletable3.uid AND xuid = yuid)) USING (uid) You might want to have an outer join (RIGHT OUTER JOIN in the first form, LEFT OUTER JOIN in the second) if you want to list records from table1 for which there are no matching records in table2 or table3. This is going to be a hard query to get to be fast though. The other alternative would be to do a self-join of table1 to table1 and then use subqueries to check for matching table2 or table3 entries. This would be a lose if the relationships are relatively sparse, but if you have more tables it might end up being a win, I don't know. Something like SELECT table1.*, (select xuid from table2 where uid = child.uid) as xuid, (select yuid from table3 where uid = child.uid) as yuid, (select zuid from table4 where uid = child.uid) as zuid, ... FROM table1 CROSS JOIN table1 AS child -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks for your answers Greg & Vincent. Although I solved the problem by a change of schema - I'm happy that I have something to digest I didn't know before. One never learns enough ... U.C. On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote: > Here's a question for the SQL guru's out there, which I've been trying to > solve for the last couple of hours. There's got to be a solution to this, > but somehow I can't find it. > > Tables: > > table1 ( > uid int PK, > uname varchar(64) > ) > > table2 ( > uid int FK to table1, > xuid int FK to table 1 > ) > > table3 ( > uid int FK to table1, > yuid int FK to table1 > ) > > There might be more tables of the type like table2 and table3, but I'd > already be happy to solve the puzzle with the 3 tables above. > Ok, assume table1 is the master table - in my case a table used for login > authentication (some columns removed above) > table2 and table3 are tables where the uid always references to the uid in > table1. The second "uid" (xuid and yuid in this example) references to > another uid record in table1. The problem is that there may or may not be > entries in table2 (or table3) referencing a specific uid in their second > uid field. > Maybe some data: > > table1: > 1 test1 > 2 test2 > 3 test3 > > table2: > 1 2 > 1 3 > 3 1 > > table3: > 1 2 > 2 3 > 3 2 > > What I want to do in a view is the following resultset: > > uid uname xuid yuid > 1test1 2 2 > 1test1 3 > 2test2 3 > 3test3 1 > 3test3 2 > > > So basically I want to know which uid is connected to which uid, one > relationship per row. So xuid and yuid shall be identical if records exist > in both table2 and table3 or the value shall be NULL if a corresponding > record can't be found in either table2 or table3. > > Can anyone here help me out? > > Thanks a lot > > UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjz6bjqGXBvRToM4RApNRAJ9tJzn/3DHSYEZPlGSjzU0H/FsQIwCffw4N XJuHiF0al0pzInvOb3BP1Jg= =490X -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] SQL question
> I have a table that has 3 date columns : > > create table xyz ( > xyz_id integer, > date1 timestamp, > date2 timestamp, > date3 timestamp > ) > > > I want to select in a query the xyz_id and the max date column for > each row > something like : > create table temp2 as select xyz_id (max date?) where ... Is this what you want? Select xyz_id, greatest(date1,date2,date3) from xyz where... http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html#AE N14508 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQL Question - "Recursion"
Hi - Was wondering if anyone could help / had some thoughts. I am building a model for a client, and right now doing customer attrition modeling. Basically, the number of customers in this period is equal to: Beg # Customers + customers added this period - attrition Ending # Customers Obviously getting the beginning number of customers and # of added has been easy via SQL. Also, the above is fairly straightforward in a spreadsheet program like Excel. But I can't seem to come to grips with how to model the attrition line item in SQL, as it's based on last months ending balance ... which in turn is based on beg + add - attr = end for the previous period, and so on. So it seems to be a recursive function as far as I can tell ... any idea how to model this via SQL? I can get running totals for the # of adds with a correlated subquery, but I can't seem to figure out how to build the attrition into that running total. All help is greatly appreciated! Thanks, Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
RE: [GENERAL] SQL Question
Yes, the "having" clause is supported in 6.4.2 with some restrictions as outlined in the documentation on the web site. -Original Message- From: Matthew [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, March 09, 1999 10:46 PM To: [EMAIL PROTECTED] Subject: [GENERAL] SQL Question I am using RH5.2 / Postgre 6.3.2 I need a query that has a having clause. In 6.3.2 it says that having is not supported yet. I looked at the changes in 6.4 and it appears that 6.4.2 supports this. Is this true? Also I have found 6.4.2 rpms but no data rpm so now when I try to use psql it says pgReadData() -- backend closed the channel unexpectedly. Please help. Matt
Re: [GENERAL] sql question
[EMAIL PROTECTED] wrote: > > > SELECT * FROM adressGroup > > > WHERE Group_Id = 6 > > > AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias > > > WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id > > > GROUP BY AdrGroup_Id HAVING COUNT(*) = 1); > > One last try : your query looks pretty strange, maybe you should design it again > from scratch : > if I understand well, you want the rows in adressGroup with : > > - Group_Id=6 > - adrGroup_Id being unique > > So why not : > > SELECT * FROM adressGroup > WHERE Group_Id = 6 > GROUP BY AdrGroup_Id HAVING COUNT(*)=1 No, when you say group_id = 6 then you have already the adresses which belongs to this group and the result of the 'group by having' statement is always 1. That's not the solution. Consider the following situation. The user sent a letter to the persons which belongs to the adressgroups 1,2 and 3. Now he like to send the same letter to the persons which belongs to adressgroup 6. But It can be that persons in group 6 belongs either to group 1, 2 or 3. So the user wants only those adresses which belongs to adressgroup 6 and do not count to any other group. That's way I tried the statement with the Exists clause. Greetings Herbie -- ~~~ Herbert LiechtiE-Mail: [EMAIL PROTECTED] ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~
Re: [GENERAL] sql question
On Wed, Jul 12, 2000 at 05:33:09PM -0500, Travis Bauer wrote: > > Let's say I have a table t1 with two fields, x and y. How do I write an > sql statement like: > > select x if y>1 else 0 from t1; SELECT CASE WHEN y>1 THEN x ELSE 0 END FROM t1; >From page 33 of Bruce's book, at: http://www.postgresql.org/docs/aw_pgsql_book/node52.html Ross -- Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] sql question
Thanks, Now that you mention it, I even remember reading it in the book! Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer On Wed, 12 Jul 2000, Ross J. Reedstrom wrote: > > SELECT CASE WHEN y>1 THEN x ELSE 0 END FROM t1; > > >From page 33 of Bruce's book, at:
Re: [NOVICE] [GENERAL] sql question
OOPs. I mean SELECT t1.id , t1.fref FROM t1 UNION ALL SELECT t1.id , t1.mref FROM t1 - Original Message - From: Vincent Hikida To: Steven Verhoeven ; pgsql-general@postgresql.org ; [EMAIL PROTECTED] Sent: Sunday, March 13, 2005 6:34 PM Subject: Re: [NOVICE] [GENERAL] sql question SELECT t1.id , t1.fref FROM t1 UNION ALL SELECT t2.id , t2.mref FROM t2 - Original Message - From: Steven Verhoeven To: pgsql-general@postgresql.org ; [EMAIL PROTECTED] Sent: Friday, March 11, 2005 4:36 AM Subject: [GENERAL] sql question Hi all My table definition : id | fref | mref--+---+-- 1 | 23 | 25 2 | 24 | 28 3 | 25 | 31 4 | 26 | 34My problem : i need a query that results in this : id | ref--+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34Do I need a crosstab-query ?Who can help me ? -- A computer is like an airconditioner. When windows open, it stops working ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Steven Verhoeven, ICT Support Engineer Department for Molecular Biomedical Research (DMBR)VIB - Ghent University 'Fiers-Schell-Van Montagu' buildingTechnologiepark 927B - 9052 Ghent (Zwijnaarde)Belgium Tel : +32-(0)9-33-13.606 Fax : +32-(0)9-33-13.609 E-mail : [EMAIL PROTECTED] URL : http://www.dmbr.UGent.be
Re: [GENERAL] SQL Question - "Recursion"
ktr73 wrote: Hi - Was wondering if anyone could help / had some thoughts. I am building a model for a client, and right now doing customer attrition modeling. Basically, the number of customers in this period is equal to: Beg # Customers + customers added this period - attrition Ending # Customers Obviously getting the beginning number of customers and # of added has been easy via SQL. Also, the above is fairly straightforward in a spreadsheet program like Excel. But I can't seem to come to grips with how to model the attrition line item in SQL, as it's based on last months ending balance ... which in turn is based on beg + add - attr = end for the previous period, and so on. So it seems to be a recursive function as far as I can tell ... any idea how to model this via SQL? I can get running totals for the # of adds with a correlated subquery, but I can't seem to figure out how to build the attrition into that running total. Don't understand why you need recursion. Seems like attrition can be calculated. You say you can get the beginning number of customers. The ending number of customers for month N = beginning number of customers for month N+1. So, if you have the beginning, ending and # customers added, then attrition = ending - beginning - #added. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQL Question - Using Group By
Hi, I have a question about using Group By. On a table like this: Type (varchar) | Active (boolean) Type One | False Type Two | True Type One | True Type Fifty | Flase Type Two | True Having this table I want a report grouping Types and giving me more statistics such as: Type|Active Count| Inactive Count|Active Percent How do i do that? I can think of : select Type from table_name group by Type But that doesn't give me how many active and inactive each had! Please help me here understand how to approach this. Thank you, Mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [SQL] Question on indexes
Emil Rachovsky wrote: > > Hi, > Can anyone show me a simple way of creating an index > in PostGre like that: > create index indName on someTable(someIntColumn DESC) > ? Not using that particular syntax, but you can do that if you create the appropiate operator classes. Note that if you want to use btree index on a single column, you don't need to have a descending index, because btree indexes can be scanned in either direction. The opclass trick is only needed if you want to have a multicolumn index. (I guess in the other access methods it doesn't make much sense to think of descending indexes.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] SQL question re aggregates & joins
OK, this does not work: select max(t1."When"), t1."Pt_Id", t2."DateOfBirth" from "PtStaffAccess" t1, "Person" t2 where t1."Pt_Id" = t2.id group by t1."Pt_Id"; But this does: select max(t1."When"), t1."Pt_Id", min(t2."DateOfBirth") from "PtStaffAccess" t1, "Person" t2 where t1."Pt_Id" = t2.id group by t1."Pt_Id"; Now the error message was clear, and I think PG is following the standard here. But I have a question just for my own education. It seems to me, given that "Person".id is declared as the primary key, it should possible to deduce that there is no way that the 1st query could ever have multiple values of "DateOfBirth" to choose from when building a result row. Am I missing something? Or am I right, that this is something that SQL could do but simply doesn't, for whatever reason, historical, complexity... In fact, what's even more surprising to me, is that if I change the grouping to the other side of the join, it still doesn't work: select max(t1."When"), t2.id, t2."DateOfBirth" from "PtStaffAccess" t1, "Person" t2 where t1."Pt_Id" = t2.id group by t2.id; Come on, I'm grouping on the primary key and it thinks that there might be multiple values for the other columns? -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL Question - Using Group By
You could use COUNT() in conjunction with NULLIF: select "Type", count(nullif("Active", false)) as "Active Count", count(nullif("Active", true)) as "Inactive Count", 100 * count(nullif("Active", false)) / count(*) as "Active Percent" from table_name group by "Type" On Feb 23, 2:50 pm, "Mike" <[EMAIL PROTECTED]> wrote: > Hi, > > I have a question about using Group By. > > On a table like this: > > Type (varchar) | Active (boolean) > > Type One | False > Type Two | True > Type One | True > Type Fifty | Flase > Type Two | True > > Having this table I want a report grouping Types and giving me more > statistics such as: > > Type|Active Count| Inactive Count|Active > Percent > > How do i do that? > > I can think of : > > select Type from table_name group by Type > > But that doesn't give me how many active and inactive each had! > > Please help me here understand how to approach this. > > Thank you, > Mike ---(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: [GENERAL] SQL Question - Using Group By
Thank you! Exactly what I needed. Mike On Feb 23, 4:42 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > You could use COUNT() in conjunction with NULLIF: > > select "Type", > count(nullif("Active", false)) as "Active Count", > count(nullif("Active", true)) as "Inactive Count", > 100 * count(nullif("Active", false)) / count(*) as "Active Percent" > from table_name group by "Type" > > On Feb 23, 2:50 pm, "Mike" <[EMAIL PROTECTED]> wrote: > > > Hi, > > > I have a question about using Group By. > > > On a table like this: > > > Type (varchar) | Active (boolean) > > > > Type One | False > > Type Two | True > > Type One | True > > Type Fifty | Flase > > Type Two | True > > > Having this table I want a report grouping Types and giving me more > > statistics such as: > > > Type|Active Count| Inactive Count|Active > > Percent > > > How do i do that? > > > I can think of : > > > select Type from table_name group by Type > > > But that doesn't give me how many active and inactive each had! > > > Please help me here understand how to approach this. > > > Thank you, > > Mike ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL Question - Using Group By
Hi, I have a question about using Group By. On a table like this: Type (varchar) | Active (boolean) Type One | False Type Two | True Type One | True Type Fifty | Flase Type Two | True Having this table I want a report grouping Types and giving me more statistics such as: Type|Active Count| Inactive Count|Active Percent How do i do that? I can think of : select Type from table_name group by Type This should been quite easy - the trick is aggregate functions omit NULL values (maybe there is some other / better way): SELECT type, COUNT(CASE WHEN active THEN 1 ELSE NULL END) AS active_count, COUNT(CASE WHEN active THEN NULL ELSE 1 END) AS inactive_count, COUNT(CASE WHEN active THEN 1 ELSE NULL END) / COUNT(*) AS active_pct FROM table_name; but have not tested it ;( Tomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SQL Question - Using Group By
[EMAIL PROTECTED] wrote: > You could use COUNT() in conjunction with NULLIF: > > select "Type", > count(nullif("Active", false)) as "Active Count", > count(nullif("Active", true)) as "Inactive Count", > 100 * count(nullif("Active", false)) / count(*) as "Active Percent" > from table_name group by "Type" Tom Lane suggested me to use sum("Active"::int) in a similar situation; Except that I had boolean expressions instead of values. It is a bit faster; IMO readability is just "different". -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] SQL question: checking all required items
Hi all, Given the following tables - create table people ( person_id text primary key, person_name text, [...etc...] ); create table items ( item_id text primary key, item_name text, is_required boolean, [...etc...] ); create table items_for_people ( person_id text, item_id text, primary key (person_id, item_id), foreign key person_id references people(person_id), foreign key item_id references items(item_id) ); - how can I find those people who don't have _all_ of the items which are marked "required"? In other words, how do I select those rows in "people" which don't have a corresponding row in "items_for_people" for *each* row in "items" which has is_required=true? Many thanks, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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
Re: [GENERAL] SQL question re aggregates & joins
On 28 January 2010 21:32, Scott Ribe wrote: > OK, this does not work: > > select max(t1."When"), t1."Pt_Id", t2."DateOfBirth" > from "PtStaffAccess" t1, "Person" t2 > where t1."Pt_Id" = t2.id > group by t1."Pt_Id"; > > But this does: > > select max(t1."When"), t1."Pt_Id", min(t2."DateOfBirth") > from "PtStaffAccess" t1, "Person" t2 > where t1."Pt_Id" = t2.id > group by t1."Pt_Id"; > > Now the error message was clear, and I think PG is following the standard > here. But I have a question just for my own education. > > It seems to me, given that "Person".id is declared as the primary key, it > should possible to deduce that there is no way that the 1st query could > ever > have multiple values of "DateOfBirth" to choose from when building a result > row. Am I missing something? Or am I right, that this is something that SQL > could do but simply doesn't, for whatever reason, historical, complexity... > > In fact, what's even more surprising to me, is that if I change the > grouping > to the other side of the join, it still doesn't work: > > select max(t1."When"), t2.id, t2."DateOfBirth" > from "PtStaffAccess" t1, "Person" t2 > where t1."Pt_Id" = t2.id > group by t2.id; > > Come on, I'm grouping on the primary key and it thinks that there might be > multiple values for the other columns? > > You can't include an aggregate in the select if you don't group by non-aggregates, so it should be: select max(t1."When"), t1."Pt_Id", t2."DateOfBirth" from "PtStaffAccess" t1, "Person" t2 where t1."Pt_Id" = t2.id group by t1."Pt_Id", t2."DateOfBirth"; and likewise select max(t1."When"), t2.id, t2."DateOfBirth" from "PtStaffAccess" t1, "Person" t2 where t1."Pt_Id" = t2.id group by t2.id, t2."DateOfBirth"; PostgreSQL might already know that there is only 1 date of birth per ID, but it still doesn't make sense to select them both and group by one of them when an aggregate is present (even though MySQL will ignore that and just fill out what it thinks you missed internally) Regards Thom
Re: [GENERAL] SQL question re aggregates & joins
> You can't include an aggregate in the select if you don't group by > non-aggregates, so it should be: > > select max(t1."When"), t1."Pt_Id", t2."DateOfBirth" > from "PtStaffAccess" t1, "Person" t2 > where t1."Pt_Id" = t2.id > group by t1."Pt_Id", t2."DateOfBirth"; I was aware that I could alternatively group by all the columns, but that actually just highlights the redundancy even more--consider your second example: select max(t1."When"), t2.id , t2."DateOfBirth" from "PtStaffAccess" t1, "Person" t2 where t1."Pt_Id" = t2.id group by t2.id, t2."DateOfBirth"; Given that t2.id is the primary key, grouping by any other column of t2 is really redundant. I know *what* SQL won't allow me to do, I'm interested in knowing if there's some reason *why* other than historical... -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL question re aggregates & joins
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Scott Ribe > Sent: Thursday, January 28, 2010 2:10 PM > To: Thom Brown > Cc: pgsql-general > Subject: Re: [GENERAL] SQL question re aggregates & joins > > > You can't include an aggregate in the select if you don't group by > > non-aggregates, so it should be: > > > > select max(t1."When"), t1."Pt_Id", t2."DateOfBirth" > > from "PtStaffAccess" t1, "Person" t2 > > where t1."Pt_Id" = t2.id > > group by t1."Pt_Id", t2."DateOfBirth"; > > I was aware that I could alternatively group by all the columns, but > that > actually just highlights the redundancy even more--consider your second > example: > > select max(t1."When"), t2.id , t2."DateOfBirth" > from "PtStaffAccess" t1, "Person" t2 > where t1."Pt_Id" = t2.id > group by t2.id, t2."DateOfBirth"; > > Given that t2.id is the primary key, grouping by any other column of t2 > is > really redundant. I know *what* SQL won't allow me to do, I'm > interested in > knowing if there's some reason *why* other than historical... Having a group by on a primary key in a single table is of no value. After all, the group by accomplishes nothing at all. Having a group by on the primary key of one table in a join does not guarantee uniqueness of the join result. Consider the simplified schema Table Customers: Custname varchar(80) Custaddr varchar(80) Custid int primary key Data: {'Joe', 'Paris France', 1} {'Fred', 'Seattle USA', 2} Table Orders: OrderID int Custid int OrderItem int OrderQty int OrderPrice Numeric 12.2 Primary key: OrderID, CustID, OrderItem Foreign key: CustiID references Customers(CustID) Data: {1, 1, 17, 2, 12.92}, {1, 1, 12, 5, 14.75}, {2, 2, 9, 1, 27.45}, {2, 2, 2, 8, 11.23}, {2, 2, 1, 1, 123.45} SELECT c.Custid, c.CustName, c.Custaddr, o.OrderID, o.OrderItem, sum(o.OrderQty * o.OrderPrice) as dollars From Customers c, Orders o WHERE c.Custid = o.Custid GROUP BY c.Custid Now, c.Custid is the primary key for Customers and also a foreign key for Orders and yet the query is utter nonsense. Had all of the primary key columns for the second table been included, then the group by is superfluous. Having one or more of the primary key columns left out from the child table but added to the group by list changes the meaning and value of the result. I have always thought this way for aggregate queries: 1. Add an aggregate function to each column that you are aggregating in some way. 2. Add a 'group by' for every column that is not being aggregated. Anything else means that the query has not been thought through carefully. IMO-YMMV -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL question re aggregates & joins
Scott Ribe writes: > Given that t2.id is the primary key, grouping by any other column of t2 is > really redundant. I know *what* SQL won't allow me to do, I'm interested in > knowing if there's some reason *why* other than historical... SQL92 says so. More recent versions of the SQL spec describe allowing omission of grouping columns when one of them can be proven unique, but it's complicated and we haven't got round to doing it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL question - problem with INTERSECT
On Mon, Oct 30, 2000 at 07:59:06PM -0500, Keith L. Musser wrote: > "(SELECT messages.msgid FROM messages, subject_index WHERE > ((subject_index.word='Hello' or subject_index.word='There') and > (subject_index.msgid = messages.msgid)) > GROUP BY messages.msgid HAVING count(messages.msgid)=2) > INTERSECT > (SELECT messages.msgid FROM messages, author_index WHERE > ((author_index.word='Jim' or author_index.word='Jones') and > (author_index.msgid = messages.msgid)) > GROUP BY messages.msgid HAVING count(messages.msgid)=2);" Try removing first "GROUP BY messages.msgid HAVING count(messages.msgid)=2)" -- Igor Roboul, Unix System Administrator & Programmer @ sanatorium "Raduga", Sochi, Russia http://www.brainbench.com/transcript.jsp?pid=304744
Re: [GENERAL] SQL question - problem with INTERSECT
If I remove the "GROUP BY messages.msgid ...", then the result will be messages whose subject contains either 'Hello' or 'There' in the subject, but not necessarily both. I want messages which have both 'Hello' and 'There' in the subject, and both 'Jim' and 'Jones' in the author. (For example, if I needed all of 'Hello', 'There', and 'Now' in the subject, my first HAVING clause would use a count of 3, while the second HAVING clause would still use a count of 2, for both 'Jim' and 'Jones'.) So I cannot remove either having clause without changing the meaning. What I would really like to know is why INTERSECT does not allow this. If I understand that, maybe I can figure out how to get what I need. -Original Message- From: Igor Roboul <[EMAIL PROTECTED]> To: PGSQL-General <[EMAIL PROTECTED]> Date: Wednesday, November 01, 2000 12:03 AM Subject: Re: [GENERAL] SQL question - problem with INTERSECT >On Mon, Oct 30, 2000 at 07:59:06PM -0500, Keith L. Musser wrote: >> "(SELECT messages.msgid FROM messages, subject_index WHERE >> ((subject_index.word='Hello' or subject_index.word='There') and >> (subject_index.msgid = messages.msgid)) >> GROUP BY messages.msgid HAVING count(messages.msgid)=2) >> INTERSECT >> (SELECT messages.msgid FROM messages, author_index WHERE >> ((author_index.word='Jim' or author_index.word='Jones') and >> (author_index.msgid = messages.msgid)) >> GROUP BY messages.msgid HAVING count(messages.msgid)=2);" >Try removing first "GROUP BY messages.msgid HAVING count(messages.msgid)=2)" > >-- >Igor Roboul, Unix System Administrator & Programmer @ sanatorium "Raduga", >Sochi, Russia >http://www.brainbench.com/transcript.jsp?pid=304744 >
Re: [GENERAL] SQL question - problem with INTERSECT
On Thu, Nov 02, 2000 at 06:50:07AM -0500, Keith L. Musser wrote: > > upper(a.word) = 'JIM' or upper(a.word) = 'JONES' > > upper(s.word) = 'HELLO' or upper(s.word) = 'THERE' > Interesting possibility. Unfortunately, the outer select ends up being > a sequential scan over the entire messages table, where at each row the > message id is looked up sequentially in the nested query. did you try my quesry with "upper's" in it? if so then is your index on a.word or on upper(a.word)? try making functional indies, and of course vacuum'im tables to make indices work. this query *should* work with indices. depesz -- hubert depesz lubaczewski najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
Re: [GENERAL] SQL question: checking all required items
On 8/10/07, Raymond O'Donnell <[EMAIL PROTECTED]> wrote: > - how can I find those people who don't have _all_ of the items which > are marked "required"? > > In other words, how do I select those rows in "people" which don't have > a corresponding row in "items_for_people" for *each* row in "items" > which has is_required=true? Without writing the exact query you need, I'll give you a couple of ways to solve the problem of finding things in one set that aren't in another. select table1.id from table1 left join table2 on (table1.id=table2.id) where table2.id is null OR select table1.id from table1 where table1.id is not in (select id from table2); ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL question: checking all required items
On 10/08/2007 21:29, Scott Marlowe wrote: select table1.id from table1 where table1.id is not in (select id from table2); Duh! I should have thought of that thanks for that, and apologies for the stupidity (blame it on the glass of wine I had with dinner!). Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL question: checking all required items
On 8/10/07, Raymond O'Donnell <[EMAIL PROTECTED]> wrote: > On 10/08/2007 21:29, Scott Marlowe wrote: > > > > select table1.id from table1 where table1.id is not in (select id from > > table2); > > Duh! I should have thought of that thanks for that, and apologies > for the stupidity (blame it on the glass of wine I had with dinner!). It's only obvious after you've done it a few times... Show us the query when you're done, I'm sure there are enough folks who'd like to see your solution. ---(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: [GENERAL] SQL question: checking all required items
On 10/08/2007 22:03, Carlos Ortíz wrote: Select * from people where person_id in ( Select person_ID from Items_for_people group by Person_id Having Count(*) = ( Select count(*) from Items Where is_required = true)) That seems to work fine! I'd only change "having count(*) = ..." to "having count(*) >= ..." to allow for people having other items in addition to the required ones. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL question: checking all required items
On 10/08/2007 21:42, Scott Marlowe wrote: Show us the query when you're done, I'm sure there are enough folks who'd like to see your solution. Here's what I came up with: select distinct ip.person_id from items_for_people ip where exists ( ( select item_id from items where is_required = true ) except ( select ip2.item_id from items_for_people ip2 inner join items i on (ip2.item_id = i.item_id) where ip2.person_id = ip.person_id and i.is_required = true ) ) This finds all those who don't have all the required items, whatever else they may have. Comments and improvements are welcome! Thanks for the help, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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: [GENERAL] SQL question: checking all required items
? Try Select * from people where person_id in ( Select person_ID from Items_for_people group by Person_id Having Count(*) = ( Select count(*) from Items Where is_required = true)) Or something like that. That's the idea. Probe it and tell us. (May be the sintaxis it's not correct, but I'm new in postgresql. In sql server it's ok) De: [EMAIL PROTECTED] en nombre de Raymond O'Donnell Enviado el: Vie 10/08/2007 03:07 p.m. Para: 'PostgreSQL' Asunto: [GENERAL] SQL question: checking all required items Hi all, Given the following tables - create table people ( person_id text primary key, person_name text, [...etc...] ); create table items ( item_id text primary key, item_name text, is_required boolean, [...etc...] ); create table items_for_people ( person_id text, item_id text, primary key (person_id, item_id), foreign key person_id references people(person_id), foreign key item_id references items(item_id) ); - how can I find those people who don't have _all_ of the items which are marked "required"? In other words, how do I select those rows in "people" which don't have a corresponding row in "items_for_people" for *each* row in "items" which has is_required=true? Many thanks, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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
[GENERAL] SQL Question - Group By and % results per row
I have a SQL statement that uses group-by to generate reports as such: GroupFieldClicks --- Page 1 50 Page 2 20 Page 3 30 Now, as an added field, I also want it to tell me what percent of total clicks each row represents. GroupFieldClicks Percent --- Page 1 50 50% Page 2 20 20% Page 3 30 30% How do I get access to the total of all clicks on per row basis so I can divide it? The only solution that comes to my mind is create a subquery that does a (select count(*) from... where... ) of the original grouped by sql statement. Thank you for your help in advance, Regards, Mike ---(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
Re: [GENERAL] SQL Question - Group By and % results per row
On Mon, Mar 12, 2007 at 12:53:11 -0700, Mike <[EMAIL PROTECTED]> wrote: > > How do I get access to the total of all clicks on per row basis so I > can divide it? The only solution that comes to my mind is create a > subquery that does a (select count(*) from... where... ) of the > original grouped by sql statement. That's how you do it. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] sql question; checks if data already exists before inserted
hi, there. i'm trying to write a SQL statement which does the following things. 1. checks if data already exists in the database 2. if not, insert data into database otherwise skip. for example, i'd like to insert a student called 'Michael Jordan' whose ID is 'JORDANMICHAEL' only if the id, 'JORDANMICHAEL' does not already exist in the database. anyway, my query looks like... insert into student (studentid, fname, lname) select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where studentid not in (select studentid from student); however, this does not seem to work. it does not insert data even if it does not exist in the database. hmm! any clue? thanks. ---(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
Re: [GENERAL] sql question; checks if data already exists before
nuno wrote: >hi, there. i'm trying to write a SQL statement which does the following >things. > >1. checks if data already exists in the database >2. if not, insert data into database otherwise skip. > > > Check this thread : http://archives.postgresql.org/pgsql-general/2005-10/msg01787.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] SQL-question: returning the id of an insert querry
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Im building an user database with many tables keeping the data for the Address, Phone numbers, etc which are referenced by a table where I keep the single users. My question is, how do I get the "Id"-value of a newly inserted address to store it in the referencing user table: (a) INSERT INTO address VALUES (); (b) INSERT INTO users VALUES ( name, ... , address , ... ); where address should hold the value of the Id from the Adress table. Do have to do an SELECT id FROM address WHERE oid = oid_returned_by_insert(a) or something like that after doing the insert(a) to get the correct id value, or is there a better way to do this. Im writing my app in Perl with DBD/DBI Thanks in advance, Andreas Fromm -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/rhcdPkvkZVZzNY0RApmDAJ4k4MY/zKvH2862MuHSIjDtsmIs3QCfRzaR 0zDc1bIQAOMpLurvRZ2V8JY= =kgaA -END PGP SIGNATURE- ---(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
Re: [GENERAL] sql question; checks if data already exists before inserted
On Wed, Jun 21, 2006 at 10:59:42PM -0700, nuno wrote: > insert into student (studentid, fname, lname) > select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where > studentid not in > (select studentid from student); > > however, this does not seem to work. it does not insert data even if it > does not exist in the database. hmm! There are no NULL studentid's, right? Because that would make NOT IN do something other than you think. Perhaps NOT EXISTS would work better. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] sql question; checks if data already exists before inserted
nuno wrote: > hi, there. i'm trying to write a SQL statement which does the > following things. > > 1. checks if data already exists in the database > 2. if not, insert data into database otherwise skip. > > for example, i'd like to insert a student called 'Michael Jordan' > whose ID is 'JORDANMICHAEL' only if the id, 'JORDANMICHAEL' does not > already exist in the database. anyway, my query looks like... > > insert into student (studentid, fname, lname) > select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where > studentid not in (select studentid from student); > > however, this does not seem to work. it does not insert data even if > it does not exist in the database. hmm! > > any clue? Your query is not doing what you think it's doing. Try running just the select portion of the query you provide above (minus the "insert into" part.) You are selecting from student where studentid not in (select studentid from student). That will always return the empty set, since you are looking at the same column and the same table in both the inner and outer selects. What you want to do is create a primary key on your student table. Then try to do the insert, catching the "not unique" error return. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL-question: returning the id of an insert querry
After you've done the insert on the address table, you can use currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have to have used nextval() for the original insert. Hope this helps, On Sun, Nov 09, 2003 at 11:29:49AM +0100, Andreas Fromm wrote: > Hi, > > Im building an user database with many tables keeping the data for the > Address, Phone numbers, etc which are referenced by a table where I keep > the single users. My question is, how do I get the "Id"-value of a newly > inserted address to store it in the referencing user table: > > (a) INSERT INTO address VALUES (); > > (b) INSERT INTO users VALUES ( name, ... , address , ... ); > > where address should hold the value of the Id from the Adress table. > > > Do have to do an > SELECT id FROM address WHERE oid = oid_returned_by_insert(a) > or something like that after doing the insert(a) to get the correct id > value, or is there a better way to do this. > > Im writing my app in Perl with DBD/DBI > > > Thanks in advance, > > Andreas Fromm > > > > ---(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 -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato pgp0.pgp Description: PGP signature
Re: [GENERAL] SQL-question: returning the id of an insert querry
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Martijn van Oosterhout wrote: > After you've done the insert on the address table, you can use > currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have > to have used nextval() for the original insert. > > Hope this helps, > ..going to try it. Thanks Andreas Fromm -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/rkMwPkvkZVZzNY0RAnajAJ0ePCTi/UODhGAxOs5NuptZAT0tUgCgpNAz Oqh8rM934O3SRRzv4Mh9S4I= =E71z -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote: > After you've done the insert on the address table, you can use > currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you > have to have used nextval() for the original insert. What if someone else inserts another address before I get the currval? I'm out of luck then, right? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SQL-question: returning the id of an insert querry
Scott Chapman <[EMAIL PROTECTED]> writes: > On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote: > > After you've done the insert on the address table, you can use > > currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you > > have to have used nextval() for the original insert. > > What if someone else inserts another address before I get the currval? > I'm out of luck then, right? No, currval() handles that--see the docs. -Doug ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote: > On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote: > > After you've done the insert on the address table, you can use > > currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you > > have to have used nextval() for the original insert. > > What if someone else inserts another address before I get the currval? > I'm out of luck then, right? No, currval is concurrency-safe. That's exactly what sequences are for. -- Alvaro Herrera () "I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Mon, Nov 10, 2003 at 08:09:29AM -0800, Scott Chapman wrote: > Chronological events here: > > X inserts a new record into A. > Y inserts a new record into A. > X fetches currval of the SA. What value does X get in this case, the one > from X's insert or Y's? X's. -- Alvaro Herrera () "¿Qué importan los años? Lo que realmente importa es comprobar que a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Monday 10 November 2003 08:23, David Green wrote: > Are X & Y two different connections? > If you execute 2 statements on the same connection and then get > currval() it will give the last generated id. > > Ex. > On 1 connection: > INSERT INTO A (fld) VALUES (val); -- id generated = 1 > INSERT INTO A (fld) VALUES (val2); -- id generated = 2 > SELECT currval('SA'); > 2 Thanks for the clarification. With web applications and connection pooling, it would appear that it's quite easy to get incorrect values back. This is what I thought. I talked with the author or SQLObject about this recently and I thnk he's implementing this correctly, by querying the cursor for the last OID?: def _queryInsertID(self, conn, table, idName, names, values): c = conn.cursor() q = self._insertSQL(table, names, values) if self.debug: print 'QueryIns: %s' % q c.execute(q) c.execute('SELECT %s FROM %s WHERE oid = %s' % (idName, table, c.lastoid())) return c.fetchone()[0] The other way to do it would be to manually fetch nextval and insert into the table over-riding the default for the ID field (assuming it defaulted to the nextval in the sequence). I don't know which way is best (for performance, for instance). It's be nice if INSERT could be made to return the OID or (better yet) the primary key field value when it completes. That would solve this problem in one action and completely remove the need for the second query. I expect it would have to be user-togglable so it didn't break with existing code? Scott ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SQL-question: returning the id of an insert querry
I saw this method of Statement class in jdbc. Will the return int contain the autogenerated key value ?? public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException thanks, kathy Scott Chapman wrote: > On Monday 10 November 2003 08:23, David Green wrote: > > Are X & Y two different connections? > > If you execute 2 statements on the same connection and then get > > currval() it will give the last generated id. > > > > Ex. > > On 1 connection: > > INSERT INTO A (fld) VALUES (val); -- id generated = 1 > > INSERT INTO A (fld) VALUES (val2); -- id generated = 2 > > SELECT currval('SA'); > > 2 > > Thanks for the clarification. With web applications and connection > pooling, it would appear that it's quite easy to get incorrect values > back. This is what I thought. > > I talked with the author or SQLObject about this recently and I thnk > he's implementing this correctly, by querying the cursor for the last > OID?: > > def _queryInsertID(self, conn, table, idName, names, values): > c = conn.cursor() > q = self._insertSQL(table, names, values) > if self.debug: > print 'QueryIns: %s' % q > c.execute(q) > c.execute('SELECT %s FROM %s WHERE oid = %s' > % (idName, table, c.lastoid())) > return c.fetchone()[0] > > The other way to do it would be to manually fetch nextval and insert > into the table over-riding the default for the ID field (assuming it > defaulted to the nextval in the sequence). I don't know which way is > best (for performance, for instance). > > It's be nice if INSERT could be made to return the OID or (better yet) > the primary key field value when it completes. That would solve this > problem in one action and completely remove the need for the second > query. I expect it would have to be user-togglable so it didn't break > with existing code? > > Scott > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Mon, Nov 10, 2003 at 08:56:03AM -0800, Scott Chapman wrote: > I talked with the author or SQLObject about this recently and I thnk > he's implementing this correctly, by querying the cursor for the last > OID?: That won't scale unless you index oid. And your tables will all need oids, which is not standard any more. If you do your work in one transaction and get the currval that way, it is impossible to go wrong. Also, if you don't return the connection to the pool before getting the currval, you will not go wrong. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SQL-question: returning the id of an insert querry
"scott.marlowe" <[EMAIL PROTECTED]> writes: > select tablename.fieldname.currval; That syntax would be problematic, it would mean to select all rows from tablename and evaluate fieldname.currval for each one. Actually it's worse, it would be confused with schemas I think. The postgres-ish way to do this would be to create a function like currval that took a table and column and told you the currval of the sequence associated with it. Well you can already do something like that: db=> create or replace function currval(text,text) returns bigint as 'select currval($1 || ''_'' || $2 || ''_seq'')' language sql strict; CREATE FUNCTION db=> create table test (a serial); NOTICE: CREATE TABLE will create implicit sequence "test_a_seq" for "serial" column "test.a" CREATE TABLE db=> insert into test(a) values (default); INSERT 14080230 1 db=> select currval('test','a'); currval - 1 (1 row) The only problem arises if you use table names or column names that cause postgres to truncate the resulting sequence name. This could be worked-around by using the dependency information instead of depending on the naming scheme. But as long as you do that the above works fine. And means you could always change your naming scheme or method for looking up the associated sequence later without changing all your sql. -- greg ---(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: [GENERAL] SQL-question: returning the id of an insert querry
Scott Chapman <[EMAIL PROTECTED]> writes: > On Wednesday 12 November 2003 11:29, Doug McNaught wrote: > > Scott Chapman <[EMAIL PROTECTED]> writes: > > > It would be nice if PostgreSQL could return the primary key it > > > inserted with but that may not be a fool-proof solution either. Is > > > there a nice way to handle this situation? > > > > Write a database function that inserts the record and returns the > > primary key value? That's probably the best way to insulate your app > > from the database structure... > > The function still has to know which sequence to pull from doesn't it? Yes. It's theoretically possible to derive that information if you have enough system-tables-fu, but since the function knows which table it's inserting into, it's not hard to put the proper sequence name in as well. > I don't know much about triggers/functions in PG. Is it possible to > have a function that intercepts the information AFTER the sequence > value is added as the new primary key and then return it? This would > enable the use of a more generic function. Sure, in the function you would basically do (I forget the exact pl/pgsql syntax): INSERT INTO foo VALUES (...); SELECT currval('the_pk_sequence') INTO pk; RETURN pk; Doesn't remove the need to know or derive the proper sequence name. There is no "what primary key did I just insert" built into PG. And you will need a separate function for each table. But this way the DB knowledge resides in the DB and you just have a nice clean API for inserting data from the clients. The schema can change and the API will (homefully) remain the same... -Doug ---(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
Re: [GENERAL] SQL-question: returning the id of an insert querry
Scott Chapman <[EMAIL PROTECTED]> writes: > It would be nice if PostgreSQL could return the primary key it inserted > with but that may not be a fool-proof solution either. Is there a nice > way to handle this situation? Write a database function that inserts the record and returns the primary key value? That's probably the best way to insulate your app from the database structure... -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])