【postgresql】pg分区表之列表分区

1.列表分区 

(1)创建父表 

create table sspudb_list1
(
id numeric(20,0) NOT NULL,
name character varying(20) NOT NULL,
age numeric(10,0) NOT NULL,
sex character varying(10) NOT NULL,
cardid numeric(30,0) NOT NULL,
joindate timestamp without time zone NOT NULL,
region character varying(12) NOT NULL,
primary key (id,region)
)
PARTITION BY LIST(region);

(2)创建子表 
create table sspudb_list1_pit1 partition of sspudb_list1 for values in ('北京市','上海市','深圳市','广东省');
create table sspudb_list1_pit2 partition of sspudb_list1 for values in ('浙江省','江苏省','四川省');
create table sspudb_list1_pother partition of sspudb_list1 default;

(3)查看表结构 
sspudb=> \d+ sspudb_list1
                                            Partitioned table "public.sspudb_list1"
  Column  |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
----------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id       | numeric(20,0)               |           | not null |         | main     |             |              | 
 name     | character varying(20)       |           | not null |         | extended |             |              | 
 age      | numeric(10,0)               |           | not null |         | main     |             |              | 
 sex      | character varying(10)       |           | not null |         | extended |             |              | 
 cardid   | numeric(30,0)               |           | not null |         | main     |             |              | 
 joindate | timestamp without time zone |           | not null |         | plain    |             |              | 
 region   | character varying(12)       |           | not null |         | extended |             |              | 
Partition key: LIST (region)
Indexes:
    "sspudb_list1_pkey" PRIMARY KEY, btree (id, region)
Partitions: sspudb_list1_pit1 FOR VALUES IN ('北京市', '上海市', '深圳市', '广东省'),
            sspudb_list1_pit2 FOR VALUES IN ('浙江省', '江苏省', '四川省'),
            sspudb_list1_pother DEFAULT


(4)插入数据 
insert into sspudb_list1 values 
(1,'xsq1',15,'male',26654456235152,'2012-12-01','北京市'),
(2,'xsq2',12,'male',26654452235152,'2013-12-01','上海市'),
(3,'xsq1',13,'male',26654456233152,'2014-12-01','深圳市'),
(4,'xsq1',16,'male',26654446235152,'2015-12-01','广东省'),
(5,'xsq1',15,'male',26654456535152,'2016-12-01','浙江省'),
(6,'xsq6',16,'male',26644456235152,'2012-12-01','江苏省'),
(7,'xsq6',17,'male',26544456235152,'2012-12-01','四川省');

(5)创建索引  
CREATE INDEX idx_sspudb_list1_region ON sspudb_list1 USING BTREE(region);

sspudb=> \d+ sspudb_list1
                                            Partitioned table "public.sspudb_list1"
  Column  |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
----------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id       | numeric(20,0)               |           | not null |         | main     |             |              | 
 name     | character varying(20)       |           | not null |         | extended |             |              | 
 age      | numeric(10,0)               |           | not null |         | main     |             |              | 
 sex      | character varying(10)       |           | not null |         | extended |             |              | 
 cardid   | numeric(30,0)               |           | not null |         | main     |             |              | 
 joindate | timestamp without time zone |           | not null |         | plain    |             |              | 
 region   | character varying(12)       |           | not null |         | extended |             |              | 
Partition key: LIST (region)
Indexes:
    "sspudb_list1_pkey" PRIMARY KEY, btree (id, region)
    "idx_sspudb_list1_region" btree (region)
Partitions: sspudb_list1_pit1 FOR VALUES IN ('北京市', '上海市', '深圳市', '广东省'),
            sspudb_list1_pit2 FOR VALUES IN ('浙江省', '江苏省', '四川省'),
            sspudb_list1_pother DEFAULT
(6)数据查询  
SELECT v.tableoid::regclass,count(v.*) FROM sspudb_list1 v GROUP BY v.tableoid;
sspudb=> SELECT v.tableoid::regclass,count(v.*) FROM sspudb_list1 v GROUP BY v.tableoid;
     tableoid      | count 
-------------------+-------
 sspudb_list1_pit2 |     3
 sspudb_list1_pit1 |     4
(2 rows)