Re: [SQL] How to enter lists into database.
Mr. Klauschen, > I try to find a way to enter a list of items into my > database , of which the size should be flexible. > I then would like perform queries on that list in > a way, that the whole list is retrieved, when the > query's items are found to be in that list. > Thanks for any help, Can you be more explicit about what you are attempting? Perhpas yyou could give specific examples from you actual data. -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] Request for book reviews/comments
Folks, Also, any reviews on foriegn-language SQL or database books (in the appropriate language) would be appreciated. -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 4: Don't 'kill -9' the postmaster
Re: [SQL] Bug?: Update on ancestor for a row of a child
Kevin, > After doing so, you'll find that postgres actually crashes when you > try > to insert a vote into the uservote table. That's the one that has me > looking at the costs involved with migrating to Oracle. And you think that Oracle is entirely free of bugs? ;-) At least here, you can get a message to the actual database developers. Still, I understand your frustration. -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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to enter lists into database:Example.
Frederick, > The problem is that I do not know what kind of > attributes the user wants to enter. So I do not > want to create a table with attributes like > "Hometown", "eye color" , etc. The user should just > enter what he likes to. This is actually a common SQL issue. If I knew a good SQL book in German (your 1st language, correct?) I would simply recommend it. As it is: While arrays may seem an attractive and simple answer to the issue of "how do I store an undefined list of characteristics" it is not the relationally correct answer, and will lead to database normalization proplems later on. Instead, you need to use what I call a "vertical characteristic table". As in your example Main Table: People CREATE TABLE PEOPLE ( people_id SERIAL name VARCHAR (60) address VARCHAR (200) ); data: 20 Mary Stuart 1600 Pensylvannia Ave. Vertical Table: people_attributes CREATE TABLE people_attributes ( attribute_id SERIAL people_id INT NOT NULL REFERENCES people(people_id) attribute_name VARCHAR (40) attribute_value VARCHAR (100) ); data: 47 20 "Hair" "Brown" 48 20 "Eyes" "Hazel" 49 20 "Hobby" "Breeding pedigree mice." Now, a crucial part of this scheme is defining an available list of attribute types. While not all "people" have to have all "attributes", without a pre-defined list searches will become impossible. PART II: Searching the attributes First, build two tables whose structure mirrors exactly "people" and "people-attributes" above: "searches" and "search_attributes". Second, Construct an interface so that a user can populate the search_attributes for any individual search, thus: "searches" 31 "mark" "mark's first search" "search_attributes" 22 31 "hair" "brown" 23 31 "hobby" "mice" Then use the following query: SELECT people.people_id, people.name, people.address, people_attributes.attribute_name, people_attributes.attribute_value FROM people, people_attributes WHERE people.people_id = people_attributes.people_id AND people.people_id IN ( SELECT people_id 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 ) (the ~* allows searches on partial value matches) This will give you these results: 20 Mary Stuart 1600 Pensylvannia Ave. HairBrown 20 Mary Stuart 1600 Pensylvannia Ave. EyesHazel 20 Mary Stuart 1600 Pensylvannia Ave. Hobby Breeding pedigree mice. It's up to you to format them in a pleasing way. -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] Bug?: Update on ancestor for a row of a child
> And you think that Oracle is entirely free of bugs? ;-) Yes, but they'd be exciting technology-oriented e-business enabled bugs! > Still, I understand your frustration. Thanks... It's just frustrating that the bug is on something so basic, which makes it both hard to code around and hard for me to delve into the postgres source and fix. I spent a few hours tracing source before finally conceding the point that it takes more than a few hours to understand postgres internals well enough to fix a major bug. For development purposes I've just removed all the CHECK constraints from my child tables, and I'm hoping some genius will solve the problem by the time I'm looking to deploy. -Kevin Way ---(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] How to enter lists into database.
He is probably thinking about arrays. contrib/array, contrib/intarray could be worth to see. Oleg On Mon, 24 Sep 2001, Josh Berkus wrote: > Mr. Klauschen, > > > I try to find a way to enter a list of items into my > > database , of which the size should be flexible. > > I then would like perform queries on that list in > > a way, that the whole list is retrieved, when the > > query's items are found to be in that list. > > Thanks for any help, > > Can you be more explicit about what you are attempting? Perhpas yyou > could give specific examples from you actual data. > > -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 > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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] confounding, incorrect constraint error
Kevin Way wrote: > I'm unemployed at the moment and this is a pet project, so I can't offer > much in the way of financial compensation, but I'll start the bidding at > $50 donation in your name to your choice of the EFF, the Red Cross, or the > American Cancer Society, in return for a fix. (If none of these charities > are acceptable, surely one can be found later that is acceptable to both > parties). Sorry, I missed the original post of the problem. If you can send it to me again and change your offer into donating blood at the Red Cross, I would take a look at it. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] CHECK problem really OK now...
Baldvin, > Probably the chances for a prize went... :-(( However, is there > still a shortage of blod??? Last I checked, the American Red Cross does not want your blood right now -- they want it two weeks from now. Currently blood stores are full, but they get depleted pretty fast. Of course, what they really want is for you to make a commitment to donate twice a year, every year. -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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] explain command
hi. I still go on looking for query time. I get a question! explain is a command that estimate cost of query time ok, but I don't know how is the time. for example: mybase=> explain select * from tabla1; NOTICE: QUERY PLAN: Seq Scan on tabla1 (cost=0.00..1.06 rows=6 width=4) EXPLAIN the cost query time is 1.06?1.06..seconds or milliseconds thanks friends! bye Esteban Gutierrez Abarzua Chillan- Chile ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Server crash caused by CHECK on child
> -- I don't think that I only found a minor bug compared to > -- the other you wrote in your last letter: the backend crash > -- is caused by the same CHECK constraint in the child table. Oooh, my bad. I should run your scripts before assuming I know how they fail. > -- However, for you without time to analyzing Kevin's huge > -- scheme, here is the very simplified, crash-causing script. Thank you so much for finding this simplified method of crashing Postgres. Hopefully somebody can find a fix now. > -- I am hunting it, but I have to learn all what this query-executing > -- about, so probably it takes uncomparable longer for me than for > -- a developer. That's my problem as well, though your example is vastly easier to trace than mine. -Kevin Way ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [SQL] outer joins strangeness
Alex Pilosov <[EMAIL PROTECTED]> writes: > I'm going to CC this to -hackers, maybe someone will shed a light on the > internals of this. It's not unintentional. See http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Server crash caused by CHECK on child
-- Hi Kevin, and everyone! -- -- I don't think that I only found a minor bug compared to -- the other you wrote in your last letter: the backend crash -- is caused by the same CHECK constraint in the child table. -- -- However, for you without time to analyzing Kevin's huge -- scheme, here is the very simplified, crash-causing script. -- drop table child; drop table ancestor; create table ancestor ( node_id int4, a int4 ); create table child ( b int4 NOT NULL DEFAULT 0 , c int4 not null default 3, CHECK ( child.b = 0 OR child.b = 1 ) ) inherits (ancestor); insert into ancestor values (3,4); insert into child (node_id, a, b) values (5,6,1); update ancestor set a=8 where node_id=5; - -- -- I am hunting it, but I have to learn all what this query-executing -- about, so probably it takes uncomparable longer for me than for -- a developer. -- -- Regards, -- Baldvin -- ---(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] CHECK problem really OK now...
Kovacs Baldvin wrote: > Hi everybody! > > I tried, and it works: the current CVS version really runs > happily the query what sent to heaven our 7.1 version of the > backend. > > Kevin: your original complex schema also runs smoothly. > > Thanks for our mindful developers! > > Regards, > Baldvin > > I think Jan wrote: > > Sorry, I missed the original post of the problem. If you can > > send it to me again and change your offer into donating blood > > at the Red Cross, I would take a look at it. > > Probably the chances for a prize went... :-(( However, is there > still a shortage of blod??? Well, for the NY disaster they probably have more than enough - not that many injured people there - sad enough though. But what's wrong with using the current wave of patriotism to get as much as they can get? It help's saving life! Using the victims for that purpose isn't abuse. It is turning grief, anger and sadness into help and hope. Let blood become "Open Source". Give it for free and you'll get plenty of it when you need some. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] outer joins strangeness
On Mon, 24 Sep 2001, Alex Pilosov wrote: > On Sun, 23 Sep 2001, Stephan Szabo wrote: > > > On Sun, 23 Sep 2001, Alex Pilosov wrote: > > > > Postgres treats join syntax as an explicit definition of what order to > > joins in. So, I'd guess it sees the first as: do the LOJ and then join > > that to the separate table. > Yeah, I figure that's how it sees it, but that's pretty stupid from > performance reasons :P) > > It _should_ realize that left outer join only constricts join order > between two tables in outer join, and joins to all other tables should > still be treated normally. (see below) > > I'm going to CC this to -hackers, maybe someone will shed a light on the > internals of this. > > > And for right outer join (for example), those two queries would not > > be equivalent if I read the ordering correctly. The former syntax > > would mean outer first and then the inner, whereas the second would > > be inner first then the outer, and that could have different results. > True. But this is not right outer join, its a left outer join...:) > > Postgres should understand that left outer join does not constrict join > order... But it can. If your condition was a joining between the other table and the right side of the left outer join, you'd have the same condition as a right outer join and the left side. The real condition I think is that you can join a non-explicitly joined table to the side of an outer join before the outer join but not to the other side. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [HACKERS] Server crash caused by CHECK on child
What version are you trying this script on? I'm not seeing a crash on my 7.2 devel system (and the update occurs). On Mon, 24 Sep 2001, Kovacs Baldvin wrote: > -- Hi Kevin, and everyone! > -- > -- I don't think that I only found a minor bug compared to > -- the other you wrote in your last letter: the backend crash > -- is caused by the same CHECK constraint in the child table. > -- > -- However, for you without time to analyzing Kevin's huge > -- scheme, here is the very simplified, crash-causing script. > -- > > > drop table child; > drop table ancestor; > > create table ancestor ( > node_id int4, > a int4 > ); > > create table child ( > b int4 NOT NULL DEFAULT 0 , > c int4 not null default 3, > CHECK ( child.b = 0 OR child.b = 1 ) > ) inherits (ancestor); > > insert into ancestor values (3,4); > insert into child (node_id, a, b) values (5,6,1); > > update ancestor set a=8 where node_id=5; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] CHECK problem really OK now...
Hi everybody! I tried, and it works: the current CVS version really runs happily the query what sent to heaven our 7.1 version of the backend. Kevin: your original complex schema also runs smoothly. Thanks for our mindful developers! Regards, Baldvin I think Jan wrote: > Sorry, I missed the original post of the problem. If you can > send it to me again and change your offer into donating blood > at the Red Cross, I would take a look at it. Probably the chances for a prize went... :-(( However, is there still a shortage of blod??? Baldvin ---(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] CHECK problem really OK now...
On Mon, Sep 24, 2001 at 03:23:13PM -0400, Jan Wieck wrote: > > It help's saving life! Using the victims for that purpose > isn't abuse. It is turning grief, anger and sadness into > help and hope. > > Let blood become "Open Source". Give it for free and you'll > get plenty of it when you need some. I couldn't agree more! -Roberto -- +| Roberto Mello - http://www.brasileiro.net |+ Computer Science, Utah State University -http://www.usu.edu USU Free Software & GNU/Linux Club -http://fslc.usu.edu Space Dynamics Lab, Developer-http://www.sdl.usu.edu OpenACS - Enterprise free web toolkit-http://openacs.org TAFB -> Text Above Fullquote Below ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
