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

 

一、对于null 值处理

1、Oracle

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

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 分区

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