Re: [SQL] Table transform query
> A take on a self-join: > > SELECT t1.serial, t1.date as dateL, MIN(t2.date) as dateR FROM t t1 > LEFT JOIN t t2 ON t1.serial = t2.serial AND t1.date < t2.date AND > t2.delivery = 'R' > WHERE t1.delivery = 'L' > GROUP BY t1.serial, t1.date > > Whether this is any clearer, or runs faster, than the correlated > subquery (which could be simplified by using MIN instead of LIMIT 1) > is up for debate and test, respectively. Hi Nis, Thanks for your tip with the "MIN" operator. I always imagined a self-join solution was faster than a query with a subselect. With a quick test, it seems to be the case here. CREATE TABLE foo ( serial integer, delivery character(1), date integer ); INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (2, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (3, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (1, 'R', 2); INSERT INTO foo (serial, delivery, date) VALUES (2, 'R', 2); INSERT INTO foo (serial, delivery, date) VALUES (4, 'L', 2); INSERT INTO foo (serial, delivery, date) VALUES (5, 'L', 3); INSERT INTO foo (serial, delivery, date) VALUES (3, 'R', 3); INSERT INTO foo (serial, delivery, date) VALUES (4, 'R', 3); INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 4); -- Subselect SELECT f.serial, f.date as dateL, ( SELECT MIN(f2.date) FROM foo AS f2 WHERE f2.serial = f.serial AND f2.date > f.date AND f2.delivery = 'R' ) AS dateR FROM foo AS f WHERE f.delivery = 'L' ORDER BY f.serial, f.date -- Self-join SELECT t1.serial, t1.date as dateL, MIN(t2.date) as dateR FROM foo t1 LEFT JOIN foo t2 ON t1.serial = t2.serial AND t1.date < t2.date AND t2.delivery = 'R' WHERE t1.delivery = 'L' GROUP BY t1.serial, t1.date ORDER BY t1.serial, t1.date ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Many databases
I'm in a strange situation where it makes more sense to give each user it's own database instead of having a single database with users and permissions. How does PG deal with that? Is it a problem to have have say a thousand small databases? S. ---(end of broadcast)--- TIP 1: 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] Many databases
Well I'm just toying with an idea. The problem I'm facing is that I would like clients to only see the tuples that they have created and own. I guess I'll just skip direct sql access to the db and write some API on top of it that manages the data. Not a big deal but it complicates things :-) S. On 9/22/07, Hiltibidal, Robert <[EMAIL PROTECTED]> wrote: > How is this possible? > > It smacks of a security issue which there are many proven solutions to > that. > > Would you elaborate? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Stefan Arentz > Sent: Friday, September 21, 2007 5:39 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Many databases > > I'm in a strange situation where it makes more sense to give each user > it's own database instead of having a single database with users and > permissions. How does PG deal with that? Is it a problem to have have > say a thousand small databases? > > S. > > ---(end of broadcast)--- > TIP 1: 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 > > > PRIVILEGED AND CONFIDENTIAL > This email transmission contains privileged and confidential information > intended only for the use of the individual or entity named above. If the > reader of the email is not the intended recipient or the employee or agent > responsible for delivering it to the intended recipient, you are hereby > notified that any use, dissemination or copying of this email transmission is > strictly prohibited by the sender. If you have received this transmission in > error, please delete the email and immediately notify the sender via the > email return address or mailto:[EMAIL PROTECTED] Thank you. > > > > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Many databases
--- Stefan Arentz <[EMAIL PROTECTED]> wrote: > Well I'm just toying with an idea. The problem I'm facing is that I > would like clients to only see the tuples that they have created and > own. I guess I'll just skip direct sql access to the db and write some > API on top of it that manages the data. Not a big deal but it > complicates things :-) The veil project already does this. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
