Re: [SQL] Outer Joins
Marc, > >This would work, but it would be *much* slower than a UNION query. "Not > >In" queries are perhaps the slowest you can run; see the earlier thread > >"Query Problem" for a discussion. UNION queries are, in fact, very fast > >... just awkward to code and manipulate. > > Why should this be slower since the UNION Query still has an identical not in clause? > This is far easier (for me) to read. Good point. Frankly, if you have a relevant large population of data (>10,000 rows) to test, I'd love to see comparative execution tests between the two query structures. Fortunately, this will all soon become moot; Tom says that outer joins have been stable in the 7.1 build for a while. Speaking of which, when's the 7.1 "release"? Huh, huh? -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Outer Joins
Marc, Tom, > Good point. Frankly, if you have a relevant large population of data > (>10,000 rows) to test, I'd love to see comparative execution tests > between the two query structures. > > Fortunately, this will all soon become moot; Tom says that outer joins > have been stable in the 7.1 build for a while. Speaking of which, > when's the 7.1 "release"? Huh, huh? On second thought, couldn't we use some kind of EXCLUDES clause to expedite this? Tom? Further, it occurs to me that as in my query, you don't want to use "NOT IN" on *either* version. Instead, use "NOT EXISTS", which is much, much faster. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: Re(2): Re(2): [SQL] Large Object dump ?
"pgsql-sql" <[EMAIL PROTECTED]> writes: > Exported 1131 large objects. > NOTICE: LockReleaseAll: xid loop detected, giving up Pre-7.0 LockReleaseAll() contained an entirely arbitrary assumption that it should never encounter a situation where there were more than 1000 locks held by one transaction :-(. So it chokes when you access more than 1000 LOs in the same transaction. Dunno about your other issue, but clearly your application is failing to report whatever error message was returned when the transaction was aborted... regards, tom lane
[SQL] PL/PGSQL beginning is hard....
hello, i am a beginner at SQL and PL/pgsql and thus have some surely already known problems... i have set up some tables, and wanted to play around with inbuild functions, and set up the following function: CREATE FUNCTION balance (int4) RETURNS int4 AS ' DECLARE compte ALIAS FOR $1; actplus accounts.num%TYPE; actminus accounts.num%TYPE; actres accounts.num%TYPE; BEGIN SELECT SUM(amount) INTO actplus FROM journal WHERE plus=compte; select sum(amount) INTO actminus from journal where minus=compte; actres := actplus - actminus; RETURN actres; END; ' LANGUAGE 'plpgsql'; Now this works fine, until it hits one of the cases where either of the selects returns an empty result (meaning that no line conforming to the contraint could be found) in this case even if the other select returns a value, the whole function does return nothing what did i wrong, or what do i have to change, to assume the value 0 if no hit was found to the select? BTW i am wondering if the same thing could have been achieved with sole SQL, and if yes, how -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing
Re: [SQL] PL/PGSQL beginning is hard....
> i am a beginner at SQL and PL/pgsql and thus have some surely > already known problems... > > i have set up some tables, and wanted to play around with inbuild > functions, and set up the following function: > > CREATE FUNCTION balance (int4) RETURNS int4 AS ' > DECLARE >compte ALIAS FOR $1; >actplus accounts.num%TYPE; >actminus accounts.num%TYPE; >actres accounts.num%TYPE; >BEGIN >SELECT SUM(amount) INTO actplus FROM journal WHERE plus=compte; >select sum(amount) INTO actminus from journal where minus=compte; >actres := actplus - actminus; >RETURN actres; > END; > ' LANGUAGE 'plpgsql'; > > > Now this works fine, until it hits one of the cases where either of the > selects returns an empty result (meaning that no line conforming to the > contraint could be found) in this case even if the other select returns > a value, the whole function does return nothing > > what did i wrong, or what do i have to change, to assume the value 0 if > no hit was found to the select? Probably this would do it: select coalesce(sum(amount),0) ... > BTW i am wondering if the same thing could have been achieved with sole > SQL, and if yes, how You might be able to do this with subselects.. (select coalesce(sum(amount), 0) from ... ) - (select coalesce...) So, maybe something like this, if you were say going over a table which had the compte values: select (select coalesce(sum(amount), 0) from journal where plus=compte) -(select coalesce(sum(amount), 0) from journal where minus=compte) from table_with_compte_values;
[SQL] Help! Storing trees in Postgres
Hi! I just subscribed myself to this list and sent a message but I am not sure that it reached the list. So, I am asking one more time. :-) Is there any way to implement a query in Postgres that would be in some way analogues to Oracle's START WITH/CONNECT BY structure? I'd very much appreciate any suggestions. Thanks in advance, Sergei
Re: [SQL] Re: [GENERAL] Problem with coalesce..
Stephan Szabo <[EMAIL PROTECTED]> writes: > There are still some contexts in which subqueries in expressions are > wierd. The example below appears to work in current sources however. >> SELECT COALESCE ((SELECT CURVALUE FROM TABLEKEYS WHERE TABLENAME = 'BUYER'),1) >> returns a "UNKNOWN expression type 501" AFAICT it works in 7.0.* as well. Are you sure this was 7.0.2, and not 6.5.something? regards, tom lane
