I'm looking to rename a column in PostgreSQL with no downtime. My code will depend on the column name so I'd like to duplicate the column with a new name along with the contents and data type of the existing column, then push the code changes before deleting the original column. Is there a Postgres command for duplicating a column with its contents into the same table?
3
-
Is the table/field being updated by SQL updates at the same time as the migration will happen? If so, copying a column will lose changes when changing over since updates will happen on the old field until the code changes over.Joachim Isaksson– Joachim Isaksson2014-07-03 13:47:29 +00:00Commented Jul 3, 2014 at 13:47
-
lock table, alter table, update table setting new col to old col value, drop col. Too bad postgres doesn't support 'MERGE'.Discordanian– Discordanian2014-07-03 13:50:27 +00:00Commented Jul 3, 2014 at 13:50
-
@JoachimIsaksson: The field only gets updated by a cron job, so lost writes won't be an issue. The only problem is that Django will look at the models file, see that there's a missing column name, and throw up an error as soon as it starts up.benwad– benwad2014-07-03 13:57:41 +00:00Commented Jul 3, 2014 at 13:57
Add a comment
|
1 Answer
I found a relatively simple way to do this in two commands:
ALTER TABLE mytable ADD COLUMN "new_column" <DATA TYPE STUFF FROM OLD COLUMN>;
UPDATE mytable SET new_column = old_column;
Didn't realise it would be this easy. I didn't lock the table as that column isn't used too frequently so a small slowdown would be okay.
1 Comment
Layla
Incredible solution! This comment made my day. Thanks @benwad.