KingbaseES 分区表与 Oracle 分区表对于空值的处理差异

 

一、对于null 值处理

1、Oracle

分区字段允许为空，只要存在maxvalue 分区，值就可以插入。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SQL> create table t1(id number,data varchar(9)) partition by range(id)   2  (   3    partition p1 values less than (1000),   4    partition p2 values less than (maxvalue)   5  );   Table created.   SQL> insert into t1(data) values('a');   1 row created.   SQL> commit;   Commit complete.   SQL> select * from t1 partition(p2);           ID DATA ---------- ---------            a

2、KingbaseES

允许分区列值为空，但需要 Default 分区

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

test=# create table t1(id integer,name text) partition by range(id); CREATE TABLE test=# create table t1_p1 partition of t1 for values from (minvalue) to (1000); CREATE TABLE test=# create table t1_p2 partition of t1 for values from (1000) to (maxvalue); CREATE TABLE test=# test=# insert into t1(name) values('a'); ERROR:  no partition of relation "t1" found for row DETAIL:  Partition key of the failing row contains (id) = (null).   test=# create table t1_default partition of t1 default; CREATE TABLE test=# insert into t1(name) values('a'); INSERT 0 1