Re: [SQL] question on SELECT
Howard Hiew <[EMAIL PROTECTED]> writes: > Hi, > I would like to know what is the sql statement that list all the tables > name. > > For example in Oracle, > 'SELECT TABLE_NAME from ALL_TABLES where owner="Oracle" '; > > What is the statement for Postgres? > Thank you > > Best Regards, > Howard > CIM/MASTEC > Tel:(65)8605283 You can do '\dt' to list all tables. There is also a system table 'pg_tables' which you can use if you like to do a select instead. Do SELECT tablename FROM pg_tables where tableowner='postgres'; -- Prasanth Kumar [EMAIL PROTECTED]
Re: [SQL] Tree structure table normalization problem (do I need atrigger?)
On Tue, 19 Dec 2000, Frank Joerdens wrote: > In a recent thread (How to represent a tree-structure in a relational > database) I asked how to do a tree structure in SQL, and got lots of > suggestions (thanks!), of which I chose the one below: > > create table Category ( > CategoryID int4 not null primary key, > ParentCategoryID int4 not null REFERENCES Category (CategoryID), > CategoryName varchar(100) > ); > > The one described in Joe Celko's article (the one with the worm that > travels along the edge of the tree . . . ) seemed more evolved but > requires fairly complex SQL stuff, I thought, for simple operations that > are straighforward with the above model. However, I have a problem now > which seems non-trivial: I am at some point in the tree, say 3 nodes > down from the root, but I don't know where I am exactly (across which > nodes would I travel along the shortest path to the top?) and would like > to find out. This is, again, not really difficult if I know how deep > into the tree I am, in which case I can simply do (I know that I am 3 > nodes from the root and that my current node number is x): That's exactly what the 'worm' stuff is all about... If you use the structure above, you have to use recursion in order to find out the path, which is AFAIK missing from SQL (not counting some DB2 feature which allows recursion, in a strange way, but we're talking Postgres here). This solution also does not require any client-side programming, since you only have to use one single statement, regardless of which level you are on in the tree. The SQL stuff of that nested set structure is fairly easy, I wrote some quick'n'dirty plpgsql functions that will do inserts, updates, deletes from the tree, display level number etc. I can send it to you if you like (please allow a few days since I have several exams at the university this week). Zsolt Tulassay > > SELECT A1.CategoryID, A2.CategoryID, A3.CategoryID FROM Category AS A1, > Category AS A2, Category AS A3 WHERE A3.CategoryID=x AND > A3.ParentCategoryID=A2.CategoryID AND A2.ParentCategoryID=A1.CategoryID; > > (This is probably very expensive if the tree gets really deep, but I > don't expect that to happen in my database anytime soon.) > > So I introduced a 'level' column into the above schema, where I store > the information about how deep I am into the tree to have it readily > available when I want to compute the path to the top. Unfortunately, > this is dangerous because the information is already implicit in the > original schema. The problem is that the only way I can see that you > would get at the information is by walking up the tree step-by-step > until you get a zero value (which is assigned to the root). This means > you need a loop control structure which means you have to write a > PL/pgSQL procedure (or some other procedure) that is run by a trigger to > update the level column on insert or update, as in > > WHILE (CategoryID is not null) LOOP > Run SQL statement to get the next higher-up node's CategoryID and > increment a counter. > END LOOP; > Return counter and insert value into level column. > > This seems to feasible but not really as straightforward as one might > hope. Is there an easier way? > > - Frank >
[SQL] replace??
hello, i have the following problem: i have in php an array structure with user-prefs that have to be stored onto DB actually i look up if the corresponding entry exists (comparing user-name and field-name) if yes i update, if no i insert this isn't very appealing, but i couldn't find another way yet to make this shorter... would be nice if something like the following existed: replace settings set auser='toto',field='lang',data='fr' where auser='toto' and field='lang'; update settings set auser='toto',field='lang',data='fr' where auser='toto' and field='lang' REPLACE; or even better: replace settings keys(auser,field) set auser='toto',field='lang',data='fr'; -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing
Re: [SQL] replace??
On Tue, 19 Dec 2000, Bruno Boettcher wrote: > actually i look up if the corresponding entry exists (comparing > user-name and field-name) if yes i update, if no i insert > > this isn't very appealing, but i couldn't find another way yet to make > this shorter... > > would be nice if something like the following existed: > > replace settings set auser='toto',field='lang',data='fr' where > auser='toto' and field='lang'; > > update settings set auser='toto',field='lang',data='fr' where > auser='toto' and field='lang' REPLACE; What is the difference between replacing data and updating data in this sense? What you might want to do with your array (which in PHP can also function as a hash, with string indices) is create a wrapper class that kind of emulates a hash tied to the DB like one might do in Perl, so that whenever you 'store' a value in the array (via a method), it will automagically update or insert into the underlying database. PHP doesn't do ties explicitly, but you can emulate this behaviour with a PHP class. -- Brett http://www.chapelperilous.net/~bmccoy/ --- The price of greatness is responsibility.
Re: [SQL] Tree structure table normalization problem (do I need atrigger?)
Tulassay Zsolt wrote: [ . . . ] > The SQL stuff of that nested set structure is fairly easy, I wrote some > quick'n'dirty plpgsql functions that will do inserts, updates, deletes > from the tree, display level number etc. What scared me about it in particular was one scenario where you try to delete a subtree. This would normally leave a gap, since the structure is based on the worm's ability to get from one node to the next with an increment of just 1. Once you had a subtree deleted, you'd either would have to have the worm leap-frog (a leaping frog-worm?!! :)) the gap or update an entire half of the tree to close it . . . then my brain started to hurt and I gave up. > I can send it to you if you like (please allow a few days since I > have several exams at the university this week). Sure, I'd like to have a look at it! Thanks, Frank
Re: [SQL] Tree structure table normalization problem (do I need atrigger?)
> Michael Ansley wrote: > > Hi, Frank, > > This is exactly why there are alternative solutions for trees. The mechanism that >you > are using traded input speed for 'queryability', and this is where you start to run >into > problems. Either you need to store redundant information (i.e.: the level) or you >need > to have a recursive or iterative procedure which steps back up to the top. > > Just for illustration, if you were using a node type to indicate the ID of each node > (say, something like 1, 1.1, 1.2, 1.2.1, etc.) then input into the table would be an > expensive operation, but querying this would involve only getting out the ID of the >node > that you were at, and splitting up the ID using the periods for separators. So if >you > wanted to traverse up the tree from node 1.2.5.3, (you know that it's at level three > simply by counting the periods)then the order would be: > > 1.2.5.3 > 1.2.5 > 1.2 > 1 It almost looks my very first attempt to do a tree, except that I had the node number not in one column but a column for each number: Instead of 1.2.5.3 1.2.5 1.2 1 I had 1|2|5|3 1|2|5|0 1|2|0|0 1|0|0|0 > And you only need the original node ID to do that, which is a single query. > > If you are not going to be doing this query often, and/or there are not going to be >many > levels, then you should probably consider the plpgsql solution, as it makes life >simple > for the programmer, and the cost is not really an issue, and you don't have to >maintain > redundant data. Denormalisation is a major pain when you start having to maintain >it. What exactly is the problem with it (are you referring to the scheme I proposed or to the 1.2.3-type scheme)? Actually, I don't really know now why I left my original idea. I guess it looked to simple. I think I will stick with my recursive parentid PL/pgSQL-trigger type schema for now. I'll just have to get into that PL/pgSQL stuff which I haven't properly looked at so far. Would this be a denormalized table then? The level information would be redundant but linked with the other columns via the trigger, hence there's nothing much that could go wrong . . . or is there a hidden maintenance penalty? Cheers, Frank
[SQL] SQL query not working when GROUP BY / HAVING is used
Hello there I have a question regarding a SQL statement. When I execute (and that's what I need) SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta >= 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc I get the following error in the pgadmin.log file. 19.12.00 10:53:34 Executing: SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta >= 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc 19.12.00 10:53:34 Executing SQL Query... 19.12.00 10:53:34 Done - 0,01 Secs. ** * Error - 19.12.00 10:53:34 ** Software Program: pgAdmin Version: 7.0.4 Sub or Function: frmSQL, cmdExecute_Click Error Details * Error No: -2147217887 Error Description: Der ODBC-Treiber unterstützt die angeforderten Eigenschaften nicht. Error Source: Microsoft OLE DB Provider for ODBC Drivers DLL Error Code: 0 Memory Details ** Total Physical: 132435968 Total Swap: 434098176 Total Virtual: 2147352576 Available Physical: 34004992 Available Swap: 291512320 Available Virtual: 2079350784 Percentage Free: 0 System Details ** Processor: 586 OEM ID: 0 No. Processors: 1 Page Size: 4096 OS Details ** Platform: Windows NT Version: 4.0 Build: 1381 OS Info: Service Pack 5 Environment Details *** Datasource: pgmondadori Tracking: False TrackVer: 0 Connect: Provider=MSDASQL.1;Extended Properties ="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD **;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS " Version: 2.6 Using the same statement without the GROUP BY and HAVING it is ok ! SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND (Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll ANDAuftrag.A_Ztyp=Zylinder.Z_typ ANDZ_A_nr =NULL ANDZ_status = 'zcu' ORDER BY Zylinder_Typen.Z_durch_soll desc Whats wrong, according to the docs, the syntax is ok and it should be possible to use these keywords! PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315
Re: [SQL] Tree structure table normalization problem (do I need atrigger?)
On Tue, 19 Dec 2000, Frank Joerdens wrote: > Tulassay Zsolt wrote: > [ . . . ] > > I can send it to you if you like (please allow a few days since I > > have several exams at the university this week). > > Sure, I'd like to have a look at it! I'd like to have a look at it as well, please. Cheers! (Relax...have a homebrew) Neil
Re: [SQL] Bounds checking on an alias
[EMAIL PROTECTED] wrote: > SELECT DISTINCT tbl_restaurant.restaurant, > tbl_restaurant_location.postal_code, tbl_restaurant_location.latitude > AS latitude, tbl_restaurant_location.longitude AS longitude, distance > (49.24894, -122.90419, latitude, longitude) AS distance FROM > tbl_restaurant, tbl_restaurant_location, tbl_restaurant_cuisine_link > WHERE tbl_restaurant.restaurant_id = > tbl_restaurant_location.restaurant_id AND tbl_restaurant.restaurant_id > = tbl_restaurant_cuisine_link.restaurant_id AND > tbl_restaurant_cuisine_link.cuisine_id = 14 AND > tbl_restaurant.price_id = 1 AND tbl_restaurant_location.latitude IS > NOT NULL AND tbl_restaurant_location.latitude > 49.113804 AND > tbl_restaurant_location.latitude < 49.384075 AND > tbl_restaurant_location.longitude > -123.03932 AND > tbl_restaurant_location.longitude < -122.76906 AND distance <= 15.0 > ORDER BY distance; > ERROR: Attribute 'distance' not found It's not related to your function or query, but it's a generic behaviour: changemaster=# select id + 1 as next, val from t; next | val --+-- 2 | Test (1 row) changemaster=# select id + 1 as next, val from t where next > 1; ERROR: Attribute 'next' not found changemaster=# select id + 1 as next, val from t where id + 1 > 1; next | val --+-- 2 | Test (1 row) The named expression isn't available in the WHERE clause. You have to copy the same expression in it. -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
Re: [SQL] SQL query not working when GROUP BY / HAVING is used
On Tue, 19 Dec 2000 [EMAIL PROTECTED] wrote: > Hello there > > I have a question regarding a SQL statement. > > When I execute (and that's what I need) > > SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, > ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, > (Zylinder.Z_durch_soll+0.12) AS effektiv > FROM Auftrag,Zylinder_Typen, Zylinder > WHERE Auftrag.A_nr = '11' > AND ( Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') > AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll > AND Auftrag.A_Ztyp=Zylinder.Z_typ > AND Z_A_nr = NULL > AND Z_status = 'zcu' > GROUP BY Zylinder.Z_durch_soll > HAVING durchmesserdelta >= 0.085 > ORDER BY Zylinder_Typen.Z_durch_soll desc This query is not legal SQL. All columns in the select list of a group by query must either be grouped columns or set value functions (pretty much anyway). The general construct is legal but there are syntax rules for GROUP BY that you are violating. If Z_id and Z_durch_ist are unique for values of Z_durch_soll you might try grouping on all of them. If not, the query above is indeterminate since you are not specifying which Z_id and which Z_durch_ist to use for a particular Z_durc_soll value.
Re: [SQL] Tree structure table normalization problem (do I need a trigger?)
Frank, > However, I have > a problem now > which seems non-trivial: I am at some point in the tree, > say 3 nodes > down from the root, but I don't know where I am exactly > (across which > nodes would I travel along the shortest path to the top?) > and would like > to find out. This is, again, not really difficult if I > know how deep > into the tree I am, in which case I can simply do (I know > that I am 3 > nodes from the root and that my current node number is > x): This is exactly why my model includes a "Level" column. It was more important to me to have the easy queriability of the "redundant" level info than to have the fluid flexibility of a tree without it. The choice sorta depends on what you're storing in the tree. > (This is probably very expensive if the tree gets really > deep, but I > don't expect that to happen in my database anytime soon.) Not really. You're querying (hopefully) two indexed fields within the same table, refrenced to itself. Once you've run it a few times, even the elaborate UNION query I posted will run very quickly - on my table (~300 items) it runs <2 seconds. > This means > you need a loop control structure which means you have to > write a > PL/pgSQL procedure (or some other procedure) that is run > by a trigger to > update the level column on insert or update, as in > This seems to feasible but not really as straightforward > as one might > hope. Is there an easier way? Hmmm. I don't know, Frank. That strikes me as a really good, straightforward workaround to your problem. I'm not sure what you could do that would be simpler. This is practically a textbook example of why triggers are necessary to retain relational integrity. -Josh Berkus
Re: [SQL] Tree structure table normalization problem (do I need atrigger?)
On Tue, 19 Dec 2000, Frank Joerdens wrote: > Tulassay Zsolt wrote: > [ . . . ] > > The SQL stuff of that nested set structure is fairly easy, I wrote some > > quick'n'dirty plpgsql functions that will do inserts, updates, deletes > > from the tree, display level number etc. > > What scared me about it in particular was one scenario where you try to delete a >subtree. > This would normally leave a gap, since the structure is based on the worm's ability >to get > from one node to the next with an increment of just 1. Once you had a subtree >deleted, > you'd either would have to have the worm leap-frog (a leaping frog-worm?!! :)) the >gap or > update an entire half of the tree to close it . . . then my brain started to hurt >and I > gave up. > that's exactly why i wrote a deletion function which updates the nodes' "left" and "right" values to close the gap. sure it causes a bit of overhead to update many nodes, but if you don't delete subtrees all the time, you can live with this. Zsolt Tulassay
Re: [SQL] Tree structure table normalization problem (do I need a trigger?)
Josh Berkus wrote: [ . . . ] > This is exactly why my model includes a "Level" column. I looked at your post from a few days ago again; you did indeed explain about the level column. I missed that somehow and had to reinvent the wheel . . . > > This means > > you need a loop control structure which means you have to > > write a > > PL/pgSQL procedure (or some other procedure) that is run > > by a trigger to > > update the level column on insert or update, as in > > > This seems to feasible but not really as straightforward > > as one might > > hope. Is there an easier way? > > Hmmm. I don't know, Frank. That strikes me as a really > good, straightforward workaround to your problem. I'm not > sure what you could do that would be simpler. This is > practically a textbook example of why triggers are necessary > to retain relational integrity. Cool. And I didn't consult a textbook ;). Actually, it's even simpler than I described above: The function that you run when the trigger fires is plain vanilla sql with a littel subselect thrown in: create function update_level(int4) returns int4 as 'update index set level=(A.level+1) from index as A where A.id = (select parentid from index where id = $1 ) and index.id = $1; select 1 as ignore_this;' LANGUAGE 'sql'; . . . i.e. you just get the level from the higher-up node's level plus 1, rather than walking to the top of the tree and counting the steps. This _doesn't_ work though if you move an entire subtree within the hierarchy to another level. Then you'd need to have a function that walks through the entire subtree to update the level column for every single node . . . hmmm. I'll think about it. I don't think I'll need it for the current project since I'll only allow the moving around of end nodes. Cheers, Frank
[SQL] Create table doesn't work in plpgsql
Hi,
can I do some table manipulation in plpgsql?
Look at only the "create table" line and the error message:
create function plural (text) returns text as '
begin
create table tmp (num int4);
return $1 || ''s'';
end;' language 'plpgsql';
select plural('test');
CREATE
ERROR: copyObject: don't know how to copy 611
What does the error message mean? Where can I read more about it?
Cheers, Volker
Re: [SQL] SQL query not working when GROUP BY / HAVING is used
I hope it may help: 1. if you use group clause in a select stmt, the select list must be agregate function such as sum(field),count(field), max(field)..., cannot use field. 2. for field have NULL field, should use field IS NULL, = NULL will give you wrong result! Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Tue, 19 Dec 2000 [EMAIL PROTECTED] wrote: > Hello there > > I have a question regarding a SQL statement. > > When I execute (and that's what I need) > > SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, > ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, > (Zylinder.Z_durch_soll+0.12) AS effektiv > FROM Auftrag,Zylinder_Typen, Zylinder > WHERE Auftrag.A_nr = '11' > AND ( Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') > AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll > AND Auftrag.A_Ztyp=Zylinder.Z_typ > AND Z_A_nr = NULL > AND Z_status = 'zcu' > GROUP BY Zylinder.Z_durch_soll > HAVING durchmesserdelta >= 0.085 > ORDER BY Zylinder_Typen.Z_durch_soll desc > > I get the following error in the pgadmin.log file. > > 19.12.00 10:53:34 Executing: SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, > Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) > AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM > Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( > Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = > Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL > AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta > >= 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc > 19.12.00 10:53:34 Executing SQL Query... > 19.12.00 10:53:34 Done - 0,01 Secs. > ** > * Error - 19.12.00 10:53:34 > ** > > Software > > Program: pgAdmin > Version: 7.0.4 > Sub or Function: frmSQL, cmdExecute_Click > > Error Details > * > Error No: -2147217887 > Error Description: Der ODBC-Treiber unterstützt die angeforderten > Eigenschaften nicht. > Error Source: Microsoft OLE DB Provider for ODBC Drivers > DLL Error Code: 0 > > Memory Details > ** > Total Physical: 132435968 > Total Swap: 434098176 > Total Virtual: 2147352576 > Available Physical: 34004992 > Available Swap: 291512320 > Available Virtual: 2079350784 > Percentage Free: 0 > > System Details > ** > Processor: 586 > OEM ID: 0 > No. Processors: 1 > Page Size: 4096 > > OS Details > ** > Platform: Windows NT > Version: 4.0 > Build: 1381 > OS Info: Service Pack 5 > > Environment Details > *** > Datasource: pgmondadori > Tracking: False > TrackVer: 0 > Connect: Provider=MSDASQL.1;Extended Properties > >="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD > > >**;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS > > " > Version: 2.6 > > > Using the same statement without the GROUP BY and HAVING it is ok ! > > SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, > ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, > (Zylinder.Z_durch_soll+0.12) AS effektiv > FROM Auftrag,Zylinder_Typen, Zylinder > WHERE Auftrag.A_nr = '11' > AND (Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') > AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll > ANDAuftrag.A_Ztyp=Zylinder.Z_typ > ANDZ_A_nr =NULL > ANDZ_status = 'zcu' > ORDER BY Zylinder_Typen.Z_durch_soll desc > > Whats wrong, according to the docs, the syntax is ok and it should be > possible to use these keywords! > > > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > phone: +4141 790 4040 > fax: +4141 790 2545 > mobile: +4179 211 0315 > >
Re: [SQL] SQL query not working when GROUP BY / HAVING is used
Hope it helps: 1. If you use GROUP, the select list should sum|count|max ..., no single field. 2. If you use NULL, the condition should be field IS [NOT] NULL, = NULL will give the wrong answer. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Tue, 19 Dec 2000 [EMAIL PROTECTED] wrote: > Hello there > > I have a question regarding a SQL statement. > > When I execute (and that's what I need) > > SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, > ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, > (Zylinder.Z_durch_soll+0.12) AS effektiv > FROM Auftrag,Zylinder_Typen, Zylinder > WHERE Auftrag.A_nr = '11' > AND ( Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') > AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll > AND Auftrag.A_Ztyp=Zylinder.Z_typ > AND Z_A_nr = NULL > AND Z_status = 'zcu' > GROUP BY Zylinder.Z_durch_soll > HAVING durchmesserdelta >= 0.085 > ORDER BY Zylinder_Typen.Z_durch_soll desc > > I get the following error in the pgadmin.log file. > > 19.12.00 10:53:34 Executing: SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, > Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) > AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM > Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( > Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = > Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL > AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta > >= 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc > 19.12.00 10:53:34 Executing SQL Query... > 19.12.00 10:53:34 Done - 0,01 Secs. > ** > * Error - 19.12.00 10:53:34 > ** > > Software > > Program: pgAdmin > Version: 7.0.4 > Sub or Function: frmSQL, cmdExecute_Click > > Error Details > * > Error No: -2147217887 > Error Description: Der ODBC-Treiber unterstützt die angeforderten > Eigenschaften nicht. > Error Source: Microsoft OLE DB Provider for ODBC Drivers > DLL Error Code: 0 > > Memory Details > ** > Total Physical: 132435968 > Total Swap: 434098176 > Total Virtual: 2147352576 > Available Physical: 34004992 > Available Swap: 291512320 > Available Virtual: 2079350784 > Percentage Free: 0 > > System Details > ** > Processor: 586 > OEM ID: 0 > No. Processors: 1 > Page Size: 4096 > > OS Details > ** > Platform: Windows NT > Version: 4.0 > Build: 1381 > OS Info: Service Pack 5 > > Environment Details > *** > Datasource: pgmondadori > Tracking: False > TrackVer: 0 > Connect: Provider=MSDASQL.1;Extended Properties > >="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD > > >**;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS > > " > Version: 2.6 > > > Using the same statement without the GROUP BY and HAVING it is ok ! > > SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, > ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, > (Zylinder.Z_durch_soll+0.12) AS effektiv > FROM Auftrag,Zylinder_Typen, Zylinder > WHERE Auftrag.A_nr = '11' > AND (Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') > AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll > ANDAuftrag.A_Ztyp=Zylinder.Z_typ > ANDZ_A_nr =NULL > ANDZ_status = 'zcu' > ORDER BY Zylinder_Typen.Z_durch_soll desc > > Whats wrong, according to the docs, the syntax is ok and it should be > possible to use these keywords! > > > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > phone: +4141 790 4040 > fax: +4141 790 2545 > mobile: +4179 211 0315 > >
[SQL] substring ..
hi folks.. i want to do this to a datetime field.. select foo from table where substr(datefoo,1,11) = '2000-12-14'; it returns no results yet.. select substr(datefoo,1,11) does return some values that say 2000-12-14 any clues ? Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
Re: [SQL] question on SELECT
use: \dt -- all tables \dv -- all views \df -- all functions ... Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On 19 Dec 2000, Prasanth A. Kumar wrote: > Howard Hiew <[EMAIL PROTECTED]> writes: > > > Hi, > > I would like to know what is the sql statement that list all the tables > > name. > > > > For example in Oracle, > > 'SELECT TABLE_NAME from ALL_TABLES where owner="Oracle" '; > > > > What is the statement for Postgres? > > Thank you > > > > Best Regards, > > Howard > > CIM/MASTEC > > Tel:(65)8605283 > > You can do '\dt' to list all tables. There is also a system table > 'pg_tables' which you can use if you like to do a select instead. Do > SELECT tablename FROM pg_tables where tableowner='postgres'; > > -- > Prasanth Kumar > [EMAIL PROTECTED] >
[SQL] group by: properly?
Hello,
I must confess I have always been a bit baffled by the GROUP BY,
therefore I would appreciate if you could tell me if there's a better way:
I have the table "items":
id | integer | not null default nextval('items_id_seq'::text)
seller_id| integer | not null
material | integer | not null
item_kind| integer |
finishing| integer |
amount | integer | not null
What I need is to SELECT the list of items that have the same values in
fields in between the marks.
The material, finishing and item_kind are references to the appropriate id
fields in tables material, finishing and item_kinds:
materials:
id SERIAL,
name_en text
and I would like to get the name of the material rather then the id from the
query.
So I wrote:
SELECT M.name_en AS material, P.name_en AS itemtype, F.name_en
AS finishing, amount FROM items I, item_kinds P, materials M, finish F
WHERE F.id=I.finishing AND M.id=I.material AND P.id=I.item_kind
This gives me the list of items with the names rather than id's already
prepared, the list could be something like:
material itemtype finishing amount
'birch' 'SCAFFOLD BOARDS' 'levelled' 5
'birch' 'SCAFFOLD BOARDS' 'levelled' 33
'birch' 'SCAFFOLD BOARDS' 'levelled' 4
'oak' 'SCAFFOLD BOARDS' '' 7
'oak' 'C.L.S.' '' 66
Now, I want this query to give me only the one value for the items that differ
only with the amount. so that i have:
'birch' 'SCAFFOLD BOARDS' 'levelled' 42
'oak' 'SCAFFOLD BOARDS' '' 7
'oak' 'C.L.S.' '' 66
The following GROUP BY accomplishes it:
SELECT M.name_en AS material, P.name_en AS itemtype, F.name_en
AS finishing, sum(amount) FROM items I, item_kinds P, materials M, finish
F WHERE F.id=I.finishing AND M.id=I.material AND P.id=I.item_kind
GROUP BY M.name_en,P.name_en,F.name_en;
The question is:
assuming I will have quite a lot of rows in the result, will the performance be
OK to GROUP BY the resulting text fields rather then by I.item_kind,
I.material indexed numeric fields?
Is it possible to rewrite the query so that GROUP BY (or any alternative
construct) deals with the numeric fields and yet I can receive the textual
output (that I need) via a single query?
Any comments appreciated,
Emils
RE: [SQL] substring ..
Hi Jeff, '2000-12-14' is only 10 chars long. You're asking for an 11-char long substring to match a 10-char ... not going to happen! You can see this better if you do something like this ... select '@' || substr(datefoo,1,11) || '@' from table; ... and you'll get results like: @2000-12-14 @ So, you could modify your query to do: select foo from table where substr(datefoo, 1, 10) = '2000-12-14'; Alternatively, what's wrong with this approach? select foo from table where date(datefoo) = '2000-12-14'; I think that might execute a little faster. Hope this helps Francis Solomon > hi folks.. > > i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; > > it returns no results yet.. > > select substr(datefoo,1,11) does return some values that say > 2000-12-14 > > any clues ? > > Jeff MacDonald,
Re: [SQL] substring ..
On Tue, 19 Dec 2000, Jeff MacDonald wrote: > hi folks.. > > i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; > > it returns no results yet.. > > select substr(datefoo,1,11) does return some values that say > 2000-12-14 > > any clues ? My guess is that it's a trailing space thing. '2000-12-14' is only 10 characters not 11... What you're actually getting is probably '2000-12-14 '
Re: [SQL] substring ..
Jeff, > i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; > > it returns no results yet.. > > select substr(datefoo,1,11) does return some values that say > 2000-12-14 Well, for one it's not a string, it's a datetime field. WHy are you trying to substring a datetime field, anyway? -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] substring ..
> i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; > > it returns no results yet.. > > select substr(datefoo,1,11) does return some values that say > 2000-12-14 Ummm... because '2000-12-14' is a ten-character, not eleven character long string. Try substr(datefoo,1,10) and it works for me (under 7.1devel). However, this all seems sloppy. Why not extract the date, and compare it as a date? -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [SQL] substring ..
Jeff MacDonald wrote: > > hi folks.. > > i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; > > it returns no results yet.. > > select substr(datefoo,1,11) does return some values that say > 2000-12-14 > > any clues ? > > Jeff MacDonald, > > - > PostgreSQL Inc | Hub.Org Networking Services > [EMAIL PROTECTED] | [EMAIL PROTECTED] > www.pgsql.com | www.hub.org > 1-902-542-0713 | 1-902-542-3657 > - > Facsimile : 1 902 542 5386 > IRC Nick : bignose > PGP Public Key : http://bignose.hub.org/public.txt try remove the "-" from your WHERE clausule... This signal appears only in SELECT results... -- == AKACIA TECNOLOGIA Desenvolvimento de sistemas para Internet www.akacia.com.br
Re: [SQL] substring ..
Hehe, here is my tests with this: ctntest2=# SELECT create_date FROM users; create_date 2000-08-29 13:01:53-04 2000-08-27 20:04:41-04 2000-08-27 21:24:28-04 2000-08-30 09:51:16-04 2000-07-25 23:14:08-04 2000-07-25 23:14:08-04 2000-09-01 02:53:02-04 2000-07-25 23:14:08-04 (8 rows) ctntest2=# SELECT substr(create_date,1,10) FROM users; substr 2000-08-29 2000-08-27 2000-08-27 2000-08-30 2000-07-25 2000-07-25 2000-09-01 2000-07-25 (8 rows) ctntest2=# SELECT create_date FROM users WHERE substr(create_date,1,7) = '2000-08'; create_date 2000-08-29 13:01:53-04 2000-08-27 20:04:41-04 2000-08-27 21:24:28-04 2000-08-30 09:51:16-04 (4 rows) Seems to work fine. My fields are TIMESTAMP. If you really still have trouble, then try a cast: substr(datefoo::TEXT,1,10) ?? On Tuesday 19 December 2000 14:06, Jeff MacDonald wrote: > hi folks.. > > i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; > > it returns no results yet.. > > select substr(datefoo,1,11) does return some values that say > 2000-12-14 > > any clues ? > > Jeff MacDonald, > > - > PostgreSQL Inc| Hub.Org Networking Services > [EMAIL PROTECTED]| [EMAIL PROTECTED] > www.pgsql.com | www.hub.org > 1-902-542-0713| 1-902-542-3657 > - > Facsimile : 1 902 542 5386 > IRC Nick : bignose > PGP Public Key : http://bignose.hub.org/public.txt -- Robert B. Easter [EMAIL PROTECTED] - - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - -- http://www.comptechnews.com/~reaster/
Re: [SQL] substring ..
On Tue, 19 Dec 2000, Jeff MacDonald wrote: > hi folks.. > > i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; And why not to_char()? Karel
