Rename a column in AWS Aurora DB

user_9890829 0
Viewed 510

I'm like:

ALTER TABLE a RENAME COLUMN b TO c

And the thing is like:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN b TO c' at line 1

I wonder, is it possible to rename a column in Aurora DB? I did my research, but there seems to be like zero docs about these restrictions. How do I rename a column without using RENAME? There's a ton of data in that table, so I don't want to create->copy data->delete.

amazon-aurora amazon-web-services mysql
2 Answers

I've managed to rename it after all, using this weird CHANGE statement. Let me leave the query here in case anyone faces the same issue in the future:

ALTER TABLE a CHANGE b c INT NOT NULL

As you can see, it's not perfect, cause one has to specify column type, not just name. So, make sure you replace INT NOT NULL with your own type. And in case you don't add NOT NULL in there, it might create a nullable column, so beware.

user_98908290

Actually, from perusing the Aurora DB documentation, I did not see renaming columns as an example operation. But here is one workaround. You may add a new column with the name you want, populate it using the data from the old column, and then drop that old column.

ALTER TABLE a ADD COLUMN (c INT), ALGORITHM = INSTANT;  -- or whatever type you want
UPDATE a SET c = b;                                     -- copy over all data from old to new
ALTER TABLE a DROP COLUMN b, ALGORITHM = INSTANT;       -- now drop the old column
user_18632290
Related