1.父子繼承表
目前openGauss還不支持inherits繼承特性。
omm=# CREATE TABLE tab_t2(age int) inherits(tab_t1);
ERROR: CREATE TABLE ... INHERITS is not yet supported.
PostgreSQL支持繼承，版本10之前的分區(qū)表都是通過繼承特性來實現(xiàn)，每個分區(qū)實際上都是一個獨立的表。數(shù)據(jù)更新可通過觸發(fā)器trigger或者規(guī)則rule來實現(xiàn)。
下面演示PostgreSQL中的繼承特性：
CREATE TABLE tab_t1(id int primary key,name varchar(20) not null);
CREATE TABLE tab_t2(age int) inherits(tab_t1);
對父表增加字段
alter table tab_t1 add create_date date;
查看表結(jié)構(gòu)
postgres=# \d tab_t1
Table "public.tab_t1"
Column | Type | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(20) | | not null |
create_date | date | | |
Indexes:
"tab_t1_pkey" PRIMARY KEY, btree (id)
Number of child tables: 1 (Use \d+ to list them.)
postgres=# \d tab_t2
Table "public.tab_t2"
Column | Type | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(20) | | not null |
age | integer | | |
create_date | date | | |
Inherits: tab_t1
我們不通過觸發(fā)器或者規(guī)則路由數(shù)據(jù)，直接插入數(shù)據(jù)
INSERT INTO tab_t1 VALUES (1,'data 1 in tab_t1',now());
INSERT INTO tab_t1 VALUES (2,'data 2 in tab_t1',now());
INSERT INTO tab_t2 VALUES (3,'data 3 in tab_t2',18,now());
INSERT INTO tab_t2 VALUES (4,'data 4 in tab_t2',20,now());
從父表中查詢數(shù)據(jù)將顯示父表及子表的所有數(shù)據(jù)
postgres=# SELECT * from tab_t1;
id | name | create_date
----+------------------+-------------
1 | data 1 in tab_t1 | 2021-04-11
2 | data 2 in tab_t1 | 2021-04-11
3 | data 3 in tab_t2 | 2021-04-11
4 | data 4 in tab_t2 | 2021-04-11
(4 rows)
通過ONLY關(guān)鍵字實現(xiàn)只對父表的查詢
postgres=# SELECT * from ONLY tab_t1;
id | name | create_date
----+------------------+-------------
1 | data 1 in tab_t1 | 2021-04-11
2 | data 2 in tab_t1 | 2021-04-11
(2 rows)
從子表中查詢只顯示子表中的數(shù)據(jù)
postgres=# select * from tab_t2;
id | name | age | create_date
----+------------------+-----+-------------
3 | data 3 in tab_t2 | 18 | 2021-04-11
4 | data 4 in tab_t2 | 20 | 2021-04-11
(2 rows)
繼承特性使用注意點：
- 子表并不能完全繼承父表的所有屬性，比如唯一約束、主鍵、外鍵，檢查約束與非空約束可以繼承。
- 修改父表的結(jié)構(gòu)，子表結(jié)構(gòu)同時被修改。
- 父表不存數(shù)據(jù)時，不建議在父表上創(chuàng)建索引和或唯一約束，應該在每個子表上分別創(chuàng)建。
2.聲明式分區(qū)：范圍分區(qū)
將數(shù)據(jù)基于范圍映射到每一個分區(qū)，這個范圍是由創(chuàng)建分區(qū)表時指定的分區(qū)鍵決定的。這種分區(qū)方式較為常用，并且分區(qū)鍵經(jīng)常采用日期。
PostgreSQL從版本10開始支持，范圍分區(qū)聲明式語法分兩步：
1.通過指定PARTITION BY子句把表創(chuàng)建為分區(qū)表，包括分區(qū)方法以及用作分區(qū)鍵的column列表。
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate)
2.創(chuàng)建分區(qū)，每個分區(qū)的定義必須指定對應于父表的分區(qū)方法和分區(qū)鍵的邊界。
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
...
openGauss范圍分區(qū)聲明式語法可以一步完成，范圍分區(qū)從句語法有兩種格式
- VALUES LESS THAN語法格式(范圍分區(qū)策略的分區(qū)鍵最多支持4列)
- START END語法格式(范圍分區(qū)策略的分區(qū)鍵僅支持1列)
注意上面兩種從句語法不能混用，START END語法格式使用gs_dump時會轉(zhuǎn)變?yōu)閂ALUES LESS THAN語法格式。
openGauss范圍分區(qū)例子
CREATE TABLE tab_part (
id int not null,
create_date date not null
) PARTITION BY RANGE(create_date)
(
PARTITION p_20210401 VALUES LESS THAN(to_date('2021-04-01','yyyy-mm-dd')),
PARTITION p_20210402 VALUES LESS THAN(to_date('2021-04-02','yyyy-mm-dd')),
PARTITION p_max VALUES LESS THAN(MAXVALUE)
);
查看系統(tǒng)表可看到分區(qū)策略為“r”，range分區(qū)。
omm=# select relname,partstrategy from pg_partition where relname='tab_part';
relname | partstrategy
----------+--------------
tab_part | r
(1 row)
查看分區(qū)及邊界
select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_part');
relname | parttype | parentid | boundaries
------------+----------+----------+-------------------------
tab_part | r | 16412 |
p_20210401 | p | 16412 | {"2021-04-01 00:00:00"}
p_20210402 | p | 16412 | {"2021-04-02 00:00:00"}
p_max | p | 16412 | {NULL}
(4 rows)
接下來插入三條數(shù)據(jù)
insert into tab_part values(1,'2021-03-31');
insert into tab_part values(2,'2021-04-01');
insert into tab_part values(3,'9999-12-31');
查詢分區(qū)，按分區(qū)名p_20210402，也可以按分區(qū)邊界值（PARTITION FOR）
omm=# select * from tab_part PARTITION (p_20210402);
id | create_date
----+---------------------
2 | 2021-04-01 00:00:00
(1 row)
刪除分區(qū)
alter table tab_part drop partition p_max;
添加分區(qū)
alter table tab_part add partition p_20210403
VALUES LESS THAN(to_date('2021-04-03','yyyy-mm-dd'));
3.聲明式分區(qū)：列表分區(qū)
通過顯式地列出每一個分區(qū)中出現(xiàn)的鍵值來劃分表。
與前面范圍分區(qū)一樣，PostgreSQL列表分區(qū)聲明式語法也是兩步，從版本10開始支持。
openGauss列表分區(qū)例子
CREATE TABLE tab_list(
dept_no number,
part_no varchar2(20),
country varchar2(20),
dtime date,
amount number
)
PARTITION BY LIST(country)(
PARTITION europe VALUES('FRANCE', 'ITALY'),
PARTITION asia VALUES('INDIA', 'PAKISTAN'),
PARTITION americas VALUES('US', 'CANADA')
);
查看系統(tǒng)表可看到分區(qū)策略為“l(fā)”，list分區(qū)。
omm=# select relname,partstrategy from pg_partition where relname='tab_list';
relname | partstrategy
----------+--------------
tab_list | l
(1 row)
查看分區(qū)及邊界
select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_list');
relname | parttype | parentid | boundaries
----------+----------+----------+------------------
tab_list | r | 16389 |
americas | p | 16389 | {US,CANADA}
asia | p | 16389 | {INDIA,PAKISTAN}
europe | p | 16389 | {FRANCE,ITALY}
(4 rows)
刪除分區(qū)
alter table tab_list drop partition europe;
添加分區(qū)
alter table tab_list add partition tab_list_europe
VALUES('FRANCE', 'ITALY');
4.聲明式分區(qū)：哈希分區(qū)
將數(shù)據(jù)通過哈希映射到每一個分區(qū)，每一個分區(qū)中存儲了具有相同哈希值的記錄。
PostgreSQL哈希分區(qū)聲明式語法也是兩步，從版本11開始支持。
openGauss哈希分區(qū)例子
CREATE TABLE tab_hash(
dept_no number,
part_no varchar2(20),
country varchar2(20),
dtime date,
amount number
)PARTITION BY HASH(part_no)(
PARTITION p1,
PARTITION p2,
PARTITION p3
);
查看系統(tǒng)表可看到分區(qū)策略為“h”，hash分區(qū)。
omm=# select relname,partstrategy from pg_partition where relname='tab_hash';
relname | partstrategy
----------+--------------
tab_hash | h
(1 row)
查看分區(qū)及邊界
select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_hash');
relname | parttype | parentid | boundaries
----------+----------+----------+------------
tab_hash | r | 16405 |
p3 | p | 16405 | {2}
p2 | p | 16405 | {1}
p1 | p | 16405 | {0}
(4 rows)
hash分區(qū)不支持刪除分區(qū)和添加分區(qū)
5.基于范圍分區(qū)的自動擴展間隔分區(qū)
間隔分區(qū)（Interval-Partition）是針對Range類型分區(qū)的一種功能拓展。對連續(xù)數(shù)據(jù)類型的Range分區(qū)，如果插入的新數(shù)據(jù)值與當前分區(qū)均不匹配，Interval-Partition特性可以實現(xiàn)自動的分區(qū)創(chuàng)建。分區(qū)字段必須是時間類型(date或timestamp)。
PostgreSQL目前還不支持該語法，openGauss已經(jīng)支持。
openGauss間隔分區(qū)例子
CREATE TABLE tab_range_interval (
id int not null,
create_date date not null
) PARTITION BY RANGE(create_date) INTERVAL('1 month')
(
PARTITION p202101 VALUES LESS THAN(to_date('2021-02-01','yyyy-mm-dd')),
PARTITION p202102 VALUES LESS THAN(to_date('2021-03-01','yyyy-mm-dd'))
);
查看系統(tǒng)表可看到分區(qū)策略為“i”，interval分區(qū)。
omm=# select relname,partstrategy,interval from pg_partition where relname='tab_range_interval';
relname | partstrategy | interval
----------+--------------+-----------
tab_part | i | {"1 month"}
(1 row)
接下來插入三條數(shù)據(jù)
insert into tab_range_interval values(1,'2021-01-29');
insert into tab_range_interval values(2,'2021-02-28');
insert into tab_range_interval values(3,'2022-03-29');
插入數(shù)據(jù)后檢查是否自動創(chuàng)建了相應的分區(qū)
omm=# select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_range_interval');
relname | parttype | parentid | boundaries
--------------------+----------+----------+--------------
tab_range_interval | r | 41056 |
p202101 | p | 41056 | {"2021-02-01 00:00:00"}
p202102 | p | 41056 | {"2021-03-01 00:00:00"}
sys_p1 | p | 41056 | {"2022-04-01 00:00:00"}
(4 rows)
可以看到sys_p1為系統(tǒng)自動生成的分區(qū)。
注意：
1.從2.0.0版本開始，模板庫默認字符集由SQL_ASCII改為了UTF8,同時數(shù)據(jù)庫兼容性由ORACLE改為PG，對本測試的影響是date數(shù)據(jù)類型。
2.目前只支持INTERVAL-RANGE，其它方式不支持。
3.間隔分區(qū)字段必須是時間類型(date或timestamp)。
6.子分區(qū)
openGauss目前還不支持子分區(qū)，PG聲明式分區(qū)是支持子分區(qū)。
PG子分區(qū)例子
先創(chuàng)建分區(qū)表
CREATE TABLE sales(
id serial,
sales_count int,
sales_date date not null
) PARTITION BY RANGE(sales_date);
再按年創(chuàng)建分區(qū)：sales_2018、sales_2019、sales_2020
CREATE TABLE sales_2018 PARTITION OF sales
FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
CREATE TABLE sales_2019 PARTITION OF sales
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
CREATE TABLE sales_2020 PARTITION OF sales
FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
2021年新建分區(qū)時可以創(chuàng)建子分區(qū)，按月分區(qū)
create table sales_2021 partition of sales
for values from ('2021-01-01') to ('2022-01-01') partition by range(sales_date);
create table sales_2021_01 partition of sales_2021
for values from ('2021-01-01') to ('2021-02-01');
create table sales_2021_02 partition of sales_2021
for values from ('2021-02-01') to ('2021-03-01');
create table sales_2021_03 partition of sales_2021
for values from ('2021-03-01') to ('2021-04-01');
create table sales_2021_04 partition of sales_2021
for values from ('2021-04-01') to ('2021-05-01');
create table sales_2021_05 partition of sales_2021
for values from ('2021-05-01') to ('2021-06-01');
create table sales_2021_06 partition of sales_2021
for values from ('2021-06-01') to ('2021-07-01');
create table sales_2021_07 partition of sales_2021
for values from ('2021-07-01') to ('2021-08-01');
create table sales_2021_08 partition of sales_2021
for values from ('2021-08-01') to ('2021-09-01');
create table sales_2021_09 partition of sales_2021
for values from ('2021-09-01') to ('2021-10-01');
create table sales_2021_10 partition of sales_2021
for values from ('2021-10-01') to ('2021-11-01');
create table sales_2021_11 partition of sales_2021
for values from ('2021-11-01') to ('2021-12-01');
create table sales_2021_12 partition of sales_2021
for values from ('2021-12-01') to ('2022-01-01');
下面我們通過\d+查看sale表，可以看到分區(qū)sales_2021比其它分區(qū)多一個PARTITIONED屬性，說明它是一個子分區(qū)。
Partitioned table "postgres.sales"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+---------+-----------+----------+-----------------------------------+---------+--------------+-------------
id | integer | | not null | nextval('sales_id_seq'::regclass) | plain | |
sales_count | integer | | | | plain | |
sales_date | date | | not null | | plain | |
Partition key: RANGE (sales_date)
Partitions: sales_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
sales_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
sales_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
sales_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'), PARTITIONED
我們可以通過\d+查看sales_2021
Partitioned table "postgres.sales_2021"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+---------+-----------+----------+-----------------------------------+---------+--------------+-------------
id | integer | | not null | nextval('sales_id_seq'::regclass) | plain | |
sales_count | integer | | | | plain | |
sales_date | date | | not null | | plain | |
Partition of: sales FOR VALUES FROM ('2021-01-01') TO ('2022-01-01')
Partition constraint: ((sales_date IS NOT NULL) AND (sales_date >= '2021-01-01'::date) AND (sales_date < '2022-01-01'::date))
Partition key: RANGE (sales_date)
Partitions: sales_2021_01 FOR VALUES FROM ('2021-01-01') TO ('2021-02-01'),
sales_2021_02 FOR VALUES FROM ('2021-02-01') TO ('2021-03-01'),
sales_2021_03 FOR VALUES FROM ('2021-03-01') TO ('2021-04-01'),
sales_2021_04 FOR VALUES FROM ('2021-04-01') TO ('2021-05-01'),
sales_2021_05 FOR VALUES FROM ('2021-05-01') TO ('2021-06-01'),
sales_2021_06 FOR VALUES FROM ('2021-06-01') TO ('2021-07-01'),
sales_2021_07 FOR VALUES FROM ('2021-07-01') TO ('2021-08-01'),
sales_2021_08 FOR VALUES FROM ('2021-08-01') TO ('2021-09-01'),
sales_2021_09 FOR VALUES FROM ('2021-09-01') TO ('2021-10-01'),
sales_2021_10 FOR VALUES FROM ('2021-10-01') TO ('2021-11-01'),
sales_2021_11 FOR VALUES FROM ('2021-11-01') TO ('2021-12-01'),
sales_2021_12 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01')
總結(jié)
1.openGauss目前只支持聲明式分區(qū)，支持范圍分區(qū)、列表分區(qū)、哈希分區(qū)以及INTERVAL-RANGE的自動擴展間隔分區(qū)。PostgreSQL支持繼承及聲明式分區(qū)，不支持自動擴展間隔分區(qū)。
2.自動擴展間隔分區(qū)的分區(qū)字段目前只支持時間類型(date或timestamp)。
3.openGauss2.0目前不支持子分區(qū)(2.1.0開始支持子分區(qū))，PG支持聲明式子分區(qū)。
4.對于聲明式分區(qū)的分區(qū)來說，分區(qū)必須具有和分區(qū)表正好相同的列集合，表結(jié)構(gòu)必須嚴格一致，而在表繼承中，子表可以有父表中沒有出現(xiàn)過的額外列，同時表繼承允許多繼承。
最后修改時間：2025-07-14 11:11:33
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，墨天輪將立刻刪除相關(guān)內(nèi)容。
