Postgresql表分区_pgsql 设置分区字段

通过指定字段值范围分区

CREATE TABLE table_name (
    "id" int4 NOT NULL,
    "name" varchar(10),
    "age" int4,
    "sex" varchar(2)
)PARTITION BY RANGE (  //通过范围
    "id"   //通过id值分区
);
//id值1-100 存储到分区p0
CREATE TABLE "p0" PARTITION OF table_name  FOR VALUES FROM ('1') TO ('100');  
//id值101-200 存储到分区p1
CREATE TABLE "p1" PARTITION OF table_name  FOR VALUES FROM ('101') TO ('200'); 
1
2
3
4
5
6
7
8
9
10
11
12

通过指定字段的值分区

CREATE TABLE table_name  (
    "id" int4 NOT NULL,
    "name" varchar(10),
    "age" int4,
    "sex" varchar(2)
)PARTITION BY LIST ( //通过范围
        "sex"
);
//值为男的数据存储到分区p3
CREATE TABLE "p3" PARTITION OFtable_name  FOR VALUES IN ('男');
//值为男的数据存储到分区p4
CREATE TABLE "p4" PARTITION OF table_name  FOR VALUES IN ('女');
1
2
3
4
5
6
7
8
9
10
11
12