Creating the partitions

Partitioning in PostgreSQL is based on the database's table inheritance feature. This allows a table to have children that inherit all of its columns. For this sample, a partition is needed for each month that inherits from the main orders table:

CREATE TABLE orders_2004_01 (
  CHECK ( orderdate >= DATE '2004-01-01' and orderdate < DATE '2004-02-01')
) INHERITS (orders);
…
CREATE TABLE orders_2004_12 (
  CHECK ( orderdate >= DATE '2004-12-01' and orderdate < DATE '2005-01-01')
) INHERITS (orders);

But 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 this is.

Each partition needs the same primary key:

ALTER TABLE ONLY orders_2004_01
    ADD CONSTRAINT orders_2004_01_pkey...