Update sentence with limit number or fows to update in single transaction Trigger to take care of ongoing changes from the applicationsĮLSEIF (NEW.a is null and OLD.a is not null) THENĮLSEIF (NEW.a is not null and OLD.a is null) THEN It can be executed over multiple hours or days as needed.ĪLTER TABLE PRU ADD COLUMN A1 INTEGER, ADD COLUMN A1_CHANGED BOOLEAN The advantages of this method is you have more control over the process. You might have to stop your application to perform this type of long running operation.Īnother approach to change the datatype of the column could be to This exclusive lock could generate errors in the application. This method is the easiest one, but could generate high contention due to required exclusive lock for the table. SELECT * FROM pg_stat_statements WHERE query like '%optionA%' We could review stats from the command above with following query: Generate rows until 2M, by looping the following statement:ĪLTER /*optionA*/ TABLE PRU ALTER COLUMN A TYPE INTEGER USING A::INTEGER If you'd like to follow along with an example of this scenario, let's first create a table and generate data for it. Let's say we want to change the type of column A to Integer. In the second column called A we have integer data currently saved as Text type. One is a column called id with type bigserial. Suppose we have a table PRU with two columns. Due to performance and locking reasons, changing a datatype column using ALTER COLUMN can be a long-running operation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |