Re: [SQL] function expression in FROM may not refer to other relations of same query level
I found one solution: select id, usr, code, (get_lines(code)).line1, (get_lines(code)).line2 from tbl; The problem now is that get_lines is being called twice per line. (I check with a RAISE NOTICE). Philippe -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Philippe Lang Envoyé : mercredi, 11. août 2004 08:41 À : [EMAIL PROTECTED] Objet : Re: [SQL] function expression in FROM may not refer to other relations of same query level Hello, > Whats wrong with just using CASE: > > select id, usr, code, > case when code = 1 then 'A' else 'Z' end as line1, > case when code = 1 then 'A' else 'Z' end as line2 from tbl; The code I showed in my last mail was actually test code only. The logic is more complicated, and I'm not sure it's possible to implement it with a SELECT... CASE. What's more, the "get_lines" function already exists, and is already used in different parts of the database. I would like, if possible, to use it without changing it, or duplicating code. Philippe ---(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]
[SQL] Inserts with incomplete rows... NOT NULL constraints
Am trying to migrate some old data to a new database schema. I have dumped the old data as SQL statements. The new datastructure just contains some added fields to a few tables. My plan was to just create a new database with the new structure, dump the content only as SQL insert statements. And just run the statements contained in the dump on the new database. The problem am now facing is that postgres will try to insert a NULL value for fields not specified in the insert query and that are defined as NOT NULL in the table structure. Is this the correct behaviour? I would expect NULL fields not specified in the insert to get NULL inserted automatically. But that fields which are NOT NULL in the table structure gets inserted a NULL value too seems odd. Am loosing my mind in this heat but, you should be able to insert just half the fields of a table record if the datastructure would allow it? Wouldnt you? I feel like a such a noob :( Please advise.. -- L.E.Thorsplass ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Inserts with incomplete rows... NOT NULL constraints
Am Mittwoch, 11. August 2004 14:35 schrieb Lars Erik Thorsplass: > I would expect NULL fields not specified in the insert to get NULL > inserted automatically. But that fields which are NOT NULL in the > table structure gets inserted a NULL value too seems odd. More accurately, the default value is inserted, which in turn is null if you didn't specify one. You might find it odd that default values that are inconsistent with constraints are allowed, but I don't see any reasonable alternative. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Inserts with incomplete rows... NOT NULL constraints
Lars Erik Thorsplass wrote: The problem am now facing is that postgres will try to insert a NULL value for fields not specified in the insert query and that are defined as NOT NULL in the table structure. Is this the correct behaviour? Actually, what it's doing is inserting the DEFAULT value for the field in question. If you don't specify a DEFAULT, it assumes null. # CREATE TABLE test_tbl (a integer, b text DEFAULT 'bbb', c text); # INSERT INTO test_tbl (a) VALUES (1); # SELECT * FROM test_tbl; a | b | c ---+-+--- 1 | bbb | If you specify NOT NULL and don't want to provide a value you'll need to set a DEFAULT. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Displaying two tables side by side
How can you display two tables side by side? Example: > select * from t1; a | b ---+--- 2 | 2 3 | 5 4 | 7 9 | 0 > select * from t2; c | d ---+--- 4 | 5 7 | 3 3 | 2 1 | 1 2 | 0 Intended output: a | b | c | d ---+---+---+--- 2 | 2 | 4 | 5 3 | 5 | 7 | 3 4 | 7 | 3 | 2 9 | 0 | 1 | 1 | | 2 | 0 Each table has no keys (and no OIDs). Order is not important, but each row from each table needs to be displayed exactly once. -- dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] function expression in FROM may not refer to other relations of same query level
Philippe Lang wrote: > The problem now is that get_lines is being called twice per line. Is get_lines() defined as IMMUTABLE? Should it be? /rls -- :wq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Displaying two tables side by side
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! David Garamond wrote: > How can you display two tables side by side? Example: > >> select * from t1; > a | b > ---+--- > 2 | 2 > 3 | 5 > 4 | 7 > 9 | 0 > >> select * from t2; > c | d > ---+--- > 4 | 5 > 7 | 3 > 3 | 2 > 1 | 1 > 2 | 0 > > Intended output: > a | b | c | d > ---+---+---+--- > 2 | 2 | 4 | 5 > 3 | 5 | 7 | 3 > 4 | 7 | 3 | 2 > 9 | 0 | 1 | 1 >| | 2 | 0 > > Each table has no keys (and no OIDs). Order is not important, but each > row from each table needs to be displayed exactly once. > You could try to use PosgreSQL's ctid system column to join on like this: test=# select *,ctid from t1; a | b | ctid - ---+---+--- 2 | 2 | (0,1) 3 | 5 | (0,2) 4 | 7 | (0,3) 9 | 0 | (0,4) test=# select *,ctid from t2; c | d | ctid - ---+---+--- 4 | 5 | (0,1) 7 | 3 | (0,2) 3 | 2 | (0,3) 1 | 1 | (0,4) 2 | 0 | (0,5) test=# select * from t1 right outer join t2 on (t1.ctid=t2.ctid); a | b | c | d - ---+---+---+--- 2 | 2 | 4 | 5 3 | 5 | 7 | 3 4 | 7 | 3 | 2 9 | 0 | 1 | 1 | | 2 | 0 Note that this is of course very platform specific. On Oracle you could use rownum, for example. I don't have a more portable solution on hand right now. HTH - - andreas - -- Andreas Haumer | mailto:[EMAIL PROTECTED] *x Software + Systeme | http://www.xss.co.at/ Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0 A-1100 Vienna, Austria | Fax: +43-1-6060114-71 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFBGll0xJmyeGcXPhERApQ5AKCrOdLg4i6UpycLUGWxTLIpe68C6QCgk2UP gcXbeO6VEw95obz1D8GQFQk= =Ksq6 -END PGP SIGNATURE- ---(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] Displaying two tables side by side
select ( select a from t1 where CAST(t1.oid AS int) - CAST( (select min(oid) from t1) AS int ) = t_all.rownum ) AS a , ( select b from t1 where CAST(t1.oid AS int) - CAST( (select min(oid) from t1) AS int ) = t_all.rownum ) AS a , ( select c from t2 where CAST(t2.oid AS int) - CAST( (select min(oid) from t2) AS int ) = t_all.rownum ) AS a , ( select d from t2 where CAST(t2.oid AS int) - CAST( (select min(oid) from t2) AS int ) = t_all.rownum ) AS a from ( select cast(t1.oid AS int) - CAST( (select min(oid) from t1) AS int ) AS rownum UNION select cast(t2.oid AS int) - CAST( (select min(oid) from t2) AS int ) AS rownum ) AS t_all; a | a | a | a ---+---+---+--- 2 | 2 | 4 | 5 3 | 5 | 7 | 3 4 | 7 | 3 | 2 9 | 0 | 1 | 1 | | 2 | 0 David Garamond schrieb: How can you display two tables side by side? Example: > select * from t1; a | b ---+--- 2 | 2 3 | 5 4 | 7 9 | 0 > select * from t2; c | d ---+--- 4 | 5 7 | 3 3 | 2 1 | 1 2 | 0 Intended output: a | b | c | d ---+---+---+--- 2 | 2 | 4 | 5 3 | 5 | 7 | 3 4 | 7 | 3 | 2 9 | 0 | 1 | 1 | | 2 | 0 Each table has no keys (and no OIDs). Order is not important, but each row from each table needs to be displayed exactly once. -- dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Wierded error in recursive function; debugging ideas?
Joe, > There is no in_array() function in Postgres that I'm aware of -- you > sure that isn't array_in()? Yep. That's a cut-and-paste of the exact log message. > The rest of that error message doesn't seem > to be there in 7.4 either. Can we see the function? Sure: CREATE OR REPLACE FUNCTION "sf_event_decendants" (integer,integer) RETURNS text AS ' DECLARE v_event ALIAS for $1; v_user ALIAS for $2; child_list INT[]; sub_child TEXT; child_rec RECORD; p_status INT; contfrom INT; BEGIN child_list := ARRAY [ 0 ]; SELECT status INTO p_status FROM events WHERE event_id = v_event; IF p_status < 0 THEN p_status = -99; ELSE p_status = 0; END IF; FOR child_rec IN SELECT event_id FROM events WHERE parent_id = v_event AND status > p_status LOOP child_list := child_rec.event_id || child_list; IF v_user <> 0 THEN IF if_get_lock(v_user, ''events'', child_rec.event_id, NULL) <> ''OK'' THEN RETURN ''LOCKED: One or more of the child events of the current event are locked by '' || ''another user at this time. You cannot proceed.''; END IF; END IF; END LOOP; FOR child_rec IN SELECT event_id FROM events WHERE (continued_id = v_event or event_id = COALESCE(contfrom, 0)) and status > p_status LOOP child_list := child_rec.event_id || child_list; IF v_user <> 0 THEN IF if_get_lock(v_user, ''events'', child_rec.event_id, NULL) <> ''OK'' THEN RETURN ''LOCKED: One or more of the child events of the current event are locked by '' || ''another user at this time. You cannot proceed.''; END IF; END IF; END LOOP; IF child_list = ARRAY[0] THEN RETURN ''0''; END IF; FOR child_rec IN SELECT event_id FROM events WHERE status > p_status AND event_id = ANY ( child_list ) LOOP sub_child := sf_event_decendants(child_rec.event_id, v_user); IF sub_child <> ''0'' THEN child_list := child_list || string_to_array(sub_child, '','')::INT[]; IF v_user <> 0 THEN IF if_get_lock(v_user, ''events'', child_rec.event_id, NULL) <> ''OK'' THEN RETURN ''LOCKED: One or more of the child events of the current event are locked by '' || ''another user at this time. You cannot proceed.''; END IF; END IF; END IF; END LOOP; RETURN array_to_string(child_list, '',''); END;' LANGUAGE 'plpgsql'; -- -Josh Berkus "A developer of Very Little Brain" Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Displaying two tables side by side
Depending on the size of your structures, something like the below may be significantly faster than the subselect alternative, and more reliable than the ctid alternative. CREATE TYPE result_info AS (a integer, b integer, c integer, d integer); CREATE OR REPLACE FUNCTION parallelselect() RETURNS SETOF result_info AS ' DECLARE res result_info%rowtype; ct1_found boolean DEFAULT true; ct2_found boolean DEFAULT true; ct1 CURSOR FOR SELECT a,b FROM t1; ct2 CURSOR FOR SELECT c,d FROM t2; BEGIN OPEN ct1; OPEN ct2; LOOP FETCH ct1 INTO res.a, res.b; ct1_found := FOUND; FETCH ct2 INTO res.c, res.d; ct2_found := FOUND; IF ct1_found OR ct2_found THEN RETURN NEXT res; ELSE EXIT; END IF; END LOOP; RETURN; END; ' LANGUAGE plpgsql; SELECT * FROM parallelselect() AS tab; a | b | c | d ---+---+---+--- 2 | 2 | 4 | 5 3 | 5 | 7 | 3 4 | 7 | 3 | 2 9 | 0 | 1 | 1 | | 2 | 0 (5 rows) On Wed, 2004-08-11 at 10:11, David Garamond wrote: > How can you display two tables side by side? Example: > > > select * from t1; > a | b > ---+--- > 2 | 2 > 3 | 5 > 4 | 7 > 9 | 0 > > > select * from t2; > c | d > ---+--- > 4 | 5 > 7 | 3 > 3 | 2 > 1 | 1 > 2 | 0 > > Intended output: > a | b | c | d > ---+---+---+--- > 2 | 2 | 4 | 5 > 3 | 5 | 7 | 3 > 4 | 7 | 3 | 2 > 9 | 0 | 1 | 1 > | | 2 | 0 > > Each table has no keys (and no OIDs). Order is not important, but each > row from each table needs to be displayed exactly once. > > -- > dave > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Displaying two tables side by side
Andreas Haumer wrote: You could try to use PosgreSQL's ctid system column to join on like this: test=# select *,ctid from t1; a | b | ctid - ---+---+--- 2 | 2 | (0,1) 3 | 5 | (0,2) 4 | 7 | (0,3) 9 | 0 | (0,4) test=# select *,ctid from t2; c | d | ctid - ---+---+--- 4 | 5 | (0,1) 7 | 3 | (0,2) 3 | 2 | (0,3) 1 | 1 | (0,4) 2 | 0 | (0,5) test=# select * from t1 right outer join t2 on (t1.ctid=t2.ctid); a | b | c | d - ---+---+---+--- 2 | 2 | 4 | 5 3 | 5 | 7 | 3 4 | 7 | 3 | 2 9 | 0 | 1 | 1 | | 2 | 0 Note that this is of course very platform specific. On Oracle you could use rownum, for example. I don't have a more portable solution on hand right now. HTH Thanks, I just found out about ctid. I was thinking on a rownum equivalent too, actually. I guess a more portable solution would be creating a temporary table for each table to add the ctid/"row counter" equivalent, and then join on that. -- dave ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] reply to setting
On Sat, Aug 07, 2004 at 09:33:08 +0530, Kenneth Gonsalves <[EMAIL PROTECTED]> wrote: > hi, > any reason why the default reply-to on this list should not be set to the > list? I keep replying to postings only to find later that the reply goes to > the OP and not to the list. reply-all button results in needless duplication The duplication is needless. Direct replies very often get to the recipient faster than ones sent through the lists. It is also possible that the direct replies might be handled differently by the recipient (e.g. a filter may put them in different folders). Recipients that prefer not to get separate copies can indicate that desire by including an appropiate mail-followup-to header. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] sleep function
On Aug 10, 2004, at 10:57 AM, Bruce Momjian wrote: I can't think of one, no. I think you will have to use one of the server-side languages and call a sleep in there. This is no good in the real world since it pounds the CPU, but it worked well enough for my testing purposes. Best, John DeSoi, Ph.D. create or replace function sleep (integer) returns time as ' declare seconds alias for $1; later time; thetime time; begin thetime := timeofday()::timestamp; later := thetime + (seconds::text || '' seconds'')::interval; loop if thetime >= later then exit; else thetime := timeofday()::timestamp; end if; end loop; return later; end; ' language plpgsql; ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Wierd Error on update
Hi All, I am relative PostGres newbie, so if there is a simple answer to this question, please feel free to send me a link or explaination. I am running Postgres 7.3 on a intel Linux Redhat 7.3 base. I am getting two errors which are a bit confounding. ERROR: pg_class_aclcheck:relation 474653086 not found on one table within a large SQL file update (+5000 lines with documentation) that needs to be run from the command line. Both statements are multiline Update statements. This is straight SQL, not function code. Now I can guareentee that the queries refer to an existing table, but the table is being rebuilt in the core of the long SQL update. The table in question now has all permissions (my attempt to get fixed, but that did not help). Interesting aspect to this problem is that if your run the large update in one file, and then run the two update statements in a second file it works fine. Now I placed the erroring statements at the end of the large file to make sure it was not a ordering error, but that didnt help. Any ideas? Thank you Thomas Seeber ---(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
Fwd: [SQL] Inserts with incomplete rows... NOT NULL constraints
Sorry about that, forgot to send to the list. -- Forwarded message -- From: Lars Erik Thorsplass <[EMAIL PROTECTED]> Date: Wed, 11 Aug 2004 22:18:24 +0200 Subject: Re: [SQL] Inserts with incomplete rows... NOT NULL constraints To: Peter Eisentraut <[EMAIL PROTECTED]> > Am Mittwoch, 11. August 2004 14:35 schrieb Lars Erik Thorsplass: > > I would expect NULL fields not specified in the insert to get NULL > > inserted automatically. But that fields which are NOT NULL in the > > table structure gets inserted a NULL value too seems odd. > > More accurately, the default value is inserted, which in turn is null if you > didn't specify one. You might find it odd that default values that are > inconsistent with constraints are allowed, but I don't see any reasonable > alternative. Thanks for clearing that up. Guess I'll work around my migration problems with placeholder default values. I tried another approach at the same time. I tried to alter the tables that had changed between versions of the sql structure. The problem with this is that postgres is unable to add a field to a table with a NOT NULL constraint. You can add a NOT NULL constraint to the field afterwards but now all records in the table have a NULL value in the field. So you wont be able to do that until you change all the values. No default value was specified for the new field. As with the other approach this might also work if I enter some default values. Best regards, L.E.Thorsplass -- L.E.Thorsplass ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Displaying two tables side by side
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Andreas Haumer wrote: | test=# select * from t1 right outer join t2 on (t1.ctid=t2.ctid); "full outer join" is better in this case. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGn5f7UpzwH2SGd4RAjP2AKCZVDTMWX87VXI7SfpAyWsJ57NlygCg6Ki9 5kOVpxAY5KPkHxpwpWFdEcY= =O/Yc -END PGP SIGNATURE- ---(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] reply to setting
Bruno Wolff III wrote: On Sat, Aug 07, 2004 at 09:33:08 +0530, Kenneth Gonsalves <[EMAIL PROTECTED]> wrote: any reason why the default reply-to on this list should not be set to the list? I keep replying to postings only to find later that the reply goes to the OP and not to the list. reply-all button results in needless duplication The duplication is needless. Direct replies very often get to the recipient ^ is *not* needless? faster than ones sent through the lists. It is also possible that the direct replies might be handled differently by the recipient (e.g. a filter may put them in different folders). This is very true. In fact, I get mildly annoyed when people *don't* include the direct reply to me, because I very actively filter/redirect my mail. Replies directly to me are pretty much guaranteed to be seen quickly, but the ones that go to the list might get lost among the hundreds of posts that go into my "postgres" inbox every day. I think many other people do something similar. Recipients that prefer not to get separate copies can indicate that desire by including an appropiate mail-followup-to header. Also true. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] reply to setting
> > faster than ones sent through the lists. It is also possible that the direct > > replies might be handled differently by the recipient (e.g. a filter may put > > them in different folders). > > This is very true. In fact, I get mildly annoyed when people *don't* > include the direct reply to me, because I very actively filter/redirect If you don't want duplicates, send an email to [EMAIL PROTECTED] with the following command (or something similar to this anyway): set pgsql-hackers eliminatecc Whenever you're in the To: or Cc: headers, the list will not send you a copy of the message -- so you only receive it once. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Wierded error in recursive function; debugging ideas?
Josh Berkus wrote: [2] Wrong datatype for second argument in call to in_array SQL: SELECT sf_event_decendants(66645,111) Are you sure this message isn't coming from some PHP middleware, e.g. peardb or something. See: http://us2.php.net/manual/en/function.in-array.php Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Wierd Error on update
Thomas Seeber <[EMAIL PROTECTED]> writes: > I am running Postgres 7.3 on a intel Linux Redhat 7.3 base. Original 7.3 release, or (I hope) 7.3.something? > I am getting two errors which are a bit confounding. > ERROR: pg_class_aclcheck:relation 474653086 not found Are there any views involved? Is the statement invoking any functions? How about triggers? Foreign keys? Rules? Also, is it always the same number in the error, or does that change from run to run? The problem looks a bit like a corrupted rule or view, but I'm not entirely sure how one would get involved in a simple UPDATE. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Wierded error in recursive function; debugging ideas?
Joe, > Are you sure this message isn't coming from some PHP middleware, e.g. > peardb or something. See: > http://us2.php.net/manual/en/function.in-array.php Hm ... possible. Will check with my PHP guy. Would explain why I've not been able to track down the error. -- -Josh Berkus "A developer of Very Little Brain" Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] UPDATE FROM problem, multiple updates of same row don't seem to work
On Mon, Aug 09, 2004 at 15:16:29 -0500, David Stanaway <[EMAIL PROTECTED]> wrote: > Here is an example: > > CREATE TABLE tablea( > id int PRIMARY KEY, > flag int > ); > > CREATE TABLE tableb( > aid int REFERENCES tablea(id), > flag int > ); > > INSERT INTO tablea VALUES(1,0); > INSERT INTO tablea VALUES(2,0); > > -- Flags for 1st row of tablea - When ORed, should be 7 > INSERT INTO tableb VALUES(1,1); > INSERT INTO tableb VALUES(1,2); > INSERT INTO tableb VALUES(1,4); > > -- Flags for 2nd row of tablea - When ORed, should be 5 > INSERT INTO tableb VALUES(2,1); > INSERT INTO tableb VALUES(2,4); > > > UPDATE tablea > SET flag = tablea.flag | tableb.flag The original value of tablea.flag for each id will be used here. So that only one of the tableb.flag values will be or'd in for each id. > FROM tableb > WHERE tablea.id = tableb.aid; > > > SELECT * from tablea; > id | flag > +-- > 1 |1 > 2 |1 > > -- Desired output is > id | flag > +-- > 1 |7 > 2 |5 > > > Is there a way around this so that I can get the desired output? Write a custom aggregate function that does the or for you. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] reply to setting
On Wed, Aug 11, 2004 at 13:45:21 -0700, Joe Conway <[EMAIL PROTECTED]> wrote: > Bruno Wolff III wrote: > > > >The duplication is needless. Direct replies very often get to the recipient > ^ > is *not* needless? Yeah, I made a typo. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] UPDATE FROM problem, multiple updates of same row don't seem to work
On Wed, Aug 11, 2004 at 20:50:28 -0500, David Stanaway <[EMAIL PROTECTED]> wrote: > > I had thought about that, but this is a simpler case of what I need to > do. The operations for each column in the update are dependent on the > current and new values of each row being merged. > > Currently I am doing this with a cursor which is very slow! How about a > trigger on update? Or would this suffer the same behavior that I am > seeing with UPDATE FROM with a 1-many join? There is a good chance that triggers would be faster since tablea seems to reallly be a materialized view of an aggregate over tableb and doing a delta calculation when a row changes is going to be faster than recalculating the aggregate from scratch. You won't have the same problem if you use a trigger as when you were trying to calculate an aggregate using UPDATE. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
