![]() Modify the name of the table field alter table tb_test rename column "result" to "Result" Īlter table tb_test rename column "Result" to "result" ![]() Dealer_code "is' invitation code ' Ĭreate a unique index SQL for the table CREATE UNIQUE INDEX uk_tb_test_type ON tb_test(type) Ĭreate index idx_tb_test_id_ip on tb_test (id desc, ip desc) SQL statement for adding table fields: ALTER TABLE tb_test ADD COLUMN dealer_code VARCHAR(20) COLLATE "pg_catalog"."default" Ĭomment on column "tb_test". “Remark” is’ remarks’ Ĭomment on table “public”. “Update_time” is’ update time ‘ Ĭomment on column “public”. “Update_by” is’ updater ‘ Ĭomment on column “public”. ![]() “Create_time” is’ creation time ‘ Ĭomment on column “public”. “Create_by” is’ creator ‘ Ĭomment on column “public”. “Result” is’ return result ‘ ĬOMMENT ON COLUMN “public”.”tb_test”.”type” IS ‘type’ Ĭomment on column “public”. “Param” is’ request parameter ‘ Ĭomment on column “public”. “IP” is’ IP address’ Ĭomment on column “public”. “Title” is’ module title ‘ Ĭomment on column “public”. “Id” is’ primary key ID ‘ Ĭomment on column “public”. "remark" varchar(500) COLLATE "pg_catalog"."default",ĬONSTRAINT "cgm_third_docking_log_pkey" PRIMARY KEY ("id")Ĭomment on column “public”. "update_by" varchar(30) COLLATE "pg_catalog"."default", "create_by" varchar(30) COLLATE "pg_catalog"."default", "type" varchar(10) COLLATE "pg_catalog"."default" NOT NULL, "result" varchar(500) COLLATE "pg_catalog"."default", "param" varchar(500) COLLATE "pg_catalog"."default", "ip" varchar(50) COLLATE "pg_catalog"."default", "title" varchar(50) COLLATE "pg_catalog"."default", 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.SQL statement for creating database table: 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 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.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |