How Do I Rename a User/Role in Postgres Using ALTER ROLE Command? This write-up will teach you how to rename a user in Postgres via ALTER ROLE and ALTER USER commands. You must log out from the current user and log in as another user to rename the current session user. In Postgres, the session user(currently logged in) can't be renamed. Migrate clients to use the new table and column names.PostgreSQL provides a RENAME TO clause that is used with the ALTER ROLE or the ALTER USER statements to rename a user. Select unchanged_name, old_name as new_name from new_table Name to get the new column names: create view old_table as The original set of columns, so clients have to migrate to the new table Instead of select *, old_name as new_name, it might be better to only have Select *, old_name as new_name from new_table Rename the table and columns, and temporarily add an updatable view with the old name and old columns: begin Īlter table new_table rename column to old_name to new_name Renaming a table and some of its columns simultaneously Once all clients are migrated, drop the view, rename the table back: begin Īlter table my_table_tmp rename to my_table Migrate clients to use the new column name. Select *, new_name as old_name from my_table_tmp View that adds the old name for the column with the table's correct name: begin Īlter table my_table rename column old_name to new_name Īlter table my_table rename to my_table_tmp Rename the column(s), temporarily rename the table, and add an updatable Renaming a column without renaming the table is a bit more complicated,īecause we can't have a view shadow a table (apparently). Renaming columns without renaming the table Once all clients are migrated, drop the view: drop view old_table Migrate clients to use the new table name. Rename the table, and temporarily add an updatable view with the old name: begin Īlter table old_table rename to new_table Ĭreate view old_table as select * from new_table Renaming and view creation are both supposed to be very fast, though. However, I've only tested these on a small test database, not on a large production system. Some recipes for renaming tables and/or columns in a production system that seem to work. Is there a way to do this? If not, is there a recommended procedure for renaming a column while minimizing downtime? How about a table? once all clients have been updated, remove old_name.modify all clients to use new_name instead of old_name. Then a name migration could work like this: I was hoping there was some way to "add a name" to a table or column, so that old_name and new_name will both work, and then at a later time remove the old name. If existing clients are using the old name, you can't just yank it out from under them. PostgreSQL lets you rename tables and columns via alter table, but often this isn't feasible in a production without significant downtime. Sometimes one name is used in developing a feature, but testing with real users reveals that a better name is needed, and it'd be nice to have the names for things in the DB match the names used in the UI. Maybe requirements shifted slightly, or maybe as time went on, a better understanding of the concept being represented developed. It often happens that the original name something is given is not the best name.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |