CODEWITHSUNDEEP
sqlpostgresql
Nagaraj Vittal
Nagaraj Vittal

Reputation: 901

Postgres: How to set column default value as another column value while altering the table

I have a postgres table with millions of record in it. Now I want to add new column to that table called "time_modified" with the value in another column "last_event_time". Running a migration script is taking long time , so need a simple solution to run in production.

Upvotes: 3

Views: 4643

Answers (2)

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

I suggest use function with pg_sleep, which wait between iteriation in loop

This way don't invoke exclusive lock and others locks on your_table.

SELECT pg_sleep(seconds);

But time of execute is long

alter table my_table add time_modified timestamp;

CREATE OR REPLACE FUNCTION update_mew_column()
  RETURNS void AS
$BODY$
DECLARE
    rec record;

BEGIN
    for rec in (select id,last_event_time from your_table) loop

        update your_table set time_modified = rec.last_event_time where id = rec.id;
        PERFORM pg_sleep(0.01);

    end loop;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

and execute function:

select update_mew_column();

Upvotes: 0

klin
klin

Reputation: 121524

Assuming that the columns are timestamps you can try:

alter table my_table add time_modified text;
alter table my_table alter time_modified type timestamp using last_event_time;

Upvotes: 4

Related Questions