I have a large table that I need to partition based on a date field and the partitions will each be one month. Like so:
create table my_schema.my_table_202210 partition of my_schema.my_table
for values from ('2022-10-01 00:00:00') to ('2022-11-01 00:00:00');
alter table my_schema.my_table_202210
owner to my_owner;
create table my_schema.my_table_202211 partition of my_schema.my_table
for values from ('2022-11-01 00:00:00') to ('2022-12-01 00:00:00');
alter table my_schema.my_table_202211
owner to my_owner;
As you can see, with this method I need a partition statement for each month. Is there some way to do this dynamically so I do not need to use static dates and multiple statements? With this method I can only put so many dates here and even if I go out 2 or 3 years I will still need to remember to come back before the last partitioned date and add more statements here to keep my app running. I'm using Liquibase to create and handle my database schema.