Postgresql alter table update#

UPDATE /*optionB*/ PRU SET A1=A::INTEGER, A1_CHANGED=true WHERE id IN (SELECT id FROM PRU WHERE A1_CHANGED is null limit 100000) This sentence must be repeated multiple times until all rows were updated 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 The advantages of doing in this way is that the customer has more control about the process, it can be executed during multiples hours/days.ĪLTER TABLE PRU ADD COLUMN A1 INTEGER, ADD COLUMN A1_CHANGED BOOLEAN This method is the easiest one, but could generate high contention due to required exclusive lock for the table that could generate errors in applications, most of them will require stop applications to perform this type of long running operations.Īnother approach to change the datatype of the column could be to add some extra columns, migrate the data to those columns and finally drop old column and rename new one. Select * from pg_stat_statements where query like '%optionA%' We could review stats from this command with following command: Generate rows until 2M, repeating this sentence:ĪLTER /*optionA*/ TABLE PRU ALTER COLUMN A TYPE INTEGER USING A::INTEGER SELECT * FROM pg_stat_statements_reset() Supose table PRU with bigserial id column and column A, with integer data saved as Text that you want to change to Integer type: See the original author and article here.ĭue to performance and locking reasons, change datatype column can be a long-running operation.