1.父子继承表
目前opengauss还不支持inherits继承特性。
omm=# create table tab_t2(age int) inherits(tab_t1);
error: create table ... inherits is not yet supported.
postgresql支持继承,版本10之前的分区表都是通过继承特性来实现,每个分区实际上都是一个独立的表。数据更新可通过触发器trigger或者规则rule来实现。
下面演示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;
查看表结构
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
我们不通过触发器或者规则路由数据,直接插入数据
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());
从父表中查询数据将显示父表及子表的所有数据
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关键字实现只对父表的查询
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)
从子表中查询只显示子表中的数据
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)
继承特性使用注意点:
- 子表并不能完全继承父表的所有属性,比如唯一约束、主键、外键,检查约束与非空约束可以继承。
- 修改父表的结构,子表结构同时被修改。
- 父表不存数据时,不建议在父表上创建索引和或唯一约束,应该在每个子表上分别创建。
2.声明式分区:范围分区
将数据基于范围映射到每一个分区,这个范围是由创建分区表时指定的分区键决定的。这种分区方式较为常用,并且分区键经常采用日期。
postgresql从版本10开始支持,范围分区声明式语法分两步:
1.通过指定partition by子句把表创建为分区表,包括分区方法以及用作分区键的column列表。
create table measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) partition by range (logdate)
2.创建分区,每个分区的定义必须指定对应于父表的分区方法和分区键的边界。
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范围分区声明式语法可以一步完成,范围分区从句语法有两种格式
- values less than语法格式(范围分区策略的分区键最多支持4列)
- start end语法格式(范围分区策略的分区键仅支持1列)
注意上面两种从句语法不能混用,start end语法格式使用gs_dump时会转变为values less than语法格式。
opengauss范围分区例子
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)
);
查看系统表可看到分区策略为“r”,range分区。
omm=# select relname,partstrategy from pg_partition where relname='tab_part';
relname | partstrategy
---------- --------------
tab_part | r
(1 row)
查看分区及边界
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)
接下来插入三条数据
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');
查询分区,按分区名p_20210402,也可以按分区边界值(partition for)
omm=# select * from tab_part partition (p_20210402);
id | create_date
---- ---------------------
2 | 2021-04-01 00:00:00
(1 row)
删除分区
alter table tab_part drop partition p_max;
添加分区
alter table tab_part add partition p_20210403
values less than(to_date('2021-04-03','yyyy-mm-dd'));
3.声明式分区:列表分区
通过显式地列出每一个分区中出现的键值来划分表。
与前面范围分区一样,postgresql列表分区声明式语法也是两步,从版本10开始支持。
opengauss列表分区例子
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')
);
查看系统表可看到分区策略为“l”,list分区。
omm=# select relname,partstrategy from pg_partition where relname='tab_list';
relname | partstrategy
---------- --------------
tab_list | l
(1 row)
查看分区及边界
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)
删除分区
alter table tab_list drop partition europe;
添加分区
alter table tab_list add partition tab_list_europe
values('france', 'italy');
4.声明式分区:哈希分区
将数据通过哈希映射到每一个分区,每一个分区中存储了具有相同哈希值的记录。
postgresql哈希分区声明式语法也是两步,从版本11开始支持。
opengauss哈希分区例子
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 values,
partition p2,
partition p3
);
查看系统表可看到分区策略为“h”,hash分区。
omm=# select relname,partstrategy from pg_partition where relname='tab_hash';
relname | partstrategy
---------- --------------
tab_hash | h
(1 row)
查看分区及边界
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分区不支持删除分区和添加分区
5.基于范围分区的自动扩展间隔分区
间隔分区(interval-partition)是针对range类型分区的一种功能拓展。对连续数据类型的range分区,如果插入的新数据值与当前分区均不匹配,interval-partition特性可以实现自动的分区创建。分区字段必须是时间类型(date或timestamp)。
postgresql目前还不支持该语法,opengauss已经支持。
opengauss间隔分区例子
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'))
);
查看系统表可看到分区策略为“i”,interval分区。
omm=# select relname,partstrategy,interval from pg_partition where relname='tab_range_interval';
relname | partstrategy | interval
---------- -------------- -----------
tab_part | i | {"1 month"}
(1 row)
接下来插入三条数据
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');
插入数据后检查是否自动创建了相应的分区
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为系统自动生成的分区。
注意:
1.从2.0.0版本开始,模板库默认字符集由sql_ascii改为了utf8,同时数据库兼容性由oracle改为pg,对本测试的影响是date数据类型。
2.目前只支持interval-range,其它方式不支持。
3.间隔分区字段必须是时间类型(date或timestamp)。
6.子分区
opengauss目前还不支持子分区,pg声明式分区是支持子分区。
pg子分区例子
先创建分区表
create table sales(
id serial,
sales_count int,
sales_date date not null
) partition by range(sales_date);
再按年创建分区: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年新建分区时可以创建子分区,按月分区
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表,可以看到分区sales_2021比其它分区多一个partitioned属性,说明它是一个子分区。
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')
总结
1.opengauss目前只支持声明式分区,支持范围分区、列表分区、哈希分区以及interval-range的自动扩展间隔分区。postgresql支持继承及声明式分区,不支持自动扩展间隔分区。
2.自动扩展间隔分区的分区字段目前只支持时间类型(date或timestamp)。
3.opengauss2.0目前不支持子分区(2.1.0开始支持子分区),pg支持声明式子分区。
4.对于声明式分区的分区来说,分区必须具有和分区表正好相同的列集合,表结构必须严格一致,而在表继承中,子表可以有父表中没有出现过的额外列,同时表继承允许多继承。