Re: Fastest way to import only ONE column into a table? (COPY doesn't work)

Phoenix Kiula wrote:
> On 16/08/07, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
>
>> --- Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
>>
>>
>>> On 16/08/07, Rodrigo De León <rdeleonp(at)gmail(dot)com> wrote:
>>>
>>>> On Aug 15, 11:46 pm, phoenix(dot)ki(dot)(dot)(dot)(at)gmail(dot)com ("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.