In range partitioning, the table is partitioned into ranges. Each non-overlapped range consists of single or multiple columns representing a single table. For this example, a partition is needed for each quarter that inherits from the main orders table:
However, only the column structure is inherited. You'll need to add indexes, constraints, and adjust permissions on each individual partition to match the master table. The output from psql\d for the table, as shown for the preceding orders table, can be a helpful guide as to what all of those are.
Each partition needs the same primary key:
That will create an index by orderid as well. A manual index on customerid is also needed:
Each order also contains a foreign key constraint to ensure the customer referenced is valid. Those need to be applied to each partition:
The other constraint involved here is actually against the orderliness table, confirming that each order exists. So long as we're careful to never remove an order while working on the partitioning, that constraint can stay in place without modifications. If the table were being dumped and reloaded, you'd have to drop that constraint while that was going on, lest the constraint be violated and cause a problem.