PostgreSQL 10 High Performance - Third Edition

Range partitioning

Now let's see how to make a RANGE partitioning using the same table structure; we'll try to partition the table by date into four quarters. First, define a master table containing the partition method, RANGE:

drop table if exists orders_state; 
 
create table orders_state 
        (orderid integer not null, 
        orderdate date not null, 
        customerid integer not null, 
        tax numeric(12,2) not null , 
        state char(2)) 
partition by  RANGE (orderdate); 

Next, we have to create the child tables:

CREATE TABLE orders_state_q1 PARTITION OF orders_state FOR VALUES FROM ('2018-01-01') TO ('2018-03-01'); CREATE TABLE orders_state_q2 PARTITION OF orders_state FOR VALUES FROM ('2018-03-01') TO ('2018-06-01'); CREATE TABLE orders_state_q3 PARTITION OF orders_state ...