分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。
分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。

0. 进入环境

su - omm
gsql -r
复制

1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录

先看看默认有什么吧:

\d+;
复制

– 回显

                                    List of relations
 Schema | Name | Type  | Owner |  Size   |             Storage              | Description
--------+------+-------+-------+---------+----------------------------------+-------------
 public | t1   | table | omm   | 0 bytes | {orientation=row,compression=no} |
 public | t2   | table | omm   | 0 bytes | {orientation=row,compression=no} |
(2 rows)
复制

创建表store

create table store
(
        id int,
        goods varchar(20),
        inventory int
)
partition by range (inventory)
(
        partition store_p0 values less than (100),
        partition store_p1 values less than (200),
        partition store_p2 values less than (300),
        partition store_p3 values less than (400),
        partition store_p4 values less than (500)
);

\d+ store;
select * from pg_partition;
复制

– 回显

                                 Table "public.store"
  Column   |         Type          | Modifiers | Storage  | Stats target | Description
-----------+-----------------------+-----------+----------+--------------+-------------
 id        | integer               |           | plain    |              |
 goods     | character varying(20) |           | extended |              |
 inventory | integer               |           | plain    |              |
Range partition by(inventory)
Number of partition: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
复制

插入数据并查看

insert into store values (101, 'box', 80), (102, 'toy', 180), (103, 'citrus',260), (104, 'apple', 350), (105, 'egg', 450);
select * from store;
复制

– 回显

 id  | goods  | inventory
-----+--------+-----------
 101 | box    |        80
 102 | toy    |       180
 103 | citrus |       260
 104 | apple  |       350
 105 | egg    |       450
(5 rows)
复制

2.查看分区1上的数据

select * from store partition(store_p0);
复制

– 回显

omm=#  id  | goods | inventory
-----+-------+-----------
 101 | box   |        80
(1 row)
复制

3.重命名分区2

ALTER TABLE store RENAME PARTITION store_p1 to store_p10;
select relname from pg_partition;
复制

– 回显

  relname
-----------
 store
 store_p0
 store_p2
 store_p3
 store_p4
 store_p10
(6 rows)
复制

4.删除分区5

ALTER TABLE store drop PARTITION store_p4;
select relname from pg_partition;
复制

– 回显

  relname
-----------
 store
 store_p0
 store_p2
 store_p3
 store_p10
(5 rows)
复制

5.增加分区6

ALTER TABLE store add partition store_p5 VALUES LESS THAN (1000);
select relname from pg_partition;
复制

– 回显

  relname
-----------
 store
 store_p0
 store_p2
 store_p3
 store_p10
 store_p5
(6 rows)
复制

6.在系统表pg_partition中查看分区信息

select * from pg_partition;
复制

– 回显

  relname  | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64
-----------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+---------------------------------------------------+----------------
 store     | r        |    16425 |        0 |           0 | r            |           0 | 0 |        0 |         0 |             0 |             0 |             0 | 0 | t           |             0 |           0 |              0 |            0 | 0 0 |        0 |         0 |             0 |             0 |             0 | 0 | t           |             0 |           0 |              0 |            0 | 9351 |          |         |                    |          | {100}      |         | {orientation |          | 3       |                    |          |            |         | {orientation =row,compression=no,wait_clean_gpi=n} |              0
 store_p0  | p        |    16425 |        0 |           0 | r            |       16429 |
 store_p2  | p        |    16425 |        0 |           0 | r            |       16431 | 0 |        0 |         0 |             0 |             0 |             0 | 0 | t           |             0 |           0 |              0 |            0 | 9351 --More--=row,compression=no}                  |           9351 |          |         |                    |          | {300}      |         | {orientation =row,compression=no}                  |           9351
 store_p3  | p        |    16425 |        0 |           0 | r            |       16432 | 0 |        0 |         0 |             0 |             0 |             0 | 0 | t           |             0 |           0 |              0 |            0 | 9351 |          |         |                    |          | {400}      |         | {orientation =row,compression=no}                  |           9351
 store_p10 | p        |    16425 |        0 |           0 | r            |       16430 | 0 |        0 |         0 |             0 |             0 |             0 | 0 | t           |             0 |           0 |              0 |            0 | 9351 |          |         |                    |          | {200}      |         | {orientation =row,compression=no}                  |           9351
 store_p5  | p        |    16425 |        0 |           0 | r            |       16434 | 0 |        0 |         0 |             0 |             0 |             0 | 0 | t           |             0 |           0 |              0 |            0 | 9357 |          |         |                    |          | {1000}     |         | {orientation =row,compression=no}                  |           9357
(6 rows)

复制

7.删除分区表

drop table store;
\d+ store;
复制

– 回显

Did not find any relation named "store".
复制

删除成功(完).