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.user330315– user3303152020-03-01 11:37:56 +00:00Commented Mar 1, 2020 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– Ian Wanyonyi2020-03-01 12:11:12 +00:00Commented Mar 1, 2020 at 12:11
Add a comment
|
1 Answer
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
