Re: [SQL] 7.2 date/time format function problems
Oooh. Looks like TIMESTAMP became a reserved keyword. http://archives.postgresql.org/pgsql-patches/2001-11/msg00038.php >>> Nicholas Barthelemy <[EMAIL PROTECTED]> 10/22/02 08:39AM >>> I have just installed redhat 8.0. It comes with postgresql rpms for 7.2.2. I have been trying to get an application I have written to work, but my queries fail whenever I have queries that use internal date/time formatting functions. example: SELECT a.assignmentid AS "id", u.lastname || ', ' || u.firstname || ' ' || u.middleint AS "assigned", j.name AS "job_name", extract(DOW FROM TIMESTAMP TIMESTAMP(a.startdate)) AS "dow", TO_CHAR(TO_TIMESTAMP(a.starttime, 'HH:MI:SS'), 'HH12:MI am') AS "starttime", TO_CHAR(TO_TIMESTAMP(a.stoptime, 'HH:MI:SS'), 'HH12:MI am') AS "stoptime", ((extract(HOUR FROM TIME (a.stoptime - a.starttime)) + (extract(MINUTE FROM TIME (a.stoptime - a.starttime))/60)) - a.break) AS "hrs", a.break AS "break" FROM assignment a LEFT JOIN users u USING(userid), schedule s, job j, account ac, location l, groups g WHERE s.scheduleid = 1 AND s.scheduleid = a.scheduleid AND s.accountid = 3 AND s.accountid = ac.accountid AND s.locationid = 1 AND s.locationid = l.locationid AND s.groupid = g.groupid AND s.scheduleid = s.scheduleid AND a.jobid = j.jobid ORDER BY j.name ASC, a.starttime ASC, u.lastname ASC; ERROR: parser: parse error at or near "TIMESTAMP" The problem areas are the timestamp() and extract(hour from time) functions. If anyone would be so kind as to help me with this issue, it would be greatly appreciated. I don't know if I have to enable something for these functions to work or if the format changed for 7.2.2. I checked the documentation and it was exactly like 7.1. Thanks for your help, Nicholas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Finding table constraints
Hi , I have a small doubt , if any one knows how to know Constraints of a table in Oracle9i , won't you please tell me Warm Regards Jadcharla SrinivasViipurinkatu 1C 86,00510 HELSINKI,FINLAND.Phone:358408208487(Moblie)Do you Yahoo!? Y! Web Hosting - Let the expert host your web site
[SQL] 7.2 time format funtion issue
I have just installed redhat 8.0. It comes with postgresql rpms for 7.2.2. I have been trying to get an application I have written to work, but my queries fail whenever I have queries that use internal date/time formatting functions. example: SELECT a.assignmentid AS "id", u.lastname || ', ' || u.firstname || ' ' || u.middleint AS "assigned", j.name AS "job_name", extract(DOW FROM TIMESTAMP TIMESTAMP(a.startdate)) AS "dow", TO_CHAR(TO_TIMESTAMP(a.starttime, 'HH:MI:SS'), 'HH12:MI am') AS "starttime", TO_CHAR(TO_TIMESTAMP(a.stoptime, 'HH:MI:SS'), 'HH12:MI am') AS "stoptime", ((extract(HOUR FROM TIME (a.stoptime - a.starttime)) + (extract(MINUTE FROM TIME (a.stoptime - a.starttime))/60)) - a.break) AS "hrs", a.break AS "break" FROM assignment a LEFT JOIN users u USING(userid), schedule s, job j, account ac, location l, groups g WHERE s.scheduleid = 1 AND s.scheduleid = a.scheduleid AND s.accountid = 3 AND s.accountid = ac.accountid AND s.locationid = 1 AND s.locationid = l.locationid AND s.groupid = g.groupid AND s.scheduleid = s.scheduleid AND a.jobid = j.jobid ORDER BY j.name ASC, a.starttime ASC, u.lastname ASC; ERROR: parser: parse error at or near "TIMESTAMP" The problem areas are the timestamp() and extract(hour from time) functions. If anyone would be so kind as to help me with this issue, it would be greatly appreciated. I don't know if I have to enable something for these functions to work or if the format changed for 7.2.2. I checked the documentation and it was exactly like 7.1. Thanks for your help, Nicholas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Joining tables from different relational databases with AlligatorDBC
Hello everybody, with AlligatorDBC (database connectivity) you are able to join table from different relational databases f.e. a table in MySQL mytab and a table in Oracle oratab This works for Oracle8i, MySQL, Interbase, Birdstep RDM, PostgreSQL and MSSQL. Select * from oratab, mytab where oratab.id = mytab.id Export and Import from f.e. MySQL to Oracle Create table oratab as Select * from mytab. If you need information just send us a e-mail to [EMAIL PROTECTED] We will send you an article about the AlligatorDBC/SQL and the techniques. Hope that helps ? Best regards Manfred Peter (Alligator Company GmbH i.Gr.) http://www.alligatorsql.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Multiple Databases
Is it possible to create a view using tables from two different postgresql servers? Thanks Peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sub-select with aggregate
On Wed, 23 Oct 2002, Tomasz Myrta wrote: > Hi > I want to perform query looking like this: > > select > user_id, > a/sum_a as percent_a, > b/sum_b as percent_b > from > users join > (select > group_id, > sum(a) as sum_a, > sum(b) as sum_b >from users group by group_id) X using (group_id) > where group_id=3; > > This query works, but very slow. Subquery with aggregate is performed > for all table rows instead of group_id=3. Does using X.group_id=3 in the where clause work better? On 7.3 with no statistics for the table, that appears to move the filter into the subquery plan rather than the outer users scan. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 7.2 time format funtion issue
extract(DOW FROM TIMESTAMP TIMESTAMP(a.startdate)) AS "dow", ERROR: parser: parse error at or near "TIMESTAMP" Try one of these: extract(dow from cast(a.startdate as timestamp)) as "dow" extract(dow from a.startdate) as "dow" extract(dow from a.startdate::timestamp) as "dow" I remember I had problems when migrating to Postgres 7.2. I don't remember why, but I had to convert all "timestamp" to "timestamp without time zone". Probably this was because of ZEOS-direct-access components for C++ Builder. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sub-select with aggregate
Uz.ytkownik Stephan Szabo napisa?: Does using X.group_id=3 in the where clause work better? It works better, but not if you want to create a view and make "select * from some_view where group_id=3" :-( On 7.3 with no statistics for the table, that appears to move the filter into the subquery plan rather than the outer users scan. Do you mean the second query will work on 7.3? select group_id, user_id, a/sum_a as percent_a, b/sum_b as percent_b from users U, (select sum(a) as sum_a, sum(b) as sum_b from users where group_id=U.group_id) X where group_id=3; Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] problem with a query
hi to all i need to do a query to a small postgres database my best efort to the pretended query is in the attached file 'query.txt' but i need that some rows don't be selected of thouse that the query returns. i send in another attached file 'result.txt' with the query result. looking at content of the file you will see some notes at the end of the table (#..) the database as the data to enable the constrution of a school class schedule. In the 'result.txt' all the rows with max=0 were the fisrt to be inserted do the database. each row represent as example math pratical starting at 8:30 for 1:30 at room 3.15 in the bilding CP the rows with max!=0 (1,2,3,...) mean that content of an insert with max=0 was changed [ex diferent time (fiel to_char) or room (field sala) or bilding (field edificio)of the school class]. row with max=1 is a substitution to a row with max=0, row with max=2 substitutes row with max=1 and ... my objective is to obtain all the rows that were not substituted and the ones that represent the last alteration to the shedule i hope the problem was understod thanks for your time _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 query.txt Description: Binary data result.txt Description: Binary data ---(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] sub-select with aggregate
On Wed, 23 Oct 2002, Tomasz Myrta wrote: > Uz.ytkownik Stephan Szabo napisa?: > > Does using X.group_id=3 in the where clause work better? > It works better, but not if you want to create a view and make > "select * from some_view where group_id=3" :-( But you can't do that anyway, because you don't expose group_id in the original query. I assume user_id was a mistake then and was meant to be group_id or that both were meant to be in the select list. I see these two queries that in 7.3 push the clause into the subquery and I believe should have the same output: create view v as select group_id, a/sum_a as percent_a, b/sum_b as percent_b from (select group_id, sum(a) as sum_a, sum(b) as sum_b from users group by group_id) X join users using (group_id); and create view v as select X.group_id, a/sum_a as percent_a, b/sum_b as percent_b from users join (select group_id, sum(a) as sum_a, sum(b) as sum_b from users group by group_id) X on (X.group_id=users.group_id); In the first case changing the order means that the output group_id column is X.group_id rather than users.group_id (using removes one of them which is why group_id isn't ambiguous. In the second it uses on to get both group_ids and exposes the one from X. > > On 7.3 with no statistics for the table, that appears > > to move the filter into the subquery plan rather than the > > outer users scan. > Do you mean the second query will work on 7.3? No, the case of setting X.group_id. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] problem with a query
Użytkownik Carlos Sousa napisał: hi to all i need to do a query to a small postgres database my best efort to the pretended query is in the attached file 'query.txt' but i need that some rows don't be selected of thouse that the query returns. i send in another attached file 'result.txt' with the query result. looking at content of the file you will see some notes at the end of the table (#..) the database as the data to enable the constrution of a school class schedule. In the 'result.txt' all the rows with max=0 were the fisrt to be inserted do the database. each row represent as example math pratical starting at 8:30 for 1:30 at room 3.15 in the bilding CP the rows with max!=0 (1,2,3,...) mean that content of an insert with max=0 was changed [ex diferent time (fiel to_char) or room (field sala) or bilding (field edificio)of the school class]. row with max=1 is a substitution to a row with max=0, row with max=2 substitutes row with max=1 and ... my objective is to obtain all the rows that were not substituted and the ones that represent the last alteration to the shedule i hope the problem was understod I'm not sure, but you want to get data for each room (field "sala") with the biggest field "indice"? you can do this with query: select distinct on (indice) ... from ... order by sala,indice desc; Result of this query is - one row per one "sala". If there are more then 1 records in database with the same "sala", you get record with biggest "indice". You don't need to use max(indice) and group by... Tomasz Myrta ---(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] sub-select with aggregate
Uz.ytkownik Stephan Szabo napisa?: But you can't do that anyway, because you don't expose group_id in the original query. I assume user_id was a mistake then and was meant to be group_id or that both were meant to be in the select list. Yes, I meant group_id, but in orginal query I didn't have to add group_id to select list. In the first case changing the order means that the output group_id column is X.group_id rather than users.group_id (using removes one of them which is why group_id isn't ambiguous. In the second it uses on to get both group_ids and exposes the one from X. The problem isn't ambigous columns, but speed. I think Postgres first performs sub-query with all records from table (it takes very long time). After this Postgres permforms joining table with sub-query. The question is: How to speed up query like this? How to give param group_id from first table (users) to subquery? Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] sub-select with aggregate
On Wed, 23 Oct 2002, Tomasz Myrta wrote: > Uz.ytkownik Stephan Szabo napisa?: > > But you can't do that anyway, because you don't expose group_id > > in the original query. I assume user_id was a mistake then and was > > meant to be group_id or that both were meant to be in the > > select list. > Yes, I meant group_id, but in orginal query I didn't have to add > group_id to select list. Without group_id in the select list you couldn't do a where group_id = if the select was a view. > > In the first case changing the order means that the output > > group_id column is X.group_id rather than users.group_id > > (using removes one of them which is why group_id isn't > > ambiguous. In the second it uses on to get both group_ids > > and exposes the one from X. > The problem isn't ambigous columns, but speed. > I think Postgres first performs sub-query with all records from table > (it takes very long time). After this Postgres permforms joining table > with sub-query. The question is: How to speed up query like this? How to > give param group_id from first table (users) to subquery? Did you see the other two queries I gave? On 7.3, both of those queries appear (according to explain output) to do the limiting of group_id inside the subquery rather than doing the subquery with all rows. The explanation above was why I believe it was different from your original query. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sub-select with aggregate
On Wed, 23 Oct 2002, Stephan Szabo wrote: > Did you see the other two queries I gave? On 7.3, both of those queries > appear (according to explain output) to do the limiting of group_id > inside the subquery rather than doing the subquery with all rows. > The explanation above was why I believe it was different from your > original query. Replying to myself with a clarification. The other forms only move the filtering clauses around. There's still only a filter on the outer group_id equaling the inner group_id and a filter on group_id=3. It's just a question of whether it's: Scan users in subselect from group_id=3, group and aggregate them and join with users on subselect's group_id = users.group_id or Scan users for group_id=3, scan users in subselect, group and aggregate them and join these two on subselect's group_id=users.group_id. The latter is effectively what your original query gave, the former appears to be what my two queries from a couple of mails ago gives. ---(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] sub-select with aggregate
On Wed, 23 Oct 2002, Tomasz Myrta wrote: > > On Wed, 23 Oct 2002, Tomasz Myrta wrote: > > > > > Uz.ytkownik Stephan Szabo napisa?: > > Without group_id in the select list you couldn't do a where > > group_id = if the select was a view. > I know - it was just example of query. > > > Did you see the other two queries I gave? On 7.3, both of those queries > > appear (according to explain output) to do the limiting of group_id > > inside the subquery rather than doing the subquery with all rows. > > The explanation above was why I believe it was different from your > > original query. > I saw them. So should I wait for 7.3? Currently I have 7.2 and my work > stopped because of this. IIRC 7.2's explain doesn't show which clauses are on which plans (it'll show that it's doing an index scan or whatever, but not what it's actually looking for. The query probably optimizes similarly though. I'd suggest trying it and seeing if it runs any better. I'm also assuming you have an index on group_id to prevent the sequential scan of users. ---(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] Multiple Databases
Peter, > Is it possible to create a view using tables from two different > postgresql > servers? No. -Josh Berkus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Multiple Databases
Josh Berkus wrote: Is it possible to create a view using tables from two different postgresql servers? No. It isn't possible with plain old SQL, but it is possible (albeit ugly) using contrib/dblink in PostgreSQL 7.2.x. See README.dblink for documentation and examples. In PostgreSQL 7.3 (in beta testing now) the syntax and usability of contrib/dblink is greatly improved. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sub-select with aggregate
> On Wed, 23 Oct 2002, Tomasz Myrta wrote: > > > Uz.ytkownik Stephan Szabo napisa?: > Without group_id in the select list you couldn't do a where > group_id = if the select was a view. I know - it was just example of query. > Did you see the other two queries I gave? On 7.3, both of those queries > appear (according to explain output) to do the limiting of group_id > inside the subquery rather than doing the subquery with all rows. > The explanation above was why I believe it was different from your > original query. I saw them. So should I wait for 7.3? Currently I have 7.2 and my work stopped because of this. Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] odbc drivers
Hi; I've got PostgreSQL 7.2 and I would like to use MSAccess2k as a front end; in a LAN, because some of my colleagues just use Windows. Where could I find a ODBC driver for Postgresql to be use under windows2000? thanks and regards -- Javier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] plpgsql cursors : dynamic or static?
Hi: Are cursors in plpgsql dynamic or static? For example : ... /* some code */ FOR rec in SELECT f1,f2 FROM table1 WHERE LOOP /* some codes that manipulate table1 */ END LOOP; Do the result set pointed to by the cursor remains the same even if performed some data manipulation inside the FOR..LOOP? Thank you in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] odbc drivers
> Hi; > I've got PostgreSQL 7.2 and I would like to use MSAccess2k as a > front end; in a LAN, because some of my colleagues just use Windows. > Where could I find a ODBC driver for Postgresql to be use under windows2000? > > thanks and regards What about http://odbc.postgresql.org ? Isn't it enough? Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] odbc drivers
> Hi; > I've got PostgreSQL 7.2 and I would like to use MSAccess2k as a > front end; in a LAN, because some of my colleagues just use Windows. > Where could I find a ODBC driver for Postgresql to be use under windows2000? What about http://odbc.postgresql.org ? Isn't it enough? -- Tomasz Myrta <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Hairy question - transpose columns
Hello; I've got pluviometric data in a bizarre format (spanish administration is rather original) and I need to "transpose" them, to be able to use them as time series data for a model. The original data are in a format like cod_var | Year | Month | Ten | RainDay1 | RainDay2 | RainDay3 | Rainday4 | Rainday5 | RainDay6 | RainDay7 | RainDay8 | Rainday9 | Rainday10 | Rainday11 --+---++-++++++++++++ 452 | 1995 | 1 | 1 | 2 | 5 | 6 | -3 | 0 |5|4 | 5 | 4 | 4 | 452 | 1995 | 3 |2| 4 | 5 | 0 | 5 | 3 | 23 |4 | 34 | 4 | 2 | 452 | 1996 | 12|3| 12| 2 | 3 | 4 | 7 |3|3 | 15 | 2 | 4 |3 452 | 1998 | 9 |2| 2 | 8 | 6 | -3 | 5 |0|2 | 6 | 0 | 1 | 452 | 1998 | 3 |3| 2 |-3 | 7 | 9 | 4 |2|5 | 6 | 1 | 16|3 .. As you may see, the date of a rain datum is defined by the value of the fields Year, Month,Ten (1=first ten days of month; 2=second ten days of month; 3=up to eleven last days of month). and the field in which the datum is (RainDay1, RainDay2...) This is no useful for me because I need something like: cod_var | Year | Month | Day | Rain | --+---++-++-- 452 | 1995 | 1 | 1 | 2 | 452 | 1995 | 1 | 2 | 5 | 452 | 1995 | 1 | 3 | 6 | 452 | 1995 | 1 | 4 | -3 | 452 | 1995 | 1 | 5 | 0 | 452 | 1995 | 1 | 6 | 5 | ... Perhaps this is not possible to do with a database? Should I manage to make a program to fix this? Thanks for your help. I really can't imagine how to do this with Postresql; though I'm a newbye. Regards --- Javier ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Hairy question - transpose columns
On Wed, 23 Oct 2002, andres javier garcia garcia wrote: > Hello; I've got pluviometric data in a bizarre format (spanish > administration is rather original) and I need to "transpose" them, to > be able to use them as time series data for a model. > As you may see, the date of a rain datum is defined by the value of > the fields Year, Month,Ten (1=first ten days of month; 2=second ten > days of month; 3=up to eleven last days of month). and the field in > which the datum is (RainDay1, RainDay2...) > > This is no useful for me because I need something like: > > cod_var | Year | Month | Day | Rain | > --+---++-++-- > 452 | 1995 | 1 | 1 | 2 | > 452 | 1995 | 1 | 2 | 5 | > 452 | 1995 | 1 | 3 | 6 | > 452 | 1995 | 1 | 4 | -3 | > 452 | 1995 | 1 | 5 | 0 | > 452 | 1995 | 1 | 6 | 5 | > ... > > Perhaps this is not possible to do with a database? Should I manage to > make a program to fix this? I'm sure there's a better way, but I think a series of union alls would do it but be rather computationally expensive. select cod_var, Year, Month, 1 as Day, RainDay1 as Rain where Ten=1 union all select cod_var, Year, Month, 2 as Day, RainDay2 as Rain where Ten=1 ... union all select cod_var, Year, Month, 11 as Day, Rain Day 1 as Rain where Ten=2 ... union all select cod_var, Year, Month, 29 as Day, RainDay9 as Rain where Ten=3 and RainDay29 is not null; ... I'm uncertain what you do for days at the end of a month that don't exist, I'm guessing they have nulls. In 7.3, I'd say you might be able to make a function to do this that returns a result set which would probably be much better. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Hairy question - transpose columns
> I'm sure there's a better way, but I think a series of union alls would > do it but be rather computationally expensive. > > select cod_var, Year, Month, 1 as Day, RainDay1 as Rain > where Ten=1 > union all > select cod_var, Year, Month, 2 as Day, RainDay2 as Rain > where Ten=1 You could do the following: select cod_var, Year, Month, 1+((ten-1)*10) as Day, RainDay1 as Rain where RainDay1 is not null union all select cod_var, Year, Month, 2+((ten-1)*10) as Day, RainDay2 as Rain where RainDay2 is not null .. I'm sure that there is a function that could do this too, but I'd tend to just convert the data and be done with it. eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] plpgsql cursors : dynamic or static?
Ludwig, > Are cursors in plpgsql dynamic or static? > > For example : > > ... /* some code */ > FOR rec in SELECT f1,f2 >FROM table1 WHERE LOOP > > /* some codes that manipulate table1 */ > END LOOP; Technically speaking, that is not a cursor, even though it serves the same purpose. I'm pretty sure it's static, but it would be easy to test ... why don't you give it a try? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] plpgsql cursors : dynamic or static?
Josh Berkus <[EMAIL PROTECTED]> writes: >> Are cursors in plpgsql dynamic or static? >> >> FOR rec in SELECT f1,f2 >> FROM table1 WHERE LOOP >> >> /* some codes that manipulate table1 */ >> END LOOP; > Technically speaking, that is not a cursor, even though it serves the same > purpose. > I'm pretty sure it's static, but it would be easy to test ... why don't you > give it a try? The result definitely will be static in 7.3. I'm not entirely sure about prior releases: we used to manipulate the current-command counter in a different way. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Sum of Every Column
Hi Folks, I'm hoping to put together a query that generates a report on a table with a large number of boolean fields. This report has to be able to adapt to the number of fields in the table. Essentially, I want it to provide the sum of TRUE values for each field for a given subset of the table. I've got the query that returns the subset of the table (this is based on a relationship with another table): SELECT breast_cancer_resources.* FROM breast_cancer_resources, agency_contact_info WHERE breast_cancer_resources.id=agency_contact_info.id AND agency_contact_info.guideregion=1 AND agency_contact_info.list_online=TRUE But I'm not sure how to generate the sum for each column. Should I be looking elsewhere than SQL to do this for me, such as php (this is for a web-based report)? Thanks, Tom ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Sum of Every Column
Hi Folks, I'm hoping to put together a query that generates a report on a table with a large number of boolean fields. This report has to be able to adapt to the number of fields in the table. Essentially, I want it to provide the sum of TRUE values for each field for a given subset of the table. I've got the query that returns the subset of the table (this is based on a relationship with another table): SELECT breast_cancer_resources.* FROM breast_cancer_resources, agency_contact_info WHERE breast_cancer_resources.id=agency_contact_info.id AND agency_contact_info.guideregion=1 AND agency_contact_info.list_online=TRUE But I'm not sure how to generate the sum for each column. Should I be looking elsewhere than SQL to do this for me, such as php (this is for a web-based report)? Thanks, Tom ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sum of Every Column
Tom, You can add sum( case when then 1 else 0 end) for each field that you need. JLL Tom Haddon wrote: > > Hi Folks, > > I'm hoping to put together a query that generates a report on a table with > a large number of boolean fields. This report has to be able to adapt to > the number of fields in the table. Essentially, I want it to provide the > sum of TRUE values for each field for a given subset of the table. I've > got the query that returns the subset of the table (this is based on a > relationship with another table): > > SELECT breast_cancer_resources.* > FROM breast_cancer_resources, agency_contact_info > WHERE breast_cancer_resources.id=agency_contact_info.id > AND agency_contact_info.guideregion=1 > AND agency_contact_info.list_online=TRUE > > But I'm not sure how to generate the sum for each column. Should I be > looking elsewhere than SQL to do this for me, such as php (this is for a > web-based report)? > > Thanks, Tom > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sum of Every Column
Tom, > I'm hoping to put together a query that generates a report on a table with > a large number of boolean fields. This report has to be able to adapt to > the number of fields in the table. Essentially, I want it to provide the > sum of TRUE values for each field for a given subset of the table. I've > got the query that returns the subset of the table (this is based on a > relationship with another table): Sounds like you have schema problems. Is this a legacy database, or did you design it yourself? > > SELECT breast_cancer_resources.* > FROM breast_cancer_resources, agency_contact_info > WHERE breast_cancer_resources.id=agency_contact_info.id > AND agency_contact_info.guideregion=1 > AND agency_contact_info.list_online=TRUE > > But I'm not sure how to generate the sum for each column. Should I be > looking elsewhere than SQL to do this for me, such as php (this is for a > web-based report)? You cannot dynamically sum all columns through SQL. You would need to use a procedural language to loop through the columns and create sum clauses for each one. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] sub-select trouble: wrong SQL or PostgreSQL issue?
Greetings, I have 3 tables. Two of which (ta and tb) are different "attributes" the third table (tc) can have. tc is allowed up to three of each kind of "attributes". e.g., 3 ta values and 2 tb values. By assigning ta and tb attributes to each entry in tc you are also "matching" tb attributes with corresponding ta ones. e.g., c_id = 8 ta1 = 1 <-- match --> tb1 = 3 ta2 = 3 <-- match --> tb2 = 2 ta3 = 13 <-- match --> tb3 = 20 c_id = 9 ta1 = 2 <-- match --> tb1 = 3 ta2 = 5 <-- match --> tb2 = 3 ta3 = 9 <-- match --> tb3 = 2 c_id = 10 ta1 = 3 <-- match --> tb1 = 2 ta2 = 1 <-- match --> tb2 = 3 ta3 = 9 <-- match --> tb3 = 2 The task at hand is to figure out the number of ta attributes each tb attribute corresponds with. For above example I want a result set showing me: tb.b_idta.a_id count -- 22 -- matched w/ta.a_id = 3, 9 33 -- matched w/ta.a_id = 1, 2, 5 201 -- only matched w/ta.a_id = 13 -- tables create table ta ( a_id int unique ); create table tb ( b_id int unique ); create table tc ( c_idint unique, ta1 int references ta( a_id ), ta2 int references ta( a_id ), ta3 int references ta( a_id ), tb1 int references tb( b_id ), tb2 int references tb( b_id ), tb3 int references tb( b_id ) ); -- sequences create sequence ta_seq; create sequence tb_seq; create sequence tc_seq; -- populate tables with some data insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into ta values ( NEXTVAL( 'ta_seq' ) ); insert into tb values ( NEXTVAL( 'tb_seq' ) ); insert into tb values ( NEXTVAL( 'tb_seq' ) ); insert into tb values ( NEXTVAL( 'tb_seq' ) ); insert into tb values ( NEXTVAL( 'tb_seq' ) ); -- ... insert into tb values ( NEXTVAL( 'tb_seq' ) ); insert into tb values ( NEXTVAL( 'tb_seq' ) ); Don't read too much into the data as i don't think the values chosen have anything to do with the problem. I am just using a set of data sequences from my live database. Though, i'm willing to be surprised if someone's willing to show correlation between the problem and the values used. -- ta tb -- 1 2 3 1 2 3 insert into tc values ( NEXTVAL( 'tc_seq' ), 1, 1, 1, 1, 3, 2 ); insert into tc values ( NEXTVAL( 'tc_seq' ), 3, 3, 3, 2, 1, 3 ); insert into tc values ( NEXTVAL( 'tc_seq' ), 4, 4, 4, 2, 1, 3 ); insert into tc values ( NEXTVAL( 'tc_seq' ), 5, 5, 5, 1, 2, 3 ); insert into tc values ( NEXTVAL( 'tc_seq' ), 5, 5, 5, 1, 2, 3 ); insert into tc values ( NEXTVAL( 'tc_seq' ), 1, 1, 1, 1, 2, 3 ); insert into tc values ( NEXTVAL( 'tc_seq' ), 4, 4, 4, 2, 1, 3 ); insert into tc values ( NEXTVAL( 'tc_seq' ), 3, 3, 3, 2, 1, 3 ); /* insert into tc values ( NEXTVAL( 'tc_seq' ), 1, null, null, 1, null, null ); */ This last insert is to show it is possible to have null values for either of the ta or tb attributes in table tc. But it is omitted to simplify the problem for now. I am no SQL expert, as in I don't do it for a living. Therefore, I would not doubt that there exist more efficient ways to do this but this is one way I came up with and it is quite possible that a SQL mistake is causing the strange outputs I am observing. But let me describe in English what i am attempting to do in the SQL. As I said earlier the task is to determine the unique count of ta entries each tb.b_id is associated with in the tc table. I'm selecting on the tb table and doing sub selects on the tc table where I attempt to count unique counts of tb to ta matches. First sub-select (aa) does a distinct count of tc.ta1 where tc.tb1 equals tb.b_id and the corresponding tc.ta1 field is not null. Second sub-select (bb) I'm again doing a count of distinct tc.ta2 where tc.tb2 is equal to tb.b_id, tc.ta2 is not null and tc.ta2 was not counted in the aa sub-select. Next (cc), once again do a count of distinct tc.ta3 where tc.tb3 is equal to tb.b_id, tc.ta3 is not null and not in either of the two previous sub-selects (aa, bb). The sum of sub-selects aa, bb and cc should be the value I'm interested in (not shown in SQL statement). The problem: The result of the select is not the expected counts of ta to tb. The twist: If the select statement is provided a where-clause where each tb.b_id is restricted to individual tb.b_id values then the c
Re: [SQL] sub-select trouble: wrong SQL or PostgreSQL issue?
> Strangely, this is the result we were expecting from our > original query! > > Is it possible that the sub-selects are somehow > affecting the result sets? > > > Seen on: > % postmaster --version > postmaster (PostgreSQL) 7.1.3 > > and > > % postmaster --version > postmaster (PostgreSQL) 7.1.2 I think you want to upgrade, 7.3beta at least gives your expected output on all the queries when I tried it. I don't currently have a 7.2.* system to test on here to try it with that series. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sub-select trouble: wrong SQL or PostgreSQL issue?
>> Is it possible that the sub-selects are somehow >> affecting the result sets? > I think you want to upgrade, 7.3beta at least gives your > expected output on all the queries when I tried it. I don't > currently have a 7.2.* system to test on here to try it > with that series. I think this may be the same problem as bug #526, http://archives.postgresql.org/pgsql-bugs/2001-11/msg00168.php If so, the fix is in 7.2.*. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement
Hi: Is there a way to emulate a SELECT..FOR UPDATE to series of LOCK/SELECT statement. I tried the following statements using 2 psql terminals. T1 | T2 1)BEGIN; | 2)SELECT x | BEGIN; FROM y | WHERE y=1 | FOR UPDATE;| 3) | SELECT x | FROM y | WHERE y=1 | FOR UPDATE; 4)COMMIT;| 5) | COMMIT; At point #3 T2 will wait, however changing the WHERE clause to other clause such as "WHERE y=2" will allow T2 to proceed. - I tried changing the SELECT..FOR UPDATE into LOCK SHARE MODE followed by a SELECT (but w/o FOR UPDATE) but it T2 is allowed to proceed even for the clause "where y=1". I am surprised because according to the docs (version 7.2), it says: ROW SHARE MODE Note: Automatically acquired by SELECT ... FOR UPDATE. I'm assuming that the SELECT..FOR UPDATE performs a lock in ROW SHARE MODE before the SELECT. I also tried changing the lock mode into SHARE ROW EXCLUSIVE MODE / EXCLUSIVE MODE but still T2 won't be allowed to passed even the for the clause "where y=2". Is there any to do it? I'm asking becuase our db libaries (using libpq) always a cursor when generating a SELECT statement thus I'm encourtering the following error message when I use SELECT..FOR UPDATE: Cursor must be READ ONLY. DECLARE/UPDATE is not supported. Another alternative would be studying libpq and removing the cursors in a SELECT statement. By the way is there any side effect / disadavtages when I remove the "DELCARE CURSOR" statement and change it to plain SELECT statememt in C++ codes? Thank you in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] sub-select with aggregate
Hi I want to perform query looking like this: select user_id, a/sum_a as percent_a, b/sum_b as percent_b from users join (select group_id, sum(a) as sum_a, sum(b) as sum_b from users group by group_id) X using (group_id) where group_id=3; This query works, but very slow. Subquery with aggregate is performed for all table rows instead of group_id=3. I wish I could write it like this, but it doesn't work at all: select user_id, a/sum_a as percent_a, b/sum_b as percent_b from users U, (select sum(a) as sum_a, sum(b) as sum_b from users where group_id=U.group_id) X where group_id=3; I don't want to make it as single query with "group by", because it has a lot of fields and "group by" would be very long. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sub-select with aggregate
> The other forms only move the filtering clauses around. There's > still only a filter on the outer group_id equaling the inner > group_id and a filter on group_id=3. It's just a question of > whether it's: > > Scan users in subselect from group_id=3, group and aggregate them > and join with users on subselect's group_id = users.group_id > > or > > Scan users for group_id=3, scan users in subselect, group and > aggregate them and join these two on subselect's > group_id=users.group_id. > > The latter is effectively what your original query gave, the former > appears to be what my two queries from a couple of mails ago gives. I'm sorry, if you misunderstood me - I didn't show it clear. I want to scan users for group_id=3 and then join subselect for the same group_id. I can't pass group_id=3 directly to subselect, because it's not a query but a view. -- Tomasz Myrta <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
