Wednesday, January 9, 2019

How to create a LIST-RANGE sub-partitioned table in PostgreSQL



Create the table:
CREATE TABLE orders(
  id            integer,
  country_code  VARCHAR(5),
  customer_id   integer,
  order_date    DATE,
  order_total   numeric(8,2)
)
PARTITION BY LIST (country_code);

Create partition for your table. I have defined two. Define it so that it is ready to be further diveded into sub-partitions:
CREATE TABLE orders_NO
 PARTITION OF orders 
 FOR VALUES IN ('no') 
 PARTITION BY RANGE (order_total);

CREATE TABLE orders_SE
 PARTITION OF orders 
 FOR VALUES IN ('se') 
 PARTITION BY RANGE (order_total);

Finally, create your sub-partitions and set their boundaries:
CREATE TABLE small_orders_no
 PARTITION OF orders_NO FOR VALUES FROM (0) TO (2000);

CREATE TABLE medium_orders_no
 PARTITION OF orders_NO FOR VALUES FROM (2001) TO (4000);

CREATE TABLE large_orders_no
 PARTITION OF orders_NO FOR VALUES FROM (4001) to (maxvalue);

CREATE TABLE small_orders_se
PARTITION OF orders_SE FOR VALUES FROM (0) TO (2000);

CREATE TABLE medium_orders_se
 PARTITION OF orders_SE FOR VALUES FROM (2001) TO (4000);

CREATE TABLE large_orders_se
 PARTITION OF orders_SE FOR VALUES FROM (4001) to (maxvalue);

No comments:

Post a Comment