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@yahoo.com> wrote:
>
>> --- Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>>
>>
>>> On 16/08/07, Rodrigo De León <rdeleonp@gmail.com> wrote:
>>>
>>>> On Aug 15, 11:46 pm, phoenix.ki...@gmail.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.