Re: [SQL] Merging item codes using referential integrity
Andrus Moor wrote:
I have item table and a lot of child tables where the items are used.
I want to merge two item codes into single item in all tables.
It is not nice to write a lot of separate UPDATE statements for each table.
So I want to utilize REFERENCES clause for merging.
I tried the following code but got duplicate key error in UPDATE
statement.
Any idea how to impement this?
CREATE TABLE parent ( code CHAR(10) PRIMARY KEY );
BEGIN;
-- Direct Postgres to update all child tables. This causes error.
UPDATE parent SET code='1' WHERE code='2';
-- Remove duplicate row
That's the problem - you can't have a duplicate row at *any* time with a
primary key. The UNIQUE constraint is instant and can't be deferred (at
least, not yet).
However, in this case I would simply write a function:
CREATE FUNCTION merge_all(char(10), char(10) AS '
UPDATE table_1 SET col_1=$2 WHERE col1=$1;
UPDATE table_2 SET col_2=$2 WHERE col2=$2;
...etc...
' LANGUAGE SQL;
Then: SELECT merge_all('OLD_VAL','NEW_VAL') for each value (you could
even join to your "parent" table if all the values are in there). All
the updates in the function take place in the same transaction, so if
there are any problems then all changes will be rolled back.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] cost of CREATE VIEW ... AS SELECT DISTINCT
Hello Scott, Scott Marlowe wrote: On Mon, 2005-03-28 at 15:43, T E Schmitz wrote: How expensive would it be to maintain the following VIEW: CREATE VIEW origin AS SELECT DISTINCT origin FROM transaktion if there is in index on transaktion.origin; the table transaktion has thousands of records and there are only a few distinct origin? The cost will only be encurred when running the view. if you want materialized views (which WOULD have maintenance costs) you'll have to Thank you for the pointer - that might come in handy for another scenario. The cost of executing that view should be the same as the cost of running the query by hand. I did an EXPLAIN ANALYZE and a sequential scan was carried out despite the index I had on the column. Maybe this is because I only have very few records in my test DB. Would the "SELECT DISTINCT origin" always cause a sequential table scan regardless whether there is an index on the origin column or not? -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(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] cost of CREATE VIEW ... AS SELECT DISTINCT
On Mar 29, 2005, at 5:07 AM, T E Schmitz wrote: Hello Scott, Scott Marlowe wrote: On Mon, 2005-03-28 at 15:43, T E Schmitz wrote: How expensive would it be to maintain the following VIEW: CREATE VIEW origin AS SELECT DISTINCT origin FROM transaktion if there is in index on transaktion.origin; the table transaktion has thousands of records and there are only a few distinct origin? The cost will only be encurred when running the view. if you want materialized views (which WOULD have maintenance costs) you'll have to Thank you for the pointer - that might come in handy for another scenario. The cost of executing that view should be the same as the cost of running the query by hand. I did an EXPLAIN ANALYZE and a sequential scan was carried out despite the index I had on the column. Maybe this is because I only have very few records in my test DB. Would the "SELECT DISTINCT origin" always cause a sequential table scan regardless whether there is an index on the origin column or not? I think you are right. If this is expensive and run often, you could always normalize further and create a table of "unique_origin" that would have only unique origins and set transaktion to have a foreign key referring to the unique_origin table and then just query the unique_origin table when you need to do the query above. Sean ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] when using a bound cursor, error found...
I 'll use a bound cursor with parameters. But when I use such a cursor, I found a error. I don't know error message. How can I use a bound cursor. in following sample, near a 'for loop' , error found. -- CREATE OR REPLACE FUNCTION cursor_test( vp_param1 VARCHAR ,vp_param2 VARCHAR ) RETURNS VARCHAR AS $BODY$ DECLARE p_param1 VARCHAR; p_param1 VARCHAR; cur_test CURSOR (c_param VARCHAR) IS SELECT col_1, col_2, col_3 FROM tab_1 WHERE col_1 = c_param; rec_test RECORD; v_count NUMERIC; BEGIN p_param1 := nullif(vp_param1 , ''); p_param2 := nullif(vp_param2 , ''); IF p_param1 = 'txn' THEN - -- cursor - OPEN cur_test(p_param2); for rec_test in cur_test loop --FETCH cur_test INTO rec_test; SELECT count(*) INTO v_count FROM tab_2 WHERE col_1 = rec_test.col_1 and col_2 = rec_test.col_2 ; IF v_count >0 THEN insert into tab_2 ( col_1 , col_2 , col_3 ) values ( rec_test.col_1 , rec_test.col_2 , rec_test.col_3 ); END IF; end loop; -- cur_test CLOSE cur_test; END IF; RETURN 'txn OK'; END;$BODY$ LANGUAGE plpgsql;
Re: [SQL] cost of CREATE VIEW ... AS SELECT DISTINCT
On Tue, Mar 29, 2005 at 11:07:20 +0100, T E Schmitz <[EMAIL PROTECTED]> wrote: > > Would the "SELECT DISTINCT origin" always cause a sequential table scan > regardless whether there is an index on the origin column or not? It's worse than that, SELECT DISTINCT cannot use a hash aggregate plan and will need to do a sort to eliminate duplicates. Unless the view is used in a way that restricts the candidate rows, this probably isn't going to be very fast. You might be better off changing the view to use GROUP BY instead of DISTINCT. ---(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] cost of CREATE VIEW ... AS SELECT DISTINCT
Bruno Wolff III wrote: On Tue, Mar 29, 2005 at 11:07:20 +0100, T E Schmitz <[EMAIL PROTECTED]> wrote: Would the "SELECT DISTINCT origin" always cause a sequential table scan regardless whether there is an index on the origin column or not? It's worse than that, SELECT DISTINCT cannot use a hash aggregate plan and will need to do a sort to eliminate duplicates. Unless the view is used in a way that restricts the candidate rows, this probably isn't going to be very fast. You might be better off changing the view to use GROUP BY instead of DISTINCT. As far as I can see (via EXPLAIN), both DISTINCT and GROUP BY will lead to a sequentail scan. Is that correct? If that's the case, I should come up with a different concept to obtain a list of ORIGINs. -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(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] cost of CREATE VIEW ... AS SELECT DISTINCT
On Tue, Mar 29, 2005 at 02:21:15PM +0100, T E Schmitz wrote: > Bruno Wolff III wrote: > >On Tue, Mar 29, 2005 at 11:07:20 +0100, > > T E Schmitz <[EMAIL PROTECTED]> wrote: > > > >>Would the "SELECT DISTINCT origin" always cause a sequential table > >>scan regardless whether there is an index on the origin column or > >>not? > > > >It's worse than that, SELECT DISTINCT cannot use a hash aggregate > >plan and will need to do a sort to eliminate duplicates. Unless the > >view is used in a way that restricts the candidate rows, this > >probably isn't going to be very fast. You might be better off > >changing the view to use GROUP BY instead of DISTINCT. > > As far as I can see (via EXPLAIN), both DISTINCT and GROUP BY will > lead to a sequentail scan. Is that correct? That (GROUP BY using a seqscan) may be caused by the small size of the table. Try populating it some more. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "¿Qué importan los años? Lo que realmente importa es comprobar que a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] cost of CREATE VIEW ... AS SELECT DISTINCT
Alvaro Herrera wrote: On Tue, Mar 29, 2005 at 02:21:15PM +0100, T E Schmitz wrote: Bruno Wolff III wrote: On Tue, Mar 29, 2005 at 11:07:20 +0100, T E Schmitz <[EMAIL PROTECTED]> wrote: Would the "SELECT DISTINCT origin" always cause a sequential table scan regardless whether there is an index on the origin column or not? It's worse than that, SELECT DISTINCT cannot use a hash aggregate plan and will need to do a sort to eliminate duplicates. Unless the view is used in a way that restricts the candidate rows, this probably isn't going to be very fast. You might be better off changing the view to use GROUP BY instead of DISTINCT. As far as I can see (via EXPLAIN), both DISTINCT and GROUP BY will lead to a sequentail scan. Is that correct? That (GROUP BY using a seqscan) may be caused by the small size of the table. Try populating it some more. Shall do. But am I correct in assuming that I should place an index on the group by (TRANSAKTION.ORIGIN) column? -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] cost of CREATE VIEW ... AS SELECT DISTINCT
On Tue, Mar 29, 2005 at 14:21:15 +0100, T E Schmitz <[EMAIL PROTECTED]> wrote: > > As far as I can see (via EXPLAIN), both DISTINCT and GROUP BY will lead > to a sequentail scan. Is that correct? If you need to read the whole table yes. However if you join the view to something else that might not be necessary. > If that's the case, I should come up with a different concept to obtain > a list of ORIGINs. That may be a good idea, especially if there are lots of rows for each origin value. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] cost of CREATE VIEW ... AS SELECT DISTINCT
On Tue, Mar 29, 2005 at 15:12:24 +0100, T E Schmitz <[EMAIL PROTECTED]> wrote: > > Shall do. > But am I correct in assuming that I should place an index on the group > by (TRANSAKTION.ORIGIN) column? This will mainly help when joining the view to another table. This would also allow using an index scan instead of a sort in for DISTINCT which may be faster in some cases. ---(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] cost of CREATE VIEW ... AS SELECT DISTINCT
T E Schmitz <[EMAIL PROTECTED]> writes: > As far as I can see (via EXPLAIN), both DISTINCT and GROUP BY will lead > to a sequentail scan. Is that correct? I'm not sure why you expect something different. The query requires visiting every row of the table in any case --- else it might miss values that occur only once --- therefore a seqscan is most efficient. The planner will consider plans that involve an indexscan, but only as a substitute for doing an explicit sort before a uniq-style grouping step. Generally the explicit sort will win that comparison. And a hash grouping step probably dominates them both. regards, tom lane ---(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] cost of CREATE VIEW ... AS SELECT DISTINCT
Bruno Wolff III wrote: On Tue, Mar 29, 2005 at 14:21:15 +0100, T E Schmitz <[EMAIL PROTECTED]> wrote: If that's the case, I should come up with a different concept to obtain a list of ORIGINs. That may be a good idea, especially if there are lots of rows for each origin value. That's what I will do. Thank you for all your input. -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] when using a bound cursor, error found...
On Tue, Mar 29, 2005 at 08:49:49PM +0900, ?? wrote: > I 'll use a bound cursor with parameters. > But when I use such a cursor, I found a error. > I don't know error message. It's usually a good idea to post the error message. In most cases it should say what's wrong, or at least where something's wrong. > DECLARE > p_param1VARCHAR; > p_param1VARCHAR; You've declared the same variable twice; the second declaration should be p_param2. >OPEN cur_test(p_param2); > >for rec_test in cur_test loop I don't think you can iterate over a cursor this way. Rather than use an explicit cursor, why not use "FOR rec_test IN SELECT ..."? FOR loops automatically use cursors so you don't have to open one yourself. But if you want to use a cursor then you could do something like this: OPEN cur_test(p_param2); LOOP FETCH cur_test INTO rec_test; EXIT WHEN NOT FOUND; -- rest of code END LOOP; CLOSE cur_test; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
