KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
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;
Tags (comma-separated)
Save Edits
Cancel