TDSQL PostgreSQL版创建和删除数据表-开发指南-文档中心-腾讯云

不用指定 shard key 建表方式
不指定 shard key 建表方法，系统默认使用第一个字段作为表的 shard key。
postgres=# create table t_first_col_share(id serial not null,nickname text);
CREATE TABLE
postgres=# \\d+ t_first_col_share
                       Table "public.t_first_col_share"
 Column  |  Type  |              Modifiers               | Storage  | Stats target | Description 
----------+---------+----------------------------------------------------------------+----------+--------------+-------------
 id    | integer | not null default nextval('t_first_col_share_id_seq'::regclass) | plain   |        | 
 nickname | text   |                                 | extended |        | 
Has OIDs: no
Distribute By SHARD(id)
    Location Nodes: ALL DATANODES
分布键选择原则：
分布键只能选择一个字段。
如果有主键，则选择主键做分布键。
如果主键是复合字段组合，则选择字段值选择性多的字段做分布键。
也可以把复合字段拼接成一个新的字段来做分布键。
没有主键的可以使用 UUID 来做分布键。
总之一定要让数据尽可能的分布得足够散。
指定 shard key 建表方式
postgres=# create table t_appoint_col(id serial not null,nickname text) distribute by shard(nickname);
CREATE TABLE
postgres=# \\d+ t_appoint_col
                       Table "public.t_appoint_col"
 Column  |  Type  |             Modifiers              | Storage  | Stats target | Description 
----------+---------+------------------------------------------------------------+----------+--------------+-------------
 id    | integer | not null default nextval('t_appoint_col_id_seq'::regclass) | plain   |        | 
 nickname | text   |                               | extended |        | 
Has OIDs: no
Distribute By SHARD(nickname)
    Location Nodes: ALL DATANODES
创建范围分区表
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 TABLE
postgres=# insert into t_range(f1,f3) values(1,1),(2,50),(3,100),(2,110);
INSERT 0 4
创建时间范围分区表
postgres=#  create table t_time_range
(f1 bigint, f2 timestamp ,f3 bigint) 
partition by range (f2) begin (timestamp without time zone '2017-09-01 0:0:0') 
step (interval '1 month') 
partitions (12) distribute by shard(f1) 
CREATE TABLE
﻿
postgres=# \\d+ t_time_range 
                Table "public.t_time_range"
 Column |       Type       | Modifiers | Storage | Stats target | Description 
--------+-----------------------------+-----------+---------+--------------+-------------
 f1   | bigint            |      | plain  |        | 
 f2   | timestamp without time zone |      | plain  |        | 
 f3   | bigint            |      | plain  |        | 
Has OIDs: no
Distribute By SHARD(f1)
    Location Nodes: ALL DATANODES
Partition By: RANGE(f2)
  \\# Of Partitions: 12
  Start With: 2017-09-01
  Interval Of Partition: 1 MONTH
字段说明：
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=# 
查看表结构
 postgres=# \\d+ t_native_range
                    Table "tdsql_pg.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 分区
说明:
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 row
DETAIL:  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 TABLE
postgres=# 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
﻿
查看表结构
postgres=# \\d+ t_native_list
                       Table "tdsql_pg.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 分区
说明
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 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=#
多级分区表
创建主表
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 TABLE
﻿
postgres=# 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 TABLE
﻿
postgres=# 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 TABLE
﻿
postgres=# 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 TABLE
﻿
postgres=# 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 TABLE
﻿
postgres=# 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
postgres=# EXECUTE DIRECT ON (dn001) 'select * from t_rep';
 id |  mc  
----+-------
 1 | tdsql_pg
 2 | pgxz
(2 rows)
postgres=# EXECUTE DIRECT ON (dn002) 'select * from t_rep'; 
 id |  mc  
----+-------
 1 | tdsql_pg
 2 | pgxz
(2 rows)
可以看到所有节点都保存了一份相同的数据。
使用 IF NOT EXISTS
带 IF NOT EXISTS 关键字作用表示表不存在时才创建。
postgres=# create table t(id int,mc text);        
CREATE TABLE
postgres=# create table t(id int,mc text);
ERROR:  relation "t" already exists
postgres=# create table IF NOT EXISTS t(id int,mc text);
NOTICE:  relation "t" already exists, skipping
CREATE TABLE
postgres=# 
指定模式创建表
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 TABLE
postgres=# insert into t values(1,'tdsql_pg');
INSERT 0 1
postgres=#  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 1
postgres=# select * from t_as;
 id |  mc  
----+-------
 1 | tdsql_pg
(1 row)
查看 t 表的表结构
 postgres=# \\d+ t
                    Table "tdsql_pg.t"
  Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description 
 --------+---------+-----------+----------+---------+----------+--------------+-------------
  id   | integer |      |      |     | plain   |        | 
  mc   | text   |      |      |     | extended |        | 
 Distribute By: SHARD(mc)
 Location Nodes: ALL DATANODES
查看 t_as 表的表结构
 postgres=# \\d+ t_as
                   Table "tdsql_pg.t_as"
  Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description 
 --------+---------+-----------+----------+---------+----------+--------------+-------------
  id   | integer |      |      |     | plain   |        | 
  mc   | text   |      |      |     | extended |        | 
 Distribute By: SHARD(id)
 Location Nodes: ALL DATANODES
﻿
 postgres=#  
删除数据表
删除当前模式下的数据表。
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, skipping
DROP TABLE
使用 CASCADE 无条件删除数据表。
postgres=# create view tdsql_pg_schema.t1_view as select * from tdsql_pg_schema.t1 ;
CREATE VIEW
postgres=# drop table tdsql_pg_schema.t1 ;
ERROR:  cannot drop table tdsql_pg_schema.t1 because other objects depend on it
DETAIL:  view tdsql_pg_schema.t1_view depends on table tdsql_pg_schema.t1
HINT:  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_view
DROP TABLE
postgres=# 
添加分区子表
postgres=# \\d+ t1_pt
                       Table "public.t1_pt"
 Column |       Type       | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 f1   | integer           |      | not null |     | plain   |        | 
 f2   | timestamp without time zone |      | not null |     | plain   |        | 
 f3   | character varying(20)    |      |      |     | extended |        | 
Indexes:
  "t1_pt_pkey" PRIMARY KEY, btree (f1)
Distribute By: SHARD(f1)
Location Nodes: dn01
Partition By: RANGE(f2)
     # Of Partitions: 3
     Start With: 2019-01-01
     Interval Of Partition: 1 MONTH
﻿
postgres=# ALTER TABLE t1_pt ADD PARTITIONS 2;   
﻿
ALTER TABLE
postgres=# \\d+ t1_pt
                       Table "public.t1_pt"
 Column |       Type       | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 f1   | integer           |      | not null |     | plain   |        | 
 f2   | timestamp without time zone |      | not null |     | plain   |        | 
 f3   | character varying(20)    |      |      |     | extended |        | 
Indexes:
  "t1_pt_pkey" PRIMARY KEY, btree (f1)
Distribute By: SHARD(f1)
Location Nodes: dn01
Partition By: RANGE(f2)
     # Of Partitions: 5
     Start With: 2019-01-01
     Interval Of Partition: 1 MONTH
﻿
创建和删除数据表

本页目录：

不用指定 shard key 建表方式

指定 shard key 建表方式

创建范围分区表

创建时间范围分区表

逻辑分区表

range 分区表

list 分区表

多级分区表

复制表

使用 IF NOT EXISTS

指定模式创建表

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

删除数据表

添加分区子表
