Your RDBMS will take a copy of the data within the table A.The way ALTER TABLE works is a little different to SELECT, INSERT, UPDATE, or DELETE queries that you are so used to – once the statement is used, your database management system will go through a couple of phases (for convenience, the original table that you run queries on will be called A, and the other will be called B): Rename columns, add constraints, and do a whole bunch of other things.īy now, you should get it – the ALTER TABLE statement can perform pretty much any action related to modifying data within a table.change the VARCHAR data type to INT on a certain column, etc.) Change the row format of rows in a table.Specify options within a table (one can specify the size of AUTO_INCREMENT, specify the average row length, the default character set, default collations, set comments, set directories that hold indexes or data, etc.).Add, drop, discard, import, truncate, reorganize, repair, remove, or otherwise modify partitions.Add, modify, or delete indexes from a table.Add, modify, or drop columns belonging to a certain table.The ALTER TABLE statement can be used to: In either case, you should be careful to first understand how stable the data in the column is so you don't miss new changes.Rather simple, right? This simple statement has a lot of use cases – care to go through them with us? The Use Cases of ALTER TABLE However, the application has been working most of the time without high contention locking. The tradeoff here is that we can observe more resources used, and more space allocated to the table. SELECT * FROM pg_stat_statements WHERE query like '%optionB%' We could review stats from this command with following command: 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 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 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 |