![]() ![]() Generate a new table that acts as a partition for mytable:Įxecute format('create table %I partition of myTable_master for values from (%L) to (%L)', tableName, monthStart, monthEndExclusive) ![]() Check if the table we need for the supplied date exists. ![]() a date in June 2005 should be int the table mytable_200506:ĭeclare tableName text := 'mytable_' || to_char(forDate, 'YYYYmm') We infer the name of the table from the date that it should contain The old table is renamed (after data migration we'll delete it) and we get a master table for our partition which is basically the same as our original table, but without indexes)Ĭreate a function that can generate new partitions as we need them: create function createPartitionIfNotExists(forDate date) returns voidĭeclare monthStart date := date_trunc('month', forDate) ĭeclare monthEndExclusive date := monthStart + interval '1 month' This should hardly require any explanation. Rename the old table and create a new partitioned table alter table myTable rename to myTable_old In Postgres 10 "Declarative Partitioning" was introduced, which can relieve you of a good deal of work such as generating triggers or rules with huge if/else statements redirecting to the correct table. How can I migrate my table to be partitioned?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |