Re: [SQL] Porting from db2 problem
> > I am trying to port an app that currently runs over db2 and oracle (on windows > os/2 linux hpux aix etc) to also run over postgres. > I am currently porting the windows client (using odbc). > I am having major problems because of the lack of with hold cursors (cursors > that remain open over a commit) in postgres. There are a lot of places where > the app will - open a cursor with hold - fetch the first 20 or so rows, allow > the user to scroll thru the result set, updating and commiting changes to > particular rows as they go. When the user gets to the end of the current set of > fetched rows - the app will fetch the next 20 or so during a scroll down > operation etc. The user may hold the read-only cursor over the result-set all > day, and it is important that rows that they do update, be immediately > available for other users. > > What do folk do to support this kind of scenario in the abscence of with hold > cursors ? Have a look at SELECT ... FOR UPDATE ... LIMIT ... OFFSET ... ; > Is implementation of with hold cursors likely in the near future? > I have no idea. Sorry. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Help with query involving aggregation and joining.
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions may be gotten over by wrapping the first query result in a subselect. not sure though if its proper. regds mallah. test=# SELECT * from ( SELECT distinct on (a.id) b.id ,courseid,name,submission from course a join history b on (a.id=b.courseid) ) as results order by results.submission desc; ++--+---++ | id | courseid | name| submission | ++--+---++ | 3 | 104 | Maths | 2002-04-30 | | 2 | 102 | Chemistry | 2002-02-17 | | 1 | 101 | Physics | 2002-01-20 | ++--+---++ (3 rows) On Monday 24 February 2003 10:48 am, Eddie Cheung wrote: > Hi, > > I was very glad to see the replies from you guys this > morning. The two suggested SQL queries did not return > the expected results, but will help me to explain the > problem I am facing further. > > 1) Josh suggested the following query. (I have made > minor changes by adding the table name to each field) > >SELECT history.id, history.courseId, course.name, > MAX(history.submission) AS submission >FROM history JOIN course ON history.courseId = > course.Id >GROUP BY history.id, history.courseId, course.name >ORDER BY course.name; > > The results returned are: > id | courseid | name| submission > +--+---+ > 2 | 102 | Chemistry | 2002-02-17 > 4 | 102 | Chemistry | 2002-02-22 > 3 | 104 | Maths | 2002-04-30 > 5 | 104 | Maths | 2002-03-15 > 6 | 104 | Maths | 2002-01-21 > 1 | 101 | Physics | 2002-01-20 > > There are duplicate courses because the history.id > column has different values. The history.id cannot be > use in the GROUP BY clause. But it is one of the > displaying field, so I could not remove it from the > GROUP BY clause either. > > 2) Bruno suggested the following query: > > select distinct on (course.courseid) > history.id, course.courseid, course.name, > history.submission > from course natural join history > order by course.courseid, history.submission desc; > > I have not used NATURAL JOIN before, but from what I > know, it joins the columns with the same name. Since > the joining columns of History and Course have > different names, I have replace JOIN clause. Please > let me know if I have made a mistake. > > The modified query is: >SELECT DISTINCT ON (course.id) course.id, > history.id, course.name, history.submission >FROM history JOIN course ON history.courseId = > course.id >ORDER BY course.id, history.submission desc; > > The results returned are : > id | id | name| submission > -++---+ > 101 | 1 | Physics | 2002-01-20 > 102 | 4 | Chemistry | 2002-02-22 > 104 | 3 | Maths | 2002-04-30 > > The problem here is that the results are not ordered > by the submission date. If I sort by > "history.submission" first, I get >ERROR: SELECT DISTINCT ON expressions must match > initial ORDER BY expressions. > Please note that I cannot select distinct on the > course.name either because it is NOT unique. The > original tables are much larger, and the only unique > column is the id. > > I have included the queries to create the tables here. > > > > CREATE TABLE course ( > id integer, > name varchar(32), >Constraint course_pkey Primary Key (id) > ); > > CREATE TABLE history ( > id integer NOT NULL, > courseid integer REFERENCES course(id), > submission date, > Constraint history_pkey Primary Key (id) > ); > > INSERT INTO course (id,name) VALUES (101,'Physics'); > INSERT INTO course (id,name) VALUES (102,'Chemistry'); > INSERT INTO course (id,name) VALUES (103,'Biology'); > INSERT INTO course (id,name) VALUES (104,'Maths'); > INSERT INTO course (id,name) VALUES (105,'English'); > > INSERT INTO history (id,courseid,submission) VALUES > (1,101,'2002-01-20'); > INSERT INTO history (id,courseid,submission) VALUES > (2,102,'2002-02-17'); > INSERT INTO history (id,courseid,submission) VALUES > (3,104,'2002-04-30'); > INSERT INTO history (id,courseid,submission) VALUES > (4,102,'2002-02-22'); > INSERT INTO history (id,courseid,submission) VALUES > (5,104,'2002-03-15'); > INSERT INTO history (id,courseid,submission) VALUES > (6,104,'2002-01-21'); > > > > > Thanks for all your help. > > > Regards, > Eddie > > --- Bruno Wolff III <[EMAIL PROTECTED]> wrote: > > On Sun, Feb 23, 2003 at 11:02:27 -0800, > > > > Eddie Cheung <[EMAIL PROTECTED]> wrote: > > > HISTORY > > > === > > > id | courseId | submission > > > ---+---+- > > > 1 | 101 | 2002-01-20 > > > 2 | 102 | 2002-02-17 > > > 3 | 104 | 2002-04-30 > > > 4 | 102 | 2002-02-22 > > > 5 | 104 | 2002-03-15 > > > 6 | 104 | 2002-01-21 > > > > > > > > > COURSE > > > == > > >
Re: [SQL] syntax question
Jeff, > I think Josh meant to say you can't select the results of a *dynamically > constructed* query without a loop --- that is, you need FOR ... EXECUTE. > A plain EXECUTE doesn't support plpgsql's notion of SELECT INTO. That's correct. See the rest of the thread. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] indexing
Hi all, I've been doing a little reading on indexing in prelude to indexing my db. I have the following to ask: if I had three tables for a many to many relationship say A, B, AND C B being the lookup. B being a huge 50k rows plus column and made just two forigen keys(b.a_id,b.c_id). is it best to create two non-unique indexes or one unique index on both fields? Please advise Ps I also attempted creating an index on a table i have called person on person_id and ran Explain select person_id from person where person_id < n but saw no results of my created index being used - am i doing something incorrectly Pps When indexing if searching tables is more important than concurrency - which type of index is best?
Re: [SQL] indexing
James, > if I had three tables for a many to many relationship say A, B, AND C > B being the lookup. B being a huge 50k rows plus column and made just two forigen keys(b.a_id,b.c_id). > is it best to create two non-unique indexes or one unique index on both fields? That depends on whether all three tables are usually queried toghether. If yes, then a 2-column index is probably better. If not, use single-column indexes. If you want more specific advice, post your table structures. > I also attempted creating an index on a table i have called person on person_id and > ran > Explain > select person_id from person where person_id < n > > but saw no results of my created index being used - am i doing something incorrectly Not a surprise. If your the planner expects person_id < n to return a significant portion of the table, a table scan is faster than an index scan. > Pps > When indexing if searching tables is more important than concurrency - which type of index is best? You want to use a B-tree index for anything other than statistical and/or geometic data. You are unlikely to need any other kind of index. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Help with query involving aggregation and joining.
> > ERROR: > SELECT DISTINCT ON expressions must match > initial ORDER BY expressions may be gotten over by > wrapping the first query result in a subselect. > > not sure though if its proper. > regds > mallah. > > test=# SELECT * from ( SELECT distinct on (a.id) b.id > ,courseid,name,submission from course a join history b on > (a.id=b.courseid) ) as results order by results.submission desc; > I'm currently working with select version(); version --- PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2 (1 row) I've used your table definitions and sample data you've sent earlier and do not receive an error message on the above SELECT statement. Did you? Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
