[SQL]
im new in postgresql (actually came from SQL Server) and i was trying a script like this insert into table1(field1,field2) values (select field1, field2 from table 2); i dont know if this is possible (inserting a set of entries via resultset from a select stmt in one command). If anyone has any answers, or workarounds pls do email me Thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] duplicated oid
i'm using V6.5. >From my understanding, the oid should be unique in all rows of database. However, I'm getting duplicated data on one particular table, including the oid is the same! I don't recall trying to restore nor import any data. Any idea why? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
> im new in postgresql (actually came from SQL Server) and i was trying a > script like this > > insert into table1(field1,field2) values (select field1, field2 from table > 2); > > i dont know if this is possible (inserting a set of entries via resultset > from a select stmt in one command). If anyone has any answers, or > workarounds pls do email me Well, that syntax doesn't work on SQL Server either. I think what you want is: insert into table1(field1,field2) select field1, field2 from table2; HTH, -- Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] GRANT ALL ON TO GROUP failure
Can anyone suggest, what is wrong with the following sql file? SOmehow the semicolon causes error? Jari class=# \i pg-def-group-grant.sql Granting ALL to ROOT CHANGE Granting ALL to USER psql:pg-def-group-grant.sql:48: ERROR: parser: parse error at or near "user" Granting SELECT to READER psql:pg-def-group-grant.sql:69: ERROR: non-existent group "reader" 01:-- Postgres create table rights 02:-- $Id: pg-def-group-grant.sql,v 1.1 2001/08/23 19:26:16 jaalto Exp $ 03:-- 04:-- 05:-- GRANT allows the creator of an object to give specific permissions to 06:-- all users (PUBLIC) or to a certain user or group. Users other than the 07:-- creator don't have any access permission unless the creator GRANTs 08:-- permissions, after the object is created. 09: 10:\echo Granting ALL to ROOT 11: 12:GRANT ALL ON 13: bonus 14: , custid 15: , customer 16: , dept 17: , dual 18: , dummy 19: , emp 20: , item 21: , ordid 22: , ordx 23: , price 24: , prodid 25: , product 26: , sales 27: , salgrade 28: TO GROUP root; 29: 30:\echo Granting ALL to USER 31: 32:GRANT ALL ON 33: bonus 34: , custid 35: , customer 36: , dept 37: , dual 38: , dummy 39: , emp 40: , item 41: , ordid 42: , ordx 43: , price 44: , prodid 45: , product 46: , sales 47: , salgrade 48: TO GROUP user; 49: 50: 51:\echo Granting SELECT to READER 52: 53:GRANT SELECT ON 54: bonus 55: , custid 56: , customer 57: , dept 58: , dual 59: , dummy 60: , emp 61: , item 62: , ordid 63: , ordx 64: , price 65: , prodid 66: , product 67: , sales 68: , salgrade 69: TO GROUP reader; 70: 71:-- End of file -- -- http://tiny-tools.sourceforge.net/ Swatch @time http://www.ryanthiessen.com/swatch/resources.htm Convert @time http://www.mir.com.my/iTime/itime.htm ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] More on the TO DO wishlist
I did some pl/pgsql this morning and forgot a ';' at the end of a line. The result was, that the compiler complained about a wrong statement in line 304 - which is at the end of the program. The other error I made was that I used a new record without defining the record first. This, the program only detected, when it first tried to use the new record (select into xxx * from yyy...). Can the parser be changed to be a little more intelligent about it's error reporting, and can it be changed to check if all variables, records, etc. have been defined before the program runs? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL]
Joseph Syjuco writes: > im new in postgresql (actually came from SQL Server) and i was trying a > script like this > > insert into table1(field1,field2) values (select field1, field2 from table > 2); The correct syntax is: INSERT INTO table1 (field1, field2) SELECT field1, field2 FROM table2; -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL]
Try: Create table table1(field1,field2) as (select field1, field2 from table2); -Original Message- From: Joseph Syjuco [SMTP:[EMAIL PROTECTED]] Sent: Monday, September 03, 2001 3:03 AM To: [EMAIL PROTECTED] Subject: im new in postgresql (actually came from SQL Server) and i was trying a script like this insert into table1(field1,field2) values (select field1, field2 from table 2); i dont know if this is possible (inserting a set of entries via resultset from a select stmt in one command). If anyone has any answers, or workarounds pls do email me Thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(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]
On Mon, 3 Sep 2001, Joseph Syjuco wrote: > im new in postgresql (actually came from SQL Server) and i was trying a > script like this > > insert into table1(field1,field2) values (select field1, field2 from table > 2); > > i dont know if this is possible (inserting a set of entries via resultset > from a select stmt in one command). If anyone has any answers, or > workarounds pls do email me You can, but you don't need to use values. insert into table1(field1, field2) select field1, field2 from table2; should do what you want. ---(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] INSERT Syntax
Joseph, > insert into table1(field1,field2) values (select field1, field2 from > table > 2); Actually, that won't work in SQL Server either. The correct syntax (for both databases) is: INSERT INTO table1 ( field1, field2 ) SELECT field1, field2 FROM table2; The "VALUES" syntax is only appropriate if you are inserting a set of constants with no SELECT statement involved. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] More on the TO DO wishlist
Chris, > Can the parser be changed to be a little more intelligent about it's > error > reporting, This is on everybody's "todo" list. According to Bruce and Jan at LWE, though, better error reporting is not an easy fix. > and can it be changed to check if all variables, records, > etc. > have been defined before the program runs? Actually, I think this would be a role better served by an IDE. I've long thought that it would be teriffic if someone wrote a PL/whatever IDE (covering PL/pgSQL, PL/sh, PLtcl and PLperl). However, I can neither fund nor write it myself, so that'll remain a "wish list" item until maybe Red Hat sees money in it. Speaking of which, does anyone know if RH has a seperate "wish list" for what us developer-types would like to see our of RHDB? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] More on the TO DO wishlist
Josh Berkus writes: > Actually, I think this would be a role better served by an IDE. I've > long thought that it would be teriffic if someone wrote a PL/whatever > IDE (covering PL/pgSQL, PL/sh, PLtcl and PLperl). Given that Emacs has editing modes of varying sophistication for most/all of these languages, and XEmacs has a built-in PostgreSQL client module, one could write a minor mode for PG procedural languages that quote-escape the buffer and load it into the server. You heard it here first! -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] On Differing Optimizer Choices ( Again)
Mark kirkwood <[EMAIL PROTECTED]> writes (heavily edited): > SELECT > ... > WHERE d0.d0key = f.d0key > AND f.d0key BETWEEN 270 AND 350 > So far this is all as one would expect. However suppose we substitute > 'd0.d0key' in the 'AND' clause instead of 'f.d0key' to obtain : > SELECT > ... > WHERE d0.d0key = f.d0key > AND d0.d0key BETWEEN 270 AND 350 > [ produces a different plan because of differing row-count estimates ] This surprises me not at all. While the planner has some rudimentary grasp of the notion that equality is transitive, that grasp does not extend as far as recognizing that the above queries are really equivalent. You'd probably get a better plan if you wrote out the entire WHERE condition that you are thinking is intuitively obvious: SELECT ... WHERE d0.d0key = f.d0key AND d0.d0key BETWEEN 270 AND 350 AND f0.d0key BETWEEN 270 AND 350 so that the planner could see that there is a range restriction on each of the tables. While it'd be possible to teach the planner to deduce the third clause from the first two, I'm unconvinced that adding such logic would be a good idea. It would slow down all queries (probably by quite a bit) for a benefit that I suspect arises relatively seldom. Might be worth looking at this sometime in the future, but... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Index Scan Backward vs. Sort/Sequential Scan when using ORDER BY
Keith Bussey <[EMAIL PROTECTED]> writes: > In trying to figure out just why my ORDER BY queries were so slow, I came > across something interesting. The issue here seems to be that Postgres is drastically underestimating the number of rows that will come out of the indexscan in the second case: > -> Index Scan using index_client_profiles_gender on > client_profiles p (cost=0.00..35064.98 rows=198 width=8) 198 rows out when you have 54713 females seems a tad low; if it is indeed much too low, that would explain why the planner mistakenly prefers this plan. It'd be interesting to look at the EXPLAIN estimate and actual results for SELECT count(*) FROM client_profiles p WHERE (p.profiles_gender='F'); SELECT count(*) FROM client_profiles p WHERE (p.profiles_gender='F') AND (p.profiles_orientation[2] = 'F' OR p.profiles_orientation[1]='M'); I suspect the main problem may be lack of stats about the array element distributions. Does profiles_orientation really need to be an array, or could you break it out into separate fields? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] More on the TO DO wishlist
Peter, > > Given that Emacs has editing modes of varying sophistication for > most/all > of these languages, and XEmacs has a built-in PostgreSQL client > module, > one could write a minor mode for PG procedural languages that > quote-escape > the buffer and load it into the server. You heard it here first! > Does this mean I'll be forced to learn Emacs? -Josh (Who uses Kedit, Pico, and Joe) __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 2 tables, joins and same name...
Ross, > I haven't seen _any_ of these books for over a year, and know a lot > more > SQL than I did then, so take any recommendations with a grain of > salt. Hmmm... both of these books get good reviews. Is there anywhere (say, techdocs) where we could add a book list? I'll ask ... -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Need help in composing PostgreSQL query
Hi
Please help me compose the query in PostgreSQL.
Using PostgreSQL 7.1.2.
Suppose relations A and B have columns:
{X1, X2, ..., Xm, Y1, Y2, ..., Yn}
and
{Y1, Y2, ..., Yn}
Attributes Y1, Y2, ..., Yn are common for both relations
and have the same type in both.
How can I define in PostgreSQL the query producing
relation with columns X1,X2,...,Xm containing all those tuples
satisfying conditon: relation A contains tupple
{x1,x2,...xm,y1,y2,...,yn}
for _each_ tupple
{y1,y2,...,yn}
in relation B ? Where x1 denotes particular value of
colum X1 etc.
For example: consider two tables DEND and DOR.
DEND DOR
s | p p
+
s1 | p1 p1
s1 | p2 p2
s1 | p3 p3
s1 | p4 p4
s1 | p5 p5
s1 | p6 p5
s2 | p1 (6 rows)
s2 | p2
s3 | p2
s4 | p2
s4 | p4
s4 | p5
(12 rows)
For such tables our desired query should return:
s
s1
Thanks in advance.
--
Vladimir Zolotych [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
