CODEWITHSUNDEEP
sqlpostgresql
Lenny
Lenny

Reputation: 63

How to update one column of table with 8 million rows of data in PostgreSQL

I have a table with about 20 columns. It currently holds 8 million rows of data. I need to update one NULL column for all 8 million rows, it is a GUID column and a FK for another table column.

Currently, I am running simple update query in a loop. But it is taking way too long.

DO $$
BEGIN
   FOR counter IN 1..1000 LOOP

        UPDATE "Shops"
         SET "Country" = '410006e1-ca4e-4502-a9ec-e54d922d2c00'
         FROM (SELECT "Id"
                  FROM "Shops"
                   WHERE "Country" IS NULL LIMIT 1000) 
         AS "part"
         WHERE "Shops"."Id" = "part"."Id";

         COMMIT;
      RAISE NOTICE 'Counter: %', counter;

   END LOOP;
END; $$

Upvotes: 0

Views: 712

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Updating 8 million rows should not take 6 hours. But it can take a long time. It is often faster to just repopulate the table:

create table temp_shops as
    select . . . ,
           '410006e1-ca4e-4502-a9ec-e54d922d2c00' as country,
            . . . 
    from shops
    where country is null;

Once you have tested this to be sure it does what you want, you can truncate the table and insert the values:

truncate table shops;  -- be careful.  This empties the table but you have a copy!

insert into shops ( . . . )
    select *
    from temp_shops;

Upvotes: 1

Related Questions