Phoenix Kiula wrote:
On 16/08/07, Richard Broersma Jr <rabroersma@xxxxxxxxx> wrote:
--- Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote:
On 16/08/07, Rodrigo De León <rdeleonp@xxxxxxxxx> wrote:
On Aug 15, 11:46 pm, phoenix.ki...@xxxxxxxxx ("Phoenix Kiula") wrote:
Appreciate any tips, because it would
be nasty to have to do this with millions of UPDATE statements!
- Create an interim table
- COPY the data into it
- Do an UPDATE ... FROM ...
Thanks! I thought about it and then gave up because SQL trumped me up.
Could you please suggest what the query should look like?
Based on this:
http://www.postgresql.org/docs/8.1/static/sql-update.html
I tried this:
UPDATE
t1 SET title = title FROM t2
WHERE
t1.id = t2.id;
UPDATE T1
SET T1.title = T2.title
FROM T2
WHERE T1.id = T2.id
AND T1.title IS NULL;
or
UPDATE T1
SET title = ( SELECT title
FROM T2
WHERE T2.id = T1.id )
WHERE T1.title IS NULL;
Thanks much RIchard, but neither of those work. For me table t1 has
over 6 million rows, and table t2 has about 600,000. In both of the
queries above I suppose it is going through each and every row of
table t1 and taking its own sweet time. I've dropped all indexes on
t1, but the query has still been running for over 45 minutes as I
write! Any other suggestions?
I'm not sure would it be faster - but you can try to create a function
which will create new empty table, then fill it with the result of
SELECT query. Something like this:
CREATE OR REPLACE FUNCTION add_column () RETURNS INTEGER AS $$
DECLARE
r RECORD;
BEGIN
CREATE TABLE new_table (id integer, value varchar);
FOR r IN select t1.id, t2.title value from t1 left outer join t2 on
(t1.id = t2.id) LOOP
INSERT INTO new_table VALUES(r.id, r.title);
END LOOP;
return 0;
end
$$ LANGUAGE plpgsql;
Try this function and if its' time would be acceptable - you'll need to
drop existing table and rename newly created one.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
