TDSQL PostgreSQL版创建和删除数据表

一、不用指定 shard key 建表方式

分布键选择原则：

·分布键只能选择一个字段。

·如果有主键，则选择主键做分布键。

·如果主键是复合字段组合，则选择字段值选择性多的字段做分布键。

·也可以把复合字段拼接成一个新的字段来做分布键。

·没有主键的可以使用 UUID 来做分布键。

·总之一定要让数据尽可能的分布得足够散。

二、指定 shard key 建表方式

三、创建范围分区表

postgres=#create table t_range (f1 bigint,f2 timestamp default now(), f3 integer) partition by range (f3) begin (1) step (50) partitions (3) distribute by shard(f1);CREATE TABLEpostgres=# insert into t_range(f1,f3) values(1,1),(2,50),(3,100),(2,110);INSERT 0 4

四、创建时间范围分区表

字段说明：

·partition by range (x) 用于指定分区键，支持 timesamp,int 类型，数据分布于那个子表就是根据这个字段值来计算分区。

·begin(xxx) 指定开始分区的时间点。

·step(xxx) 指定分区有周期。

·partions(xx) 初始化时建立分区子表个数。

·增加分区子表的方法 ALTER TABLE public.t1_pt ADD PARTITIONS 2;

五、range 分区表

范围分区的表被分区到由列定义的范围中，不同分区的值范围之间没有重叠。通常可以按时间进行分区，或者有特定对象的范围进行分区。其操作如下：

·创建主分区

postgres=# create table t_native_range (f1 bigint,f2 timestamp default now(), f3 integer) partition by range ( f2 ) distribute by shard(f1); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLE

·建立两个子表

postgres=# create table t_native_range_201709 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-09-01') to ('2017-10-01'); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command. CREATE TABLE postgres=# create table t_native_range_201710 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-10-01') to ('2017-11-01'); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command. CREATE TABLE postgres=#

·查看表结构

·创建 default 分区

注意：说明:

注意：TDSQL PostgreSQL 版（Oracle 兼容版）支持。

不创建 default 分区，插入范围越界出错。

postgres=# insert into t_native_range values(1,'2016-09-01',1);ERROR: node:dn001, backend_pid:19011, nodename:dn001,backend_pid:19011,message:no partition of relation "t_native_range" found for rowDETAIL: Partition key of the failing row contains (f2) = (2016-09-01 00:00:00).

创建 default 分区后能正常插入数据。

postgres=# CREATE TABLE t_native_range_default PARTITION OF t_native_range DEFAULT; CREATE TABLE postgres=# insert into t_native_range values(1,'2016-09-01',1); INSERT 0 1

六、list 分区表

表格通过明确的键值进行分区。

·创建主分区

postgres=# create table t_native_list(f1 bigserial not null,f2 text, f3 integer,f4 date) partition by list( f2 ) distribute by shard(f1); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLE

·建立两个子表，分别存入“广东”和“北京”

postgres=# create table t_list_gd partition of t_native_list(f1 ,f2 , f3,f4) for values in ('广东'); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# create table t_list_bj partition of t_native_list(f1 ,f2 , f3,f4) for values in ('北京'); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLE

·查看表结构

·创建 default 分区

注意：TDSQL PostgreSQL 版（Oracle 兼容版）支持。

没有 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 rowDETAIL: 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 1postgres=#

七、多级分区表

创建主表

postgres=# 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);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLE

创建二级表

postgres=# create table t_native_mul_list_gd partition of t_native_mul_list for values in ('广东') partition by range(f5); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# create table t_native_mul_list_bj partition of t_native_mul_list for values in ('北京') partition by range(f5); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# create table t_native_mul_list_sh partition of t_native_mul_list for values in ('上海');NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLE

创建三级表

postgres=# 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'); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# 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');NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# 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'); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# 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'); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLE

TDSQL PostgreSQL 版支持存在1级和2级分区混用，大家不需要都平级。

更多分区表的内容可参考如下文档。

八、复制表

复制表是所有 DN 节点都存储一份相同的数据。

postgres=# create table t_rep (id int,mc text) distribute by replication; CREATE TABLE postgres=# insert into t_rep values(1,'tdsql_pg'),(2,'pgxz'); INSERT 0 2

可以看到所有节点都保存了一份相同的数据。

九、使用 IF NOT EXISTS

带 IF NOT EXISTS 关键字作用表示表不存在时才创建。

postgres=# create table t(id int,mc text); CREATE TABLEpostgres=# create table t(id int,mc text);ERROR: relation "t" already existspostgres=# create table IF NOT EXISTS t(id int,mc text);NOTICE: relation "t" already exists, skippingCREATE TABLEpostgres=#

十、指定模式创建表

postgres=# create table public.t(id int,mc text);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLE

十一、使用将查询结果创建数据表

创建 t 表

postgres=# create table t(id int,mc text) distribute by shard(mc);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# insert into t values(1,'tdsql_pg');INSERT 0 1postgres=# create table t_as as select * from t;NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.INSERT 0 1postgres=# select * from t_as; id | mc —-+——- 1 | tdsql_pg(1 row)

查看 t 表的表结构

查看 t_as 表的表结构

十二、删除数据表

删除当前模式下的数据表。

postgres=# drop table t;DROP TABLE

删除某个模式下数据表。

postgres=# drop table public.t;DROP TABLE

删除数据表，不存在时不执行，不报错。

postgres=# drop table IF EXISTS t;NOTICE: table "t" does not exist, skippingDROP TABLE

使用 CASCADE 无条件删除数据表。

postgres=# create view tdsql_pg_schema.t1_view as select * from tdsql_pg_schema.t1 ;CREATE VIEWpostgres=# drop table tdsql_pg_schema.t1 ;ERROR: cannot drop table tdsql_pg_schema.t1 because other objects depend on itDETAIL: view tdsql_pg_schema.t1_view depends on table tdsql_pg_schema.t1HINT: Use DROP … CASCADE to drop the dependent objects too.postgres=# drop table tdsql_pg_schema.t1 CASCADE;NOTICE: drop cascades to view tdsql_pg_schema.t1_viewDROP TABLEpostgres=#

十三、添加分区子表

TDSQL PostgreSQL版 创建和删除数据表