Here we could observe more resources used, more space allocated in table, but application has been working most of the time without high contention locking. Select * from pg_stat_statements where query like '%optionB%' After no rows need changes, we can switch the columnsĪLTER /*optionB*/ TABLE PRU DROP COLUMN A ĪLTER /*optionB*/ TABLE PRU DROP COLUMN A1_CHANGED ĪLTER /*optionB*/ TABLE PRU RENAME A1 TO A SELECT COUNT(1) FROM PRU WHERE A1_CHANGED is null SELECT COUNT(1) FROM PRU WHERE A1_CHANGED=true
#Postgresql change column type 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)
![postgresql change column type postgresql change column type](https://sqlserverguides.com/wp-content/uploads/2021/07/PostgreSQL-ALTER-TABLE-modify-column-type-to-integer.png)
![postgresql change column type postgresql change column type](https://comicvine.gamespot.com/a/uploads/original/5/52246/5464619-vessbooks4.jpg)
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%' For more information on the data types that are supported by PostgreSQL. We could review stats from this command with following command: Use the drop-down listbox next to Data Type to select a data type for the column. 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()
![postgresql change column type postgresql change column type](https://dataedo.com/asset/img/kb/db-tools/sql_workbench_j/sql_workbench_j_comments.png)
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.