Re: [SQL] UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
> > Typing what you told me there was an errror; I' ve changed it slightly an it > seems that this sintax is correct. I say "it seems" because the computer > begins to process the query but doesn't finish it. I've leaved it working for > more than half an hour, before cancel it, with no result. > > Thank you anyway. This is what I think is a good sintax for UPDATE - SELECT - > SUBSELECT. Perhaps in mor simple cases it works. May someone is interested in > it. > > -- > Javier > > > UPDATE series_lluvia SET st7237=( > SELECT rain FROM ( > SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as > rain > FROM pluviometria WHERE ten=1 > UNION ALL > ... > SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as > rain > FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL > ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7237) AS > temp2 WHERE series_lluvia.year=temp2.year AND > series_lluvia.month=temp2.month AND series_lluvia.day=temp2.day); > -- > Javier, I've seen several queries which seemed to run for ages before. In many cases it helped to generate temporary tables and / or split up into "smaller" commands. Have you tried it by removing the union clauses as UPDATE series_lluvia SET st7237=( SELECT rain FROM ( SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as rain FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7237) AS temp2 WHERE series_lluvia.year=temp2.year AND series_lluvia.month=temp2.month AND series_lluvia.day=temp2.day); If this runs in an acceptable time, split up into several UPDATEs. If not, think of using temporary tables for SELECT - SUBSELECT. It's probably useful to do this in a transaction block started by BEGIN; so you can ROLLBACK; if the result is wrong. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Help on (sub)-select
Hi Folks, I have two tables roster holds the duties to be performed and the number of people required per duty. roster_staff holds the people allocated to perform that duty. I'm trying to create a select that will tally the roster_staff and include it with the roster details. I've managed to get it working for a specific day, but I can't seem to manage to get it working generically. here's the select I've got that works, along with the output: nymr=# select r.*, s.tally from roster r, nymr-# (select count(*) as tally from roster_staff where nymr(# rsdate = '2002-01-01' and rsgid = 11 and rsgsid = 2) as s nymr-# where rodate = '2002-01-01' and rogid = 11 and rogsid = 2; rodate | rogid | rogsid | rorequired | rooptional | tally +---++++--- 2002-01-01 |11 | 2 | 0 | 1 | 2 (1 row) nymr=# What I want to be able to do is select multiple rows and have the correct tally appear for that row. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Help on (sub)-select
At 10:21 AM 20/12/2002 +, Gary Stainburn wrote: nymr=# select r.*, s.tally from roster r, nymr-# (select count(*) as tally from roster_staff where nymr(# rsdate = '2002-01-01' and rsgid = 11 and rsgsid = 2) as s nymr-# where rodate = '2002-01-01' and rogid = 11 and rogsid = 2; rodate | rogid | rogsid | rorequired | rooptional | tally +---++++--- 2002-01-01 |11 | 2 | 0 | 1 | 2 (1 row) Try something like: select r.*, count(*) from roster r, roster_staff s where rsdate = rodate and rsgid = rogid and rsgsid = rogid group by r.* or select r.*, (select count(*) from roster_staff s where rsdate = rodate and rsgid = rogid and rsgsid = rogid ) roster r May not be exactly right, but you should get the idea Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Help on (sub)-select
On Friday 20 Dec 2002 10:51 am, Philip Warner wrote: > At 10:21 AM 20/12/2002 +, Gary Stainburn wrote: > >nymr=# select r.*, s.tally from roster r, > >nymr-# (select count(*) as tally from roster_staff where > >nymr(# rsdate = '2002-01-01' and rsgid = 11 and rsgsid = 2) as s > >nymr-# where rodate = '2002-01-01' and rogid = 11 and rogsid = 2; > >rodate | rogid | rogsid | rorequired | rooptional | tally > >+---++++--- > > 2002-01-01 |11 | 2 | 0 | 1 | 2 > >(1 row) > > Try something like: > > select r.*, count(*) from roster r, roster_staff s > where rsdate = rodate and rsgid = rogid and rsgsid = rogid > group by r.* This one came up with a parser error near '*' but I don't understand it enough to debug it. > > or > > select r.*, (select count(*) from roster_staff s > where rsdate = rodate and rsgid = rogid and rsgsid = rogid > ) roster r; This one ran, but the count column had the same value in every row - the total count for the table. I've managed it using an intermediate view. I've also extended it to show everything I need - see below. I'd still like to hear from anyone who could tell me how I can do this without the intermediate view tho' create table roster ( -- roster definition table - holding jobs to be done rodate date not null, rogid int4 references diagrams(gid), -- diagram rogsid int4 references jobtypes(jid), -- jobtype rorequired int4, -- essential staff rooptional int4, -- optional staff primary key (rodate, rogid, rogsid) ); create table roster_staff ( -- people on the roster rsdate date not null, rsgid int4 references diagrams(gid), -- diagram rsgsid int4 references jobtypes(jid), -- jobtype rssid int4 references staff(sid), -- staff id. constraint r2 foreign key (rsdate,rsgid,rsgsid) references roster (rodate,rogid,rogsid) ); create view roster_tally as select rsdate, rsgid, rsgsid, count(*) as rocount from roster_staff group by rsdate, rsgid, rsgsid; create view roster_details as select r.*, coalesce(t.rocount,0) as rocount, coalesce(a.rocount,0) as roavail from roster r left outer join roster_tally t on r.rodate = t.rsdate and r.rogid = t.rsgid and r.rogsid = t.rsgsid left outer join roster_tally a on r.rodate = a.rsdate and a.rsgid is null and r.rogsid = a.rsgsid; nymr=# select * from roster_details where rocount < rorequired and roavail > 0; rodate | rogid | rogsid | rorequired | rooptional | rocount | roavail +---++++-+- 2002-01-01 |12 | 4 | 1 | 0 | 0 | 1 (1 row) nymr=# > > May not be exactly right, but you should get the idea > > > > Philip Warner| __---_ > Albatross Consulting Pty. Ltd. |/ - \ > (A.B.N. 75 008 659 498) | /(@) __---_ > Tel: (+61) 0500 83 82 81 | _ \ > Fax: (+61) 03 5330 3172 | ___ | > Http://www.rhyme.com.au |/ \| > > |---- > > PGP key available upon request, | / > and from pgp5.ai.mit.edu:11371 |/ -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help on (sub)-select
At 11:48 AM 20/12/2002 +, Gary Stainburn wrote: > select r.*, count(*) from roster r, roster_staff s > where rsdate = rodate and rsgid = rogid and rsgsid = rogid > group by r.* This one came up with a parser error near '*' but I don't understand it enough to debug it. Replace the two occurrences of r.* with the list of fields in r that you want to group the count by. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] SQL select count(*) from "myTable" failed : Relation "mytable" does not exist
What do these errors mean? I get them on certain databases when trying to view table data in webmin's postgresql module. I don't think its Webmin specific. The databases in question are often exported from Access XP using pgAdmin 1.4.2 and the migration plugin (and imported using psql -f), but I also saw it happen sometimes on a fresh phpgroupware database. Here's a specific example: SQL select count(*) from "xCompany" failed : Relation "xcompany" does not exist Does the difference in case have anything to do with it? Is there a general script or procedure I can run to check table lint like this? Thanks. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Need help paging through record sets
Hello everyone, and thanks for reading my first newbie post. :-) I am a neopyhte PHP and postgreSQL user, with a website at www.the-athenaeum.org. We store (among other things) artworks, which people can view in a list, sorted by artist name, date, medium, etc. We now have enough works that I need to rewrite the PHP listings script (and its embedded SQL) so that users can page through records. As an example, if a user is looking at works by date ascending, they may want to see 100 records at a time. Since we have 600+ records, there would be 7 pages. They'd start on the first page (of course!) and there would be links to pages 2 through 7 as well, just like with results pages of a Google search. They could, from page 1, click any of the other pages to go immdiately to that set of 100 records for display. I see this kind of thing all over the place, and in looking it up, I see most solutions use "SELECT TOP x", which postgreSQL doesn't seem to have. I know how to use LIMIT, but that always starts from the top. I could add a piece to the WHERE clause, say something like "WHERE date > 01-02-1853", but how do I know where the cutoffs are several pages along, without retrieving the whole record set? I suppose the optimal solution for me would be to sort all of the records, then be able to select a range from that sorted record set. So, if they click the link to page 3, I'd like to do this (in pseudocode): 1. SORT records by the date field, descending 2. Retrieve only records 200-299 from the sorted list Is there a way to do that? How is it done elsewhere? Thanks in advance for your help, Chris McCormick, webmaster The Athenaeum - Interactive Humanities Online www.the-athenaeum.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Need help paging through record sets
Sort of depends on the nature of your application. You can use offset to get specific chunks: select * from foo order by date limit 100 offset 100; You should be aware, however, that on a very large table this can be quite inefficient as you will have to do the select and sort on the large table just to get the next chunk. (With only 600 tuples you will probably have everything in memory anyway so it's not really a problem.) Also, if the data is actively updated you could get a tuple added or deleted between page views which would mess up the offsets and cause someone to miss an item or get a duplicate. If you want to page through a small subset of a large file you can use cursors or temporary tables but you will have to be sure your connection persistence, session management and such can accomodate such an arrangement. Cheers, Steve On Friday 20 December 2002 12:53 pm, [EMAIL PROTECTED] wrote: > Hello everyone, and thanks for reading my first newbie post. :-) > > I am a neopyhte PHP and postgreSQL user, with a website at > www.the-athenaeum.org. We store (among other things) artworks, which > people can view in a list, sorted by artist name, date, medium, etc. > > We now have enough works that I need to rewrite the PHP listings script > (and its embedded SQL) so that users can page through records. As an > example, if a user is looking at works by date ascending, they may want to > see 100 records at a time. Since we have 600+ records, there would be 7 > pages. They'd start on the first page (of course!) and there would be > links to pages 2 through 7 as well, just like with results pages of a > Google search. They could, from page 1, click any of the other pages to go > immdiately to that set of 100 records for display. > > I see this kind of thing all over the place, and in looking it up, I see > most solutions use "SELECT TOP x", which postgreSQL doesn't seem to have. > I know how to use LIMIT, but that always starts from the top. I could add > a piece to the WHERE clause, say something like "WHERE date > 01-02-1853", > but how do I know where the cutoffs are several pages along, without > retrieving the whole record set? > > I suppose the optimal solution for me would be to sort all of the records, > then be able to select a range from that sorted record set. So, if they > click the link to page 3, I'd like to do this (in pseudocode): > > 1. SORT records by the date field, descending > 2. Retrieve only records 200-299 from the sorted list > > Is there a way to do that? How is it done elsewhere? > > Thanks in advance for your help, > Chris McCormick, webmaster > The Athenaeum - Interactive Humanities Online > www.the-athenaeum.org > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
