James David Smith <james.david.smith@gmail.com> wrote:
> Given the data is so large I don't want to be taking the data out
> to a CSV or whatever and then loading it back in. I'd like to do
> this within the database using SQL. I thought I would be able to
> do this using a LOOP to be honest.
I would be amazed if you couldn't do this with a single UPDATE
statement. I've generally found declarative forms of such work to
be at least one order of magnitude faster than going to either a PL
or a script approach. I would start by putting together a SELECT
query using window functions and maybe a CTE or two to list all the
primary keys which need updating and the new values they should
have. Once that SELECT was looking good, I would put it in the
FROM clause of an UPDATE statement.
That should work, but if you are updating a large percentage of the
table, I would go one step further before running this against the
production tables. I would put a LIMIT on the above-mentioned
SELECT of something like 10000 rows, and script a loop that
alternates between the UPDATE and a VACUUM ANALYZE on the table.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company