列表分区使用

• 复制
  复制成功

postgres=# create table t_native_list(f1 bigserial not null,f2 text, f3 integer,f4 date) partition by list( f2 ) distribute by shard(f1) to group default_group;   
CREATE TABLE

• 复制
  复制成功

postgres=# create table t_list_gd partition of t_native_list(f1 ,f2 , f3,f4) for values in ('广东'); 
CREATE TABLE

postgres=# create table t_list_bj partition of t_native_list(f1 ,f2 , f3,f4) for values in ('北京');  
CREATE TABLE

• 复制
  复制成功

postgres=# \d+ t_native_list
                                                Table "tbase.t_native_list"
 Column | Type   | Collation | Nullable|                  Default                  | Storage  | Stats target | Description 
--------+---------+-----------+----------+-------------------------------------------+----------+--------------+-------------
 f1     |bigint  |           | not null |nextval('t_native_list_f1_seq'::regclass) | plain    |              | 
 f2     |text    |           |          |                                           |extended |              | 
 f3     |integer |           |          |                                           |plain    |              | 
 f4     |date    |           |         |                                           |plain    |              | 
Partition key: LIST (f2)
Partitions: t_list_bj FOR VALUES IN ('北京'),
           t_list_gd FOR VALUES IN ('广东')
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES

postgres=# 

• 复制
  复制成功

#没有default分区情况下会出错，插入会出错

postgres=# insert into t_native_list values(1,'上海',1,current_date);
ERROR:  node:dn001, backend_pid:31664, nodename:dn001,backend_pid:31664,message:no partition of relation "t_native_list" found for row
DETAIL:  Partition key of the failing row contains (f2) = (上海).

postgres=# CREATE TABLE t_native_list_default PARTITION OF t_native_list DEFAULT;           
CREATE TABLE

#创建后就能正常插入
postgres=# insert into t_native_list values(1,'上海',1,current_date);
INSERT 0 1
postgres=#