Satisfactory measurements like don't forget to make not of primary andįoreign and indexes also don't forget to procedure, functions backup also just make sure everything is fine.ĭrop/rename the table and then create the New table with your specific Take the full backup of the database or the particular table with You can also do this in your test environment also.
Hello However i don't know how big the data and table size is. Once all chunks of this are done and verified, change all the code to rely on the new column rather than the old column.Ĭhange all the code to stop trying to maintain the old columnĭrop the index that supports finding still-NULL values of the new column (unless it is still useful) Do this in chunks that are as large as feasible, given that the first row in the chunk will be locked for the duration of the processing of the chunk.
Write a script to update rows with NULL values of new column to set them to be correct based on the value of the old column. The efficient way is to schedule a downtime window in which to do it.Īdd a new column of the desired type, with a NULL default value.Ĭhange all the code which changes that old column or inserts new rows to make sure they correctly populate the new column as well (but make sure they don't depend on the new value already being correct-they just make it be correct)Ĭreate an index to efficiently find rows where the new column is still NULL
#POSTGRES ALTER COLUMN DATA TYPE HOW TO#
How to do it efficiently and how to do it without blocking everyone else for the duration are diametrically opposed goals.
0 kommentar(er)
