[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] JOIN condition confusion
Thomas Good wrote: Hi, I am wondering if there is a way to set conditions on a left joined table without hosing the join altogether: query = qq |SELECT p.*, a.user_id FROM patient_dosing p LEFT JOIN patient_assignment a ON p.patient_id = a.patient_id WHERE p.dose_type = 'Missed (AWOL)' AND (p.dose_date >= $start_date AND p.dose_date <= $end_date) -- the next two conditions hose the left join -- AND a.end_date IS NULL -- AND lower(a.assign_type) = 'primary' ORDER BY a.user_id, p.patient_id| if ($dbtype ne "oracle"); What do you want to happen? What do you mean by a left-join where the right-hand side has assign_type='primary'? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Changing location of ORDER BY has large effect on performance, but not results...
Jason Turner <[EMAIL PROTECTED]> writes: > My initial guess is that it is not able to utilize the index on > foo.tracktitle to sort the result set after foo has been joined with > other tables. Well, of course not. It should be able to do it before, though, and I'm a bit surprised that you didn't get the same plan from both cases seeing that the planner knows the first one is cheaper. Can you provide a complete self-contained test case? I'm not interested in trying to reverse-engineer your table definitions ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] serial in output
alessandra de gregorio <[EMAIL PROTECTED]> schrieb:
> Hi,
>
> What function should I use to get a serial number, together with my results,
> from a query?
>
> Ex. Of output I want:
>
> 1 ooo pp ij
> 2 hou joo iu
> 3 bhi ft yh
>
> Basically, I would like to have one column with integers, from 1 onwards, no
> matter how many tables I join in, or the data that I get back.
Create a sequence and then "select nextval('your_sequence'), * from foo;"
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] serial in output
In article <[EMAIL PROTECTED]>,
"alessandra de gregorio" <[EMAIL PROTECTED]> writes:
> Hi,
> What function should I use to get a serial number, together with my results,
> from a query?
> Ex. Of output I want:
> 1 ooo pp ij
> 2 hou joo iu
> 3 bhi ft yh
> Basically, I would like to have one column with integers, from 1 onwards, no
> matter how many tables I join in, or the data that I get back.
I'd do it in application code, but if you insist on doing it at the
SQL layer:
CREATE TEMPORARY SEQUENCE tmpseq;
SELECT nextval('tmpseq'), othercols... FROM ...
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Design question: Scalability and tens of thousands of tables?
On Nov 4, 2005, at 9:47 AM, [EMAIL PROTECTED] wrote: The problem is I am very concerned about scalability with having a different table created for each custom object. I want to design to site to handle tens of thousands of users. If each user has 3-5 custom objects the database would have to handle tens of thousands of tables. So it would appear that this is a broken solution and will not scale. Has anyone designed a similar system or have ideas to share? Well, before you discount it, did you try out your design? You could do initial segregation of user's tables into separate schemas (say, schema 'a' -> 'z' according to username or some better hashing routine like brute-force round-robin assignment at user creation time). Assignment of objects -> schema would be one additional column in your centralized user directory table(s). James Robinson Socialserve.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Design question: Scalability and tens of thousands of tables?
> On Nov 4, 2005, at 9:47 AM, [EMAIL PROTECTED] wrote: > >> The problem is I am very concerned about scalability with having a >> different >> table created for each custom object. I want to design to site to >> handle >> tens of thousands of users. If each user has 3-5 custom objects the >> database would have to handle tens of thousands of tables. >> >> So it would appear that this is a broken solution and will not >> scale. Has >> anyone designed a similar system or have ideas to share? > > Well, before you discount it, did you try out your design? You could > do initial segregation of user's tables into separate schemas (say, > schema 'a' -> 'z' according to username or some better hashing > routine like brute-force round-robin assignment at user creation > time). Assignment of objects -> schema would be one additional column > in your centralized user directory table(s). I don't imagine this would help much with the *true* problem, which is that a lot of queries inside the DB would have tens of thousands of tables to go thru rather than (say) hundreds. If you have 10,000 tables, that means 10,000 entries in pg_class. Associating them with 26 (or 260) namespaces does nothing to assist any queries that still have to scan through 10K pg_class entries. -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://linuxfinances.info/info/wp.html "Whenever you find that you are on the side of the majority, it is time to reform." -- Mark Twain ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] can not connect to pg on linux FC4
Hi, I use pg 8.03 (come with FC4). I can not connect to pg because of Ident authentication failed for user "root" error. pg_hba.conf host all all 127.0.0.1/32 ident sameuser Then I try another way by add adding a row to pg_indet so that I can use root account to connection to db. mymap root root and chage "sameuser" to "mymap" in the pg_hba.conf. But I can not connect too as the above error. Thank in advande someone help me. Onother question. Is there a way that I can create a user using password authetication at the situation I have no pg account like this sitation?. Thanks. Fucai __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
