ALTERING MULTIPLE TABLES IN POSTGRES

Question

I have multiple tables in which I would like to perform the following operation:

ALTER TABLE table_one DROP COLUMN id, ADD id SERIAL PRIMARY KEY;

Is there I can do it all at once?

You can put multiple ALTER TABLE statements into a transaction. Is that what you mean with "at once"? Also, why do you drop the column and then re-create the same column again? If you just want to change it to an auto-increment column there are much better ways. — , Mar 01 '20 at 11:37
I'm still new to postgres and sql in general. Yes, I am trying to change it to an autoincrement column. Which better ways exist? — Ian Wanyonyi, Mar 01 '20 at 12:11

score 0 · Answer 1 · answered Mar 01 '20 at 13:09

You can create sequences for each table and add default value to id column, for example, for two tables, table1 and table2, this will be like:

do $$
declare maxid int;
begin
    select coalesce(max(id)+1, 1) from table1 into maxid;
    execute 'create sequence table1_id_seq START WITH '||maxid ;
    alter table table1 alter column id set default nextval('table1_id_seq');

    select coalesce(max(id)+1, 1) from table2 into maxid;
    execute 'create sequence table2_id_seq START WITH '||maxid ;
    alter table table2 alter column id set default nextval('table2_id_seq');
end;
$$ language plpgsql

ALTERING MULTIPLE TABLES IN POSTGRES

1 Answers1