[SQL] Simple Query HELP!!!
Hello, I have the following, A table call People with 3 fields AGE (Int) NAME (Txt) COMPANY (TxT) and I want to create a query than get me a list with the seniors per company, for example : table PEOPLE NAME AGE COMPANY Bob 33 Acme Jane30 Acme Bill20 Acme Jose56 ATech Siu 40 ATech Paolo 28 IBM Maria 38 IBM I need a query than will return a list with the seniors per company like Bob Jose Maria Is there a way to do this with one query? Please help, ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] A simple join question that may stump you
OK, I have 2 tables, table A: ID FLAG - - 1 1 2 1 2 2 3 1 3 2 3 3 and table B: FLAG - 1 2 I want to find all id's from table A that have every flag in table B but no extra flags. So, I'd end up with: ID - 2 As id 2 has both flag 1 and flag 2, id 1 doesn't have flag 2, and id 3 has flag 3. I know it can be done, 'cause I've done it in the past, but I've spent hours on this to no avail. Surfing the net proved fruitless as well. Any help would be greatly appreciated. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] HP-UX 11.0 postgres compile error!
Cannot compile Postgres .7.x with gcc 2.9.6 (OS: HP-UX 11.0). error messages: .. .. gmake[3]: Entering directory `/home/download/postgresql-7.0.2/src/backend/access/common' gcc -I../../../include -I../../../backend -D_REENTRANT -I/usr/local/Berkeley DB.3.2/include -I/usr/local/src/openldap-2.0.14/include -Ddef -O2 -Wall -Wm issing-prototypes -Wmissing-declarations -I../.. -D_REENTRANT -I/usr/local/B erkeleyDB.3.2/include -I/usr/local/src/openldap-2.0.14/include -Ddef -c -o indexvalid.o indexvalid.c In file included from ../../../include/nodes/print.h:17, from ../../../include/executor/execdebug.h:17, from indexvalid.c:19: ../../../include/nodes/parsenodes.h:103: parse error before `1' ../../../include/nodes/parsenodes.h:103: warning: no semicolon at end of struct or union ../../../include/nodes/parsenodes.h:105: parse error before `}' ../../../include/nodes/parsenodes.h:105: warning: type defaults to `int' in declaration of `AlterTableStmt' ../../../include/nodes/parsenodes.h:105: warning: data definition has no type or storage class gmake[3]: *** [indexvalid.o] Error 1 gmake[3]: Leaving directory `/home/download/postgresql-7.0.2/src/backend/access/common' gmake[2]: *** [submake] Error 2 gmake[2]: Leaving directory `/home/download/postgresql-7.0.2/src/backend/access' gmake[1]: *** [access.dir] Error 2 gmake[1]: Leaving directory `/home/download/postgresql-7.0.2/src/backend' gmake: *** [all] Error 2 I cannot solve this problem. please give me a solution.. thanks. ---(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] Subquery with IN or EXISTS
Carl van Tast <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>... > Hi A., > > On 26 Sep 2001 07:24:41 -0700, [EMAIL PROTECTED] (A. Mannisto) > wrote: > > >Hello, > > > >does anybody know why this: > >SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2) > > > >equals this: > >SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = > >col2) > > > >but this: > >SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2 WHERE > >col3='huu') > > > >equals _NOT_ this: > >SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = > >col2 AND col3='huu') > > > >E.g. resultset is not the same in last two statements. > >Can I get same set as IN statement somehow using EXISTS (performance > >issue)? > > I cannot reproduce your problem, results are equal here with > PostgreSQL 7.1.3. Could you post your CREATE TABLE and INSERT > statements? > > Re performance: There's more than one way to do it. (Where did I hear > this before? ;-)) You might try: > > SELECT tab.* FROM tab, tab2 WHERE tab.col1 = tab2.col2; > > or SELECT DISTINCT ... , if col2 is not unique in tab2. > > Kind regards, > Carl van Tast Sorry, I mixed up names of the columns of different tables. There was columns of the same name in both tables and my statement uses the wrong one (of course). Now it's OK! Thanks for advise! ---(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] getting some tech skills?
Larry Holt wrote: > It really depends upon what kind of work you like to do. > > For system admin, network engineering, etc. you need scripting like SED > AWK, PERL plus O/S commands. To be a programmer you need a language that > can be compiled: C, Java. A programmer usually does not need to parse a > file table and make changes while a network engineer usually does not > need to push values on a stack. To decide which tool (languages really > are tools) you need you need to decide what job you want to do. > > Generally those who can program business applications will earn the most > money but I prefer playing with hardware instead of typing at a > workstation all day. > It looks like I'll be getting up to speed with the above mentioned scripting langurages. Thanks Larry Andy H -- "Linux, the best thing since Unix itself." ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Subquery with IN or EXISTS
Hello, does anybody know why this: SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2) equals this: SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2) but this: SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2 WHERE col3='huu') equals _NOT_ this: SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2 AND col3='huu') E.g. resultset is not the same in last two statements. Can I get same set as IN statement somehow using EXISTS (performance issue)? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Request for book reviews/comments
Josh, I noticed that you had a Joe Selko book in your list so I thought it would only be fair to put in a C.J. Date book as well. Perhaps: A Guide to the Sql Standard : A User's Guide to the Standard Database Language Sql by Hugh Darwen (Contributor), Chris J. Date Paperback - 572 pages 1 edition (April 1997) Addison-Wesley Pub Co; ISBN: 0201964260 The '93 edition the one I've used often, but I would imagine that the above is the "updated" version. --Rick Josh Berkus wrote: > > Folks, > > Below are several "how-to" book reviews I intend to put up at TechDocs > (with Justin's OK). The idea is to provide a list of reference books to > reccomend to begineers and other developers, with the caveat that all > books must be directly related to PostgreSQL in some way and the reviews > will be tailored accordingly. I will start with sections on Postgres, > Database Design/Administration and SQL, and I can also picture sections > on JDBC books, Programming Open Source Projects, etc. > > See if you agree with these reviews, and add your own: > > Postgres Books: > > PostgreSQL, Introduction and Concepts (by Bruce Momjian, Addison-Wesley, > ISBN: 0201703319) The original beginner's manual to PostgreSQL by one > of the core developers, Momjian's book contains a spectrum of > introductory material on how to get up and running with PostgreSQL while > leaving more advanced topics for later books. It includes an > introduction to SQL 92 in general as well as Postgres extensions and the > basics of Postgres command-line administration. As such, this is > probably the perfect book for the user who is brand new to Postgres and > inexperienced in SQL. > > REVIEW WANTED: PostgreSQL by Jeff Perkins > > REVIEW WANTED: PostgreSQL Programmer's Guide by our own Thomas Lockhart. > > SQL Books: > > SQL in a Nutshell (Kline, Kline & Kline, O'Reilly, ISBN: 1565927443) > This slim volume is a useful reference for any experienced SQL developer > wanting to keep track of the dialectical differences between Postgres, > MySQL, MS SQL Server, and Oracle or looking to port a SQL application > between platforms. It also has an informative overview of the several > SQL standards (92, 99 and SQL 3). As it does not contain any tutorial > material and few examples, it is not useful to the beginner. Also the > book should not be used as a primary SQL reference dictionary due to > weak indexing and several notable omissions. > > REVIEW WANTED: SQL Queries for Mere Mortals, by Michael Hernandez et > al. > > REVIEW WANTED: SQL for Smarties, by Joe Celko (plus other Joe Celko > books) > > Database Design and Administration Books: > > Practical Issues in Database Management (Fabian Pascal, Addison-Wesley, > ISBN: 0201485559) This book covers in-depth a number of issues that are > all too often neglected for the self-educated DBA, including: relational > design, normalization, primary keys, and deviations from the SQL > standard. All is explained in a completely implementation-agnostic way, > with many diagrams and examples. A must for anyone required to create > any complex database application from scratch. > > REVIEW WANTED: Database Design for Mere Mortals, by Michael Hernandez. > > -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 > > >Name: >Type: Plain Text (text/plain) >Encoding: base64 > >Name: >Type: Plain Text (text/plain) >Encoding: base64 > >Name: >Type: Plain Text (text/plain) >Encoding: base64 > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Simple Query HELP!!!
On 22 Sep 2001 19:18:10 -0700, [EMAIL PROTECTED] (Paolo Colonnello) wrote: >Hello, I have the following, A table call People with 3 fields AGE >(Int) NAME (Txt) COMPANY (TxT) and I want to create a query than get >me a list with the seniors per company, for example : > >table PEOPLE > >NAME AGE COMPANY >Bob 33 Acme >Jane30 Acme >Bill20 Acme >Jose56 ATech >Siu 40 ATech >Paolo 28 IBM >Maria 38 IBM > >I need a query than will return a list with the seniors per company >like > >Bob >Jose >Maria > >Is there a way to do this with one query? > Do you care about ties? What if Ingrid, 38 yrs old, worked at IBM? Would you want to show both Ingrid and Maria? If so, this will work: Select Name From People t1 Inner Join (Select Company, Max(Age) As Oldest FROM People Group By Company) t2 ON t1.Company = t2.Company AND t1.Age = t2.Oldest HTH, Bob Barrows Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Selecting latest value II
Hi, Thurstan On Thu, 20 Sep 2001 17:30:46 +0100, "Thurstan R. McDougle" <[EMAIL PROTECTED]> wrote: > [...] >Carl van Tast had 2 good methods as follows > >SELECT userid, val >FROM tbl >WHERE NOT EXISTS (SELECT * FROM tbl AS t2 > WHERE tbl.userid=t2.userid AND t2.ts > tbl.ts); > >or > >SELECT tbl.userid, tbl.val >FROM tbl > INNER JOIN > (SELECT userid, max(ts) AS maxts > FROM tbl > GROUP BY userid) AS t2 > ON (tbl.userid=t2.userid AND tbl.ts=t2.maxts); ... although I like Tom Lane's even better. This one should outperform all others, especially my first one: > SELECT DISTINCT ON (userid) userid, val, ts FROM table > ORDER BY userid, ts DESC; That's the reason I'm here: learning by helping :-) Kind regards Carl van Tast ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Simple Query HELP!!!
[EMAIL PROTECTED] (Bob Barrows) wrote in message news:<[EMAIL PROTECTED]>... > Do you care about ties? What if Ingrid, 38 yrs old, worked at IBM? > Would you want to show both Ingrid and Maria? If so, this will work: > > Select Name From People t1 Inner Join > (Select Company, Max(Age) As Oldest FROM People > Group By Company) t2 > ON t1.Company = t2.Company AND t1.Age = t2.Oldest > Thanks a lot, the query work perfect! I dont care about ties... this is just the query I need... Bye ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] How to get BLOB length?
Is it any getlargeobject(oid) analog? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] A simple join question that may stump you
This is one way that comes up: select id from ( select distinct a.id AS id , b.flag AS flag from A, B where a.flag = b.flag ) a_distinct where id not in (select id from a where flag not in (select flag from b)) group by id having count(*) = (select count(*) from b) ; Arian. On 25 Sep 2001 20:01:06 -0700, [EMAIL PROTECTED] (Ross Smith) wrote: >OK, I have 2 tables, table A: > > ID FLAG >- - >1 1 >2 1 >2 2 >3 1 >3 2 >3 3 > >and table B: > > FLAG >- >1 >2 > >I want to find all id's from table A that have every flag in table B >but no extra flags. So, I'd end up with: > > ID >- >2 > >As id 2 has both flag 1 and flag 2, id 1 doesn't have flag 2, and id 3 >has flag 3. > >I know it can be done, 'cause I've done it in the past, but I've spent >hours on this to no avail. Surfing the net proved fruitless as well. > >Any help would be greatly appreciated. Arian Prins / Rock Resort --U-N-L-E-A-S-H-E-D-- (keyboards/production/songwriting) listen at: http://www.mp3.com/RockResort ---(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] PL/PGSQL Regexe
Humm... 7.0.2, I'll upgrade and try again. Thanks! Tom Lane wrote: [EMAIL PROTECTED]">rdear <[EMAIL PROTECTED]> writes: I'm doing a check within a PL/PGSQL function using a regular expression and I get the error: ERROR: regcomp failed with error invalid character range This appears to be because of the underscore "_" character. The regex I want to use is ''[A-Za-z0-9_]'' but this doesn't work. Seems to work for me:regression=# select 'a' ~ '[A-Za-z0-9_]'; ?column?-- t(1 row)regression=# select '_' ~ '[A-Za-z0-9_]'; ?column?-- t(1 row)regression=# select ':' ~ '[A-Za-z0-9_]'; ?column?-- f(1 row)How old is your Postgres? (I can tell by the spelling of the errormessage that it's not current.) regards, tom lane---(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] A simple join question that may stump you
Ross, >ID FLAG > - - > 1 1 > 2 1 > 2 2 > 3 1 > 3 2 > 3 3 > > and table B: > > FLAG > - > 1 > 2 > > I want to find all id's from table A that have every flag in table B > but no extra flags. So, I'd end up with: > >ID > - > 2 Try looking at the INTERSECT and EXCEPT join types. Thus, in pseudo-sql you'd need: SELECT A.ID WHERE Count A JOIN B = Count B AND Count A EXCEPT B = 0 Assuming that all rows in A are unique. -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://archives.postgresql.org
[SQL] PARSER ERROR persists ....
Hi,
I have tried Carl's suggestion, but it does
not make a difference and the same
ERROR: parser: parse error at or near "("
results.
I have rebuilt and reinstalled Version 7.1.3,
but still the same. No compilation or installation
errors are reported and simple statements seem
to work properly. In contrast to 7.0.3, the \d
command does not work because 'format_type(oid,int4)'
does not exist, but this
probably has nothing to do with my problem?
I am running SuSE Linux 7.2 (kernel 2.4.4).
Might any libraries not be loaded or could there
be any other problem related to installation?
Thanks, Frederick
Here is Josh's statement with Carl's modification:
SELECT people.people_id, people.name,
people.address,
people_attributes.attribute_name,
people_attributes.attribute_value
FROM people, people_attributes,
( SELECT people_id, count(*) as match_count
FROM people_attributes, search_attributes
WHERE search_id = 31
AND people_attributes.attribute_name =
search_attributes.attribute_name
AND people_attributes.attribute_value ~*
search_attributes.attribute_value
GROUP BY people_id) matches,
( SELECT count(*) as attribute_count
FROM search_attributes
WHERE search_id = 31 ) searched
WHERE people.people_id = people_attributes.people_id
AND people.people_id = matches.people_id
AND matches.match_count = searched.attribute_count;
__
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone.
http://phone.yahoo.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] A bug in triggers PG 7.1.3 or misunderstand ?
I think you need "return old", not "return new", in the body of the trigger if you want the delete to take place. new would be NULL in a delete situation ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] LEFT OUTER JOIN problem
Ludek, > I have this problem (maybe only in my head ;o)): Yup. Or it's a language problem. There's a fair Czech community of PgSQL users, so hopefully you can get in touch with some of them. (Your English is better than any of my 2nd languages -- it's just that techincal docs are hard enough to understand in one's native language!) > SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = > table2.table1_id) > LEFT OUTER JOIN table3 ON (table2.id = table3.table2_id); > > This select return me only one row: > 1,'blabla',1,1,'hello',NULL,NULL,NULL > > But I think it may return two rows: > 1,'blabla',1,1,'hello',NULL,NULL,NULL > 2,'arrrgh',NULL,NULL,NULL,NULL,NULL,NULL Yes, it will. The query you want is: SELECT * FROM table1 JOIN table2 ON (table1.id = table2.table1_id) LEFT OUTER JOIN table3 ON (table2.id = table3.table2_id); -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://archives.postgresql.org
Re: [SQL] LEFT OUTER JOIN problem
On Fri, 21 Sep 2001, Ludek Finstrle wrote: > Hello, > > I have this problem (maybe only in my head ;o)): > > table1: > --- > id | name > - > 1 | 'blabla' > 2 | 'arrrgh' > > table2: > --- > id | table1_id | name > - > 1 | 1 | 'hello' > > table3: > --- > id | table2_id | name > - > > SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = table2.table1_id) > LEFT OUTER JOIN table3 ON (table2.id = table3.table2_id); > > This select return me only one row: > 1,'blabla',1,1,'hello',NULL,NULL,NULL > > But I think it may return two rows: > 1,'blabla',1,1,'hello',NULL,NULL,NULL > 2,'arrrgh',NULL,NULL,NULL,NULL,NULL,NULL > > Where is the problem (in my head or in postgres)? Do you know what select > statement return me what I want? What version are you using? On 7.2 devel I get the two row result. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to get BLOB size?
How to get BLOB size? ---(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] Simple Query HELP!!!
Please write DDL and not narrative. here is my guess at what you are trying to do. What you posted was not a table because you had no key. TEXT is not the datatype to use for names -- unless they are thousand of characters long!! Recording age as an integer is useless -- give us the birthday and we can always compute their age. Is this what you meant to post? CREATE TABLE People (name CHAR(30) NOT NULL PRIMARY KEY, -- not big enough for TEXT age INTEGER NOT NULL, -- should be birthdate instead company CHAR(30) NOT NULL); >> ... create a query than get me a list with the seniors per company, for example :<< SELECT P1.name, P1.age, P1.company FROM People AS P1 WHERE NOT EXISTS (SELECT * FROM People AS P2 WHERE P1.company = P2.company AND P1.age < P2.age); This says there is nobody older than the P1 person in the same company. ---(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] A bug in triggers PG 7.1.3 or misunderstand ?
I have this database and it was working with PG 7.1.2 !!!
When I try to delete a record the trigger is fired but the delete is
not executed.
---code start here
drop database test_trig;
create database test_trig;
\connect test_trig
--
-- TOC Entry ID 2 (OID 81843)
--
-- Name: client_pages_id_seq Type: SEQUENCE Owner: mingo
--
CREATE SEQUENCE "client_pages_id_seq" start 1 increment 1 maxvalue
2147483647 minvalue 1 cache 1 ;
--
-- TOC Entry ID 34 (OID 81862)
--
-- Name: client_pages Type: TABLE Owner: mingo
--
CREATE TABLE "client_pages" (
"id" integer DEFAULT nextval('"client_pages_id_seq"'::text) NOT NULL,
"client_id" integer NOT NULL,
"name" character varying(250) NOT NULL,
"lang_id" integer,
"content" text,
Constraint "client_pages_pkey" Primary Key ("id")
);
--
-- TOC Entry ID 28 (OID 82749)
--
-- Name: client_pages_trash_id_seq Type: SEQUENCE Owner: mingo
--
CREATE SEQUENCE "client_pages_trash_id_seq" start 1 increment 1
maxvalue 2147483647 minvalue 1 cache 1 ;
--
-- TOC Entry ID 66 (OID 82768)
--
-- Name: client_pages_trash Type: TABLE Owner: mingo
--
CREATE TABLE "client_pages_trash" (
"id" integer DEFAULT nextval('"client_pages_trash_id_seq"'::text) NOT
NULL,
"client_id" integer NOT NULL,
"page_id" integer,
"page_name" character varying(250),
"date_posted" timestamp with time zone DEFAULT now(),
"content" text,
Constraint "client_pages_trash_pkey" Primary Key ("id")
);
--
-- TOC Entry ID 85 (OID 82803)
--
-- Name: "client_pages_upd_del_tr" () Type: FUNCTION Owner: mingo
--
CREATE FUNCTION "client_pages_upd_del_tr" () RETURNS opaque AS '
begin
insert into client_pages_trash(client_id,page_id,page_name,content)
values(old.client_id,old.id,old.name,old.content);
return new;
end;' LANGUAGE 'plpgsql';
--
-- TOC Entry ID 112 (OID 93427)
--
-- Name: client_pages_tr Type: TRIGGER Owner: mingo
--
CREATE TRIGGER "client_pages_tr" BEFORE DELETE OR UPDATE ON
"client_pages" FOR EACH ROW EXECUTE PROCEDURE
"client_pages_upd_del_tr" ();
insert into client_pages(client_id,name,lang_id,content)
values(1,'car',1,'hello');
select * from client_pages;
select * from client_pages_trash;
update client_pages set content = 'updated' where id = 1;
select * from client_pages;
select * from client_pages_trash;
delete from client_pages where id = 1;
select * from client_pages;
select * from client_pages_trash;
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] how can i return multiple values from a function
i have tried retrieving multiple values using setof function but i
couldnt solve it.when i am trying using setof iam getting this as
output.
1 CREATE FUNCTION hobbies (varchar) RETURNS SETOF bank
2 AS 'SELECT * FROM bank
3 '
4 LANGUAGE 'sql';
~
output:
select hobbies('srinivas') as col;
col
---
137462096
137462096
(2 rows)
please let me know what is the alternative and if possible with a sample
piece of code.
cheers
chowdary.
---(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] simple question!
hi. I have a simple question! is there a command intersect? I mean exist a union command, but I don't know if exist a intersect command. thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Aggregate Aggravation
I have a query using the SUM() function that is not returning the appropriate results. There are 3 queries with results below. Query 1 is the type of query I'd like to use, that has the SUM() function and a join with a customer table (bolcustomer) to constrain the results to a particular customer. The "sum" field ends up being 78 for this particular car. Query 2 is the same query without the customer table (bolcustomer) qualifier. This qualifier may seem superfluous in these test queries, but in real life the customer constraint is necessary because the other ones (car number and arrival date) are not present. I had to add them to get a snapshot suitable for testing and for this post. As you can see, Query 2 returns the proper sum of 64. Query 3 and accompanying results illustrate why 64 is correct. Furthermore, there were about 100 cars involved in the "real life" application of this query, and only 2 cars produced the "inappropriate" results (including the car in this example - BCOL730563) ... so 98% of the time it appeared to work. That's why I was wondering if the SUM() function had something to do with the problem. My question is somewhat open-ended: does anyone see anything fundamentally wrong with Query 1? I'd really like to use it or it's functional equivalent if possible. What is it about adding "inventorydebit" to the "FROM" clause, "inventorydebit.bol_number = bolcustomer.bol_number" to the "WHERE" clause, and "bolcustomer.customer_id" to the "GROUP BY" clause that causes the query to return the sum of 78 instead of 64? Thank you for reading my post. Best, -Robin -- Query 1 -- SELECT inventorydebit.car_number, inventorydebit.arrival_date, SUM(inventorydebit.debit_amount) FROM inventorydebit, bolcustomer WHERE inventorydebit.bol_number = bolcustomer.bol_number AND inventorydebit.arrival_date = '25-Jun-2001' AND inventorydebit.car_number = 'BCOL730563' AND bolcustomer.customer_id = '105' GROUP BY inventorydebit.arrival_date, inventorydebit.car_number; car_number |arrival_date|sum ++--- BCOL730563 | 06-25-2001| 78 (1 row) -- Query 2 -- SELECT inventorydebit.car_number, inventorydebit.arrival_date, SUM(inventorydebit.debit_amount) FROM inventorydebit WHERE inventorydebit.car_number = 'BCOL730563' AND inventorydebit.arrival_date = '25-Jun-2001' GROUP BY inventorydebit.arrival_date, inventorydebit.car_number; car_number |arrival_date|sum ++--- BCOL730563 | 06-25-2001| 64 (1 row) -- Query 3 -- SELECT bol_number, debit_amount FROM inventorydebit WHERE car_number = 'BCOL730563' AND arrival_date = '25-Jun-2001'; bol_number|debit_amount --+ 88190| 2 88503| 16 88514| 16 88595| 16 88738| 14 (5 rows) TABLES INVOLVED pcireload=> \d inventory Table= inventory +--+-+---+ | Field | Type | Length| +--+-+---+ | car_number | char() not null |12 | | arrival_date | date not null | 4 | | line_item_seq| int2 not null | 2 | | line_item_qty| int2| 2 | | line_item_desc | char() |50 | | pieces_per_unit | int2| 2 | | material_location| char() |30 | | inventory_user | char() |12 | +--+-+---+ Index:inventory_pk pcireload=> \d inventorydebit Table= inventorydebit +--+-+---+ | Field | Type | Length| +--+-+---+ | bol_number | int4 not null | 4 | | car_number | char() not null |12 | | arrival_date | date not null | 4 | | line_item_seq| int2 not null | 2 | | debit_amount | int2| 2 | +--+-+---+ Index:inventorydebit_pk pcireload=> \d bolcustomer Table= bolcustomer +--+-+---+ | Field | Type | Length| +--+--
Re: [SQL] HP-UX 11.0 postgres compile error!
Why are you using PG 7.0.2? 7.1.3 is the current release. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] is it possible to get the number of rows of a table?
> > I would like to compare the number of rows > of one table and of another and use it in > a query like this: > SELECT * FROM > WHERE "number of rows of table " > EQUALS >"number of rows of table " > i.e. I only want get a query result if the tables > have the same number of rows. > Is there a function or a way to do this ? > > Thanks, > Frederick > Basically, the way to find out about the number of rows of a table is "select count(*) from t1" But the select statement above looks odd to me (no offence intended). To receive a selection of certain rows of a table you have to reference at least one column of that table in the where clause like in "select * from table1 t1 where t1.c1 = (select count(*) from t2)" I hope this helps at least a bit. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] TEXT in select
Hi,
does anybody know a way, how to implement the content of a field of type
TEXT
into a query?
if i am trying:
select * from testtable WHERE testTEXT LIKE '%testString%'
i am getting always an empty selection, although there is a record existing.
The same with
select * from testtable WHERE position ('testString' in testTEXT) >
0;
Additional, is there a way to use the UPPER()-function to the content of the
TEXT-field?
Thanks in advance,
Michael
---(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] Problem in connection using .odbc.ini
Hi, I have a problem connecting to the database using .odbc.ini file. The error is: SQLState = 28000 errorMessage:[Sybase][ODBC Driver][Adaptive Server Anywhere]Invalid user authorization specification:Password must be atleast ??? characters The program executes properly if the .odbc.ini file is in the HOME dir of the user. Why is the ODBCINI variable not being recognised? I have checked that it points to the appropraite location. Can anybody help? Thanx, Meghana _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] LEFT OUTER JOIN problem
Hello, I have this problem (maybe only in my head ;o)): table1: --- id | name - 1 | 'blabla' 2 | 'arrrgh' table2: --- id | table1_id | name - 1 | 1 | 'hello' table3: --- id | table2_id | name - SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id = table2.table1_id) LEFT OUTER JOIN table3 ON (table2.id = table3.table2_id); This select return me only one row: 1,'blabla',1,1,'hello',NULL,NULL,NULL But I think it may return two rows: 1,'blabla',1,1,'hello',NULL,NULL,NULL 2,'arrrgh',NULL,NULL,NULL,NULL,NULL,NULL Where is the problem (in my head or in postgres)? Do you know what select statement return me what I want? Please reply to my address ([EMAIL PROTECTED]) as I'm not in list. Thanks Luf ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PARSER ERROR persists ....
Frederick Klauschen <[EMAIL PROTECTED]> writes: > I have rebuilt and reinstalled Version 7.1.3, > but still the same. No compilation or installation > errors are reported and simple statements seem > to work properly. In contrast to 7.0.3, the \d > command does not work because 'format_type(oid,int4)' > does not exist, but this > probably has nothing to do with my problem? Actually, that's a red flag indicating that you did the wrong thing. You may be running a 7.1.3 psql, but the server you are talking to is not 7.1.*, but something older (try "select version()" to prove it). regards, tom lane ---(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] Aggregate Aggravation
"Robin's PG-SQL List" <[EMAIL PROTECTED]> writes: > I have a query using the SUM() function that is not returning the > appropriate results. I'm guessing that you have two rows in bolcustomer matching bol_number = 88738, so that the 14 gets added in twice. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] format timestamp
Hi all, Is there a way to specify the default format for a timsetamp field? Specifically, what I am trying to do is to use COPY to get the data out of a table to be loaded into another database. I would like the timestamp fields accuracy to be maintained (to at least 3 places for milliseconds). Or even more specific, I want the COPY to be local to the client so I use stdout and redirect it to a file. In order to keep the acuracy on the timestamp fields, I need to COPY BINARY. This can not be used in conjunction with stdout. Any suggestions? -- Laurette Cisneros (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com Passenger Information Everywhere ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
