postgresql分表(table partitioning)

1、创建模式

create schema dataschema;

2、建表measurement

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

3、创建分区

CREATE TABLE measurement_y2019m07 PARTITION OF measurement
    FOR VALUES FROM ('2019-07-01') TO ('2019-07-31');
CREATE TABLE measurement_y2019m08 PARTITION OF measurement
    FOR VALUES FROM ('2019-08-01') TO ('2019-08-31');

4、建立索引

CREATE INDEX ON measurement (logdate);

5、插入数据

INSERT INTO measurement(city_id,logdate) VALUES(1,'2019-07-02');
INSERT INTO measurement(city_id,logdate) VALUES(2,'2019-08-02');

6、查询结果

select * from measurement;
select * from measurement_y2019m08;