9
Trying to run an update statement like this on a table, using PostgreSQL 9.2:
UPDATE table
SET a_col = array[col];
We need to be able to run this on a ~10M row table, and not have it lock up the table (so normal operations can still happen while the update is running). I believe using a cursor will probably be the right solution, but I really have no idea if it is or how I should implement it using a cursor.
I have come up with this cursor code, which I think might be good.
Edit: Added cursor function
CREATE OR REPLACE FUNCTION update_fields() RETURNS VOID AS $$
DECLARE
cursor CURSOR FOR SELECT * FROM table ORDER BY id FOR UPDATE;
BEGIN
FOR row IN cursor LOOP
UPDATE table SET
a_col = array[col],
a_col2= array[col2]
WHERE CURRENT OF cursor;
END LOOP;
END;
$$ LANGUAGE plpgsql;