POSTGRES - Range Partitioning

Question

I have table which is already designed to partition based on month range.

I am writing code in java to check all existing partitions and would create a new one when it doesnt exist.

If I create a new range partition for the upcoming month, will postgres automatically push data from main table onto the new range once new range data is attempted to be saved in DB ?

I tried inserting data into the main table to make postgres save data into range partition but I suppose its not required.

Could you please share some ddl? Otherwise it’s hard to know what you tried — Frank Heikens, Jun 27 '23 at 17:24
If you use the modern declarative partitioning, then the main table is always empty, and inserting data with no partition for it to go into will produce an error. If you use the old inheritance based partitioning, then you haven't given us enough info to know what is going on. — jjanes, Jun 27 '23 at 19:48

score 0 · Accepted Answer · answered Jun 27 '23 at 19:33

You have to create the partition before you insert data that belong into that partition, just to make that clear.

As soon as the new partition has been created, you can insert new data for that partition into the partitioned table, and the data will end up in the correct partition automatically. Note that the partitioned table itself does not contain any data; all data are routed to the appropriate partition. You simply use the partitioned table as if it were a regular table.

POSTGRES - Range Partitioning

1 Answers1