range分区表
-
创建主分区
teledb=# create table t_native_range (f1 bigint,f2 timestamp default now(), f3 integer) partition by range ( f2 ) distribute by shard(f1); CREATE TABLE -
建立两个子表
teledb=# create table t_native_range_201709 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-09-01') to ('2017-10-01'); CREATE TABLE teledb=# create table t_native_range_201710 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-10-01') to ('2017-11-01'); CREATE TABLE -
查看表结构
teledb=# \d+ t_native_range Table "public.t_native_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------------+-----------+----------+---------+---------+--------------+------------- f1 | bigint | | | | plain | | f2 | timestamp without time zone | | | now() | plain | | f3 | integer | | | | plain | | Partition key: RANGE (f2) Partitions: t_native_range_201709 FOR VALUES FROM ('2017-09-01 00:00:00') TO ('2017-10-01 00:00:00'), t_native_range_201710 FOR VALUES FROM ('2017-10-01 00:00:00') TO ('2017-11-01 00:00:00') Distribute By: SHARD(f1) Location Nodes: ALL DATANODES -
创建default分区不创建default 分区，插入范围越界出错。
teledb=# insert into t_native_range values(1,'2016-09-01',1); ERROR: node:dn01, backend_pid:39912, nodename:dn01,backend_pid:39912,message:no partition of relation "t_native_range" found for row DETAIL: Partition key of the failing row contains (f2) = (2016-09-01 00:00:00).创建default 分区后能正常插入数据。
teledb=# CREATE TABLE t_native_range_default PARTITION OF t_native_range DEFAULT; CREATE TABLE teledb=# insert into t_native_range values(1,'2016-09-01',1); INSERT 0 1
list分区表
-
创建主分区
teledb=# create table t_native_list(f1 bigserial not null,f2 text, f3 integer,f4 date) partition by list( f2 ) distribute by shard(f1); CREATE TABLE -
建立两个子表，分别存入“广东”和“北京”
teledb=# create table t_list_gd partition of t_native_list(f1 ,f2 , f3,f4) for values in ('广东'); CREATE TABLE teledb=# create table t_list_bj partition of t_native_list(f1 ,f2 , f3,f4) for values in ('北京'); CREATE TABLE -
看表结构
teledb=# \d+ t_native_list Table "public.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 | timestamp(0) without time zone | | | | 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 -
创建default 分区没有default 分区情况下会出错，插入会出错。
teledb=# insert into t_native_list values(1,'上海',1,current_date); ERROR: node:dn01, backend_pid:40092, nodename:dn01,backend_pid:40092,message:no partition of relation "t_native_list" found for row DETAIL: Partition key of the failing row contains (f2) = (上海).创建后就能正常插入。
teledb=# CREATE TABLE t_native_list_default PARTITION OF t_native_list DEFAULT; CREATE TABLE teledb=# insert into t_native_list values(1,'上海',1,current_date); INSERT 0 1
多级分区表
-
创建主表
teledb=# create table t_native_mul_list(f1 bigserial not null,f2 integer,f3 text,f4 text, f5 date) partition by list ( f3 ) distribute by shard(f1); CREATE TABLE -
创建二级表
teledb=# create table t_native_mul_list_gd partition of t_native_mul_list for values in ('广东') partition by range(f5); CREATE TABLE teledb=# create table t_native_mul_list_bj partition of t_native_mul_list for values in ('北京') partition by range(f5); CREATE TABLE teledb=# create table t_native_mul_list_sh partition of t_native_mul_list for values in ('上海'); -
创建三级表
teledb=# create table t_native_mul_list_gd_201701 partition of t_native_mul_list_gd(f1,f2,f3,f4,f5) for values from ('2017-01-01') to ('2017-02-01'); CREATE TABLE teledb=# create table t_native_mul_list_gd_201702 partition of t_native_mul_list_gd(f1,f2,f3,f4,f5) for values from ('2017-02-01') to ('2017-03-01'); CREATE TABLE teledb=# create table t_native_mul_list_bj_201701 partition of t_native_mul_list_bj(f1,f2,f3,f4,f5) for values from ('2017-01-01') to ('2017-02-01'); CREATE TABLE teledb=# create table t_native_mul_list_bj_201702 partition of t_native_mul_list_bj(f1,f2,f3,f4,f5) for values from ('2017-02-01') to ('2017-03-01'); CREATE TABLE -
查看表结构
teledb=# \d+ t_native_mul_list Table "public.t_native_mul_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------+-----------+-------------+-----------------+-----------+---------------+------------- f1 | bigint | | not null | nextval('t_native_mul_list_f1_seq'::regclass) | plain | | f2 | integer | | | | plain | | f3 | text | | | | extended | | f4 | text | | | | extended | | f5|timestamp(0) without time zone| | | | plain | | Partition key: LIST (f3) Partitions: t_native_mul_list_bj FOR VALUES IN ('北京'), PARTITIONED, t_native_mul_list_gd FOR VALUES IN ('广东'), PARTITIONED, t_native_mul_list_sh FOR VALUES IN ('上海') Distribute By: SHARD(f1) Location Nodes: ALL DATANODES teledb=# \d+ t_native_mul_list_gd Table "public.t_native_mul_list_gd" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------------------------------+-----------+----------+-----------------------------------------------+----------+--------- -----+------------- f1 | bigint | | not null | nextval('t_native_mul_list_f1_seq'::regclass) | plain | | f2 | integer | | | | plain | | f3 | text | | | | extended | | f4 | text | | | | extended | | f5 | timestamp(0) without time zone | | | | plain | | Partition of: t_native_mul_list FOR VALUES IN ('广东') Partition constraint: ((f3 IS NOT NULL) AND (f3 = ANY (ARRAY['广东'::text]))) Partition key: RANGE (f5) Partitions: t_native_mul_list_gd_201701 FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2017-02-01 00:00:00'), t_native_mul_list_gd_201702 FOR VALUES FROM ('2017-02-01 00:00:00') TO ('2017-03-01 00:00:00') Distribute By: SHARD(f1) Location Nodes: ALL DATANODESTeleDB支持存在1级和2级分区混用，大家不需要都平级。
