腾讯云数据仓库 TCHouse-P 库/模式/表/索引/视图/物化视图等 DDL 操作-开发指南-文档中心-腾讯云

数据库管理
创建数据库
要创建一个数据库，你必须是一个超级用户或者具有特殊的 CREATEDB 特权，默认情况下，新数据库将通过克隆标准系统数据库 template1 被创建。可以通过写 TEMPLATE name 指定一个不同的模板。特别地，通过写 TEMPLATE template0 你可以创建一个干净的数据库，它将只包含你的 TBase 版本所预定义的标准对象。
默认参数创建数据库
postgres=# create database tbase_db;
CREATE DATABASE
指定克隆库
postgres=# create database tbase_db_template TEMPLATE template0;
CREATE DATABASE
指定所有者
postgres=# create role pgxz with login;
CREATE ROLE
postgres=# create database tbase_db_owner owner pgxz;
CREATE DATABASE
列出数据库及其详细信息：
List of databases
| Name             | Owner | Encoding | Collate  | Ctype  | Access privileges | Size  | Tablespace | Description      |
|-----------------|-------|----------|----------|--------|------------------|-------|------------|-------------------|
| tbase_db_owner   | pgxz  | UTF8     | en_US.utf8 | en_US.utf8 |                  | 18 MB | pg_default |
指定编码
postgres=# create database tbase_db_encoding ENCODING UTF8;
CREATE DATABASE
列出数据库及其详细信息：
List of databases
| Name                  | Owner   | Encoding | Collate  | Ctype  | Access privileges | Size  | Tablespace | Description      |
|----------------------|--------|----------|----------|--------|------------------|-------|------------|-------------------|
| tbase_db_encoding    | tbase  | UTF8     | en_US.utf8 | en_US.utf8 |                  | 18 MB | pg_default |
创建 GBK 编码数据库
postgres=# CREATE DATABASE db_gbk template template0 encoding = gbk LC_COLLATE = 'zh_CN.gbk' LC_CTYPE = 'zh_CN.gbk';
CREATE DATABASE
列出数据库及其详细信息：
List of databases
| Name  | Owner | Encoding | Collate  | Ctype  | Access privileges | Size  | Tablespace | Description      |
|-------|-------|----------|----------|--------|------------------|-------|------------|-------------------|
| db_gbk | tbase | GBK      | zh_CN.gbk | zh_CN.gbk |                  | 19 MB | pg_default |
创建 GB18030 编码数据库
postgres=# create database db_gb18030 template=template0 encoding=gb18030 LC_COLLATE = 'zh_CN.gb18030' LC_CTYPE = 'zh_CN.gb18030';
CREATE DATABASE
列出数据库及其详细信息：
List of databases
| Name        | Owner | Encoding | Collate        | Ctype          | Access privileges |
|-------------|-------|----------|---------------|---------------|------------------|
| db_gb18030  | tbase | GB18030  | zh_CN.gb18030  | zh_CN.gb18030  |
...
指定排序规则
postgres=# create database tbase_db_lc_collate lc_collate 'C';
CREATE DATABASE
列出数据库及其详细信息：
List of databases
| Name                  | Owner | Encoding | Collate | Ctype | Access privileges | Size  | Tablespace | Description      |
|----------------------|-------|----------|---------|-------|-------------------|-------|------------|-------------------|
| tbase_db_lc_collate  | tbase | UTF8     | C       | en_US.utf8 |                  | 18 MB | pg_default |
指定分组规则
postgres=# create database tbase_db_lc_ctype LC_CTYPE 'C' ;
CREATE DATABASE
列出数据库及其详细信息：
List of databases
| Name               | Owner | Encoding | Collate  | Ctype | Access privileges | Size  | Tablespace | Description      |
|--------------------|-------|----------|----------|-------|------------------|-------|------------|-------------------|
| tbase_db_lc_ctype | tbase | UTF8     | en_US.utf8 | C     |                  | 18 MB | pg_default |
配置数据可连接
postgres=# create database tbase_db_allow_connections ALLOW_CONNECTIONS true;
CREATE DATABASE
查看数据库连接配置：
postgres=# select datallowconn from pg_database where datname='tbase_db_allow_connections';
datallowconn
--------------
t
配置连接数
postgres=# create database tbase_db_connlimit CONNECTION LIMIT 100;
CREATE DATABASE
查看数据库连接数配置：
postgres=# select datconnlimit from pg_database where datname='tbase_db_connlimit';
datconnlimit
--------------
100
配置数据库可以被复制
postgres=# create database tbase_db_istemplate is_template true;
CREATE DATABASE
查看数据库模板配置：
postgres=# select datistemplate from pg_database where datname='tbase_db_istemplate';
datistemplate
---------------
t
多个参数一起配置
postgres=# create database tbase_db_mul owner pgxz CONNECTION LIMIT 50 template template0 encoding 'utf8' lc_collate 'C';
CREATE DATABASE
修改数据库配置
修改数据库名称
postgres=# alter database tbase_db rename to tbase_db_new;
ALTER DATABASE
修改连接数
postgres=# alter database tbase_db_new connection limit 50;
ALTER DATABASE
修改数据库所有者
postgres=# alter database tbase_db_new owner to tbase;
ALTER DATABASE
配置数据默认运行参数
postgres=# alter database tbase_db_new set search_path to public,pg_catalog,pg_oracle;      
ALTER DATABASE
更多set的用法可以参考运维文档。
Alter database 不支持的项目
项目
备注
encoding
编码
lc_collate
排序规则
lc_ctype
分组规则
删除数据库
删除数据库
postgres=# drop database tbase_db_new;
DROP DATABASE
删除数据库时，如果该数据库已经有 session 连接上来，则会提示如下错误：
postgres=# drop database tbase_db_template;
ERROR:  database "tbase_db_template" is being accessed by other users
DETAIL:  There is 1 other session using the database.
使用下面方法可以把 session 断开，然后再删除：
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname='tbase_db_template';    
 pg_terminate_backend 
----------------------
 t
(1 row)
再次尝试删除数据库：
postgres=# drop database tbase_db_template;
DROP DATABASE
﻿
请注意，Markdown格式的表格在渲染时可能不会显示垂直边框，但上述内容符合Markdown语法规则。如果需要进一步的调整，请告知。 
﻿
模式管理
模式本质上是一个名字空间，在不同的数据库系统中有不同的叫法。在 Oracle 中通常称为用户，在 SQL Server 中称为框架，在 MySQL 中称为数据库。模式中可以包含表、数据类型、函数以及操作符。对象名称可以在不同模式中重名，访问特定模式中的对象时，可以使用“模式名.对象名”的格式。
创建模式
标准语句：
postgres=# create schema tbase;
  CREATE SCHEMA
扩展语法，当模式不存在时才创建：
postgres=# create schema if not exists tbase;
NOTICE: schema "tbase" already exists, skipping
CREATE SCHEMA
指定模式的所属用户：
postgres=# create schema tbase_pgxz AUTHORIZATION pgxz;
CREATE SCHEMA
使用 \\dn 命令列出模式及其所有者：
List of schemas
Name | Owner
------------+-------
tbase_pgxz | pgxz
(1 row)
修改模式属性
修改模式名：
postgres=# alter schema tbase rename to tbase_new;
ALTER SCHEMA
修改所有者：
postgres=# alter schema tbase_pgxz owner to tbase;
ALTER SCHEMA
删除模式
删除模式命令：
postgres=# drop schema tbase_new;
DROP SCHEMA
当模式中存在对象时，删除会失败，并提示错误信息。可以使用 CASCADE 选项强制删除及其依赖对象：
postgres=# drop schema tbase_pgxz CASCADE;
NOTICE: drop cascades to table tbase_pgxz.t
DROP SCHEMA
配置用户访问模式权限
普通用户访问某个模式下的对象需要两个授权步骤：访问对象本身和访问模式。
授权用户访问特定表：
postgres=# grant select on tbase.t to pgxz;
GRANT
授权用户使用特定模式：
postgres=# grant USAGE on SCHEMA tbase to pgxz;
GRANT
配置访问模式的顺序
TBase 数据库使用 search_path 运行变量来配置访问数据对象的顺序。
当前连接用户的搜索路径：
postgres=# show search_path ;
search_path
-----------------
"$user", public
(1 row)
创建数据表时，如果不指定模式，则表存放于第一个搜索模式下：
postgres=# create table t(id int,mc text);
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
访问不在搜索路径中的对象时，需要写全路径：
postgres=# select * from t1;
ERROR: relation "t1" does not exist
postgres=# select * from tbase_schema.t1;
id | mc
----+----
(0 rows)
创建和删除数据表
不指定 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
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 建表方式
指定 shard key 建表时，可以通过distribute by shard(column_name)语句指定。
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
指定 group 建表方式
指定 group 建表方式通过to group group_name语句指定。
postgres=# create table t (id integer, nc text) distribute by shard(id) to group default_group;
CREATE TABLE
postgres=# \\d+ t
Table "public.t"
Column
Type
Modifiers
Storage
Stats target
Description
id
integer
﻿
plain
﻿
﻿
nc
text
﻿
extended
﻿
﻿
Has OIDs: no  
Distribute By SHARD(id)  
Location Nodes: ALL DATANODES
逻辑分区表
range 分区表
创建主分区和子分区。
postgres=# create table t_native_range (f1 bigint, f2 timestamp default now(), f3 integer) partition by range(f2) distribute by shard(f1) to group default_group;
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');
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');
注意添加子分区时会影响主表的数据 DML 操作。
默认分区表
没有默认分区表时插入会出错。
postgres=# insert into t_native_range values(2,'2017-08-01',2);
ERROR: no partition of relation "t_native_range" found for row
添加默认分区表：
postgres=# CREATE TABLE t_native_range_default PARTITION OF t_native_range DEFAULT;
CREATE TABLE
postgres=# insert into t_native_range values(2,'2017-08-01',2);
INSERT 0 1
MAXVALUE 分区
创建MAXVALUE分区。
postgres=# CREATE TABLE t_native_range_maxvalue PARTITION OF t_native_range for values from ('2017-11-01') to (maxvalue);
CREATE TABLE
postgres=# insert into t_native_range values(1,'2017-11-01',1);
INSERT 0 1
所有比2017-11-01大的数据都存储到子表t_native_range_maxvalue。
MINVALUE 分区
创建 MINVALUE 分区。
postgres=# CREATE TABLE t_native_range_minvalue PARTITION OF t_native_range for values from (minvalue) to ('2017-09-01');
CREATE TABLE
postgres=# insert into t_native_range values(1,'2017-08-01',1);
INSERT 0 1
查看表结构
查看t_native_range表结构。
postgres=# \\d+ t_native_range
Table "tbase_pg_proc.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: ...  
Distribute By: SHARD(f1)  
Location Nodes: ALL DATANODES
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) to group default_group;
建立两个子表，分别存入“广东”和“北京”。
postgres=# create table t_list_gd partition of t_native_list(f1, f2, f3, f4) for values in ('广东');
postgres=# create table t_list_bj partition of t_native_list(f1, f2, f3, f4) for values in ('北京');
查看表结构：
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: ...  
Distribute By: SHARD(f1)  
Location Nodes: ALL DATANODES
创建 default 分区
没有 default 分区情况下会出错，插入会出错。
postgres=# insert into t_native_list values(1,'上海',1,current_date);
ERROR: no partition of relation "t_native_list" found for row
创建后就能正常插入。
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
Hash 分区表
创建4个子分区的 hash 表时，需要指定分区的个数。使用分区数作为算子来计算每条数据所在的分区。目前，hash 分区不支持添加和删除操作。
postgres=# create table t_hash_partition(f1 int, f2 int) partition by hash(f2);
create table t_hash_partition_1 partition of t_hash_partition FOR VALUES WITH(MODULUS 4, REMAINDER 0);
create table t_hash_partition_2 partition of t_hash_partition FOR VALUES WITH(MODULUS 4, REMAINDER 1);
create table t_hash_partition_3 partition of t_hash_partition FOR VALUES WITH(MODULUS 4, REMAINDER 2);
create table t_hash_partition_4 partition of t_hash_partition FOR VALUES WITH(MODULUS 4, REMAINDER 3);
插入数据：
postgres=# insert into t_hash_partition values(1,1),(2,2),(3,3);
COPY 3
查询结果：
postgres=# select * from t_hash_partition;
+----+----+
| f1 | f2 |
+----+----+
|  1 |  1 |
|  3 |  3 |
|  2 |  2 |
+----+----+
(3 rows)
TBase 自动根据分区值进行剪枝：
postgres=# explain select * from t_hash_partition where f2=2;
                                    QUERY PLAN                                    
-------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Append  (cost=0.00..26.88 rows=7 width=8)
         ->  Seq Scan on t_hash_partition_3  (cost=0.00..26.88 rows=7 width=8)
               Filter: (f2 = 2)
(5 rows)
多级分区表
创建主表
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) to group default_group;
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
TBase 支持1级和2级分区混用，分区不需要都平级。
数据库复制表
复制表在所有 dn 节点都存储一份相同的数据。创建复制表后，可以插入数据，并在不同节点上查询到相同的结果。复制表不支持触发器和外键。
 创建复制表
CREATE TABLE t_rep (id INT, mc TEXT) DISTRIBUTE BY REPLICATION TO GROUP default_group;
插入数据
INSERT INTO t_rep VALUES(1, 'TBase'), (2, 'pgxz');
查询数据
EXECUTE DIRECT ON (dn001) 'SELECT * FROM t_rep';
EXECUTE DIRECT ON (dn002) 'SELECT * FROM t_rep';
列存表管理
列存表只在 TBase-v3 中支持。
创建列存表
CREATE TABLE t_col_test (f1 INT, f2 VARCHAR(32), f3 DATE) WITH (orientation='column');
查看列存表结构
\\d+ t_col_test
指定压缩类型
所有列使用统一的压缩级别：
CREATE TABLE t_col1 (f1 INT, f2 INT, f3 DATE) WITH (orientation=column, compression=HIGH);
配置默认压缩级别：
SHOW default_rel_compression;
SET default_rel_compression TO 'middle';
设置不同列不同压缩级别：
CREATE TABLE t_col3 (
  f1 INT ENCODING(compression=no),
  f2 INT ENCODING(compression=low),
  f3 INT ENCODING(compression=middle),
  f4 INT ENCODING(compression=high)
) WITH (orientation=column);
列存分区表
创建主表
CREATE TABLE t_native_range (
  f1 BIGINT,
  f2 TIMESTAMP DEFAULT NOW(),
  f3 INTEGER
) PARTITION BY RANGE (f2) WITH (orientation=column, compression=low);
创建子表
CREATE TABLE t_native_range_201709 PARTITION OF t_native_range FOR VALUES FROM ('2017-09-01') TO ('2017-10-01') WITH (orientation=column, compression=low);
CREATE TABLE t_native_range_201710 PARTITION OF t_native_range FOR VALUES FROM ('2017-10-01') TO ('2017-11-01') WITH (orientation=column, compression=low);
查看表结构
\\d+ t_native_range
列存 Stash 表
列存表的 Stash 功能允许将单条插入或更新操作的数据首先存储在 Stash 中，然后根据设置将数据持久化存储至列存储结构。
创建 Stash 表
CREATE TABLE t_stash (f1 INT, f2 INT) WITH (orientation=column, stash_enabled=on);
修改表为 Stash 表
ALTER TABLE t_stash SET (stash_enabled=off);
外表管理
创建 hdfs_fdw 插件
CREATE EXTENSION hdfs_fdw;
创建 Server
创建 COS Server
CREATE SERVER $cos_server_name
 FOREIGN DATA WRAPPER hdfs_fdw
 OPTIONS (
  address 'cos://$bucketname',
  appid '$appid',
  access_keyid '$ak',
  secret_accesskey '$sk',
  region '$region',
  client_type 'cos'
);
创建 HDFS Server
CREATE SERVER $hdfs_server_name
 FOREIGN DATA WRAPPER hdfs_fdw
 OPTIONS (
  address 'ofs://xxxxxx.chdfs.ap-guangzhou.myqcloud.com',
  appid '$appid',
  client_type 'hdfs'
);
创建融合桶 Server
CREATE SERVER $cosn_server_name
 FOREIGN DATA WRAPPER hdfs_fdw
 OPTIONS (
  address 'cosn://$bucketname',
  appid '$appid',
  access_keyid '$ak',
  secret_accesskey '$sk',
  region '$region',
  client_type 'cosn'
);
创建 Postgres Server
CREATE SERVER postgres_fdw_postgres_db
 FOREIGN DATA WRAPPER postgres_fdw
 OPTIONS (
  host '172.16.64.14',
  port '11345',
  dbname 'postgres'
);
配置 User Mapping
CREATE USER MAPPING FOR $TDSQL-A_USER SERVER $cos_server_name;
CREATE USER MAPPING FOR $TDSQL-A_USER SERVER $hdfs_server_name;
CREATE USER MAPPING FOR $TDSQL-A_USER SERVER $cosn_server_name;
创建外表
创建非分区表
CREATE FOREIGN TABLE test_csv(
  id INT,
  name TEXT
)
SERVER $ServerName
OPTIONS (
  FORMAT 'csv',
  FOLDERNAME '$数据目录/',
  distribute 'shard'
);
创建 TEXT 格式的表
CREATE FOREIGN TABLE test_text(
  id INT,
  name TEXT
)
SERVER $ServerName
OPTIONS (
  FORMAT 'text',
  DELIMITER '$列分隔符',
  FOLDERNAME '$数据目录/',
  distribute 'shard'
);
创建 ORC 格式的表
CREATE FOREIGN TABLE orc_table(
  a BIGINT,
  b TEXT,
  c FLOAT
)
SERVER $ServerName
OPTIONS (
  FORMAT 'orc',
  FOLDERNAME '$数据目录/',
  distribute 'shard'
);
创建分区表
CREATE FOREIGN TABLE login_logs_parquet(
  l_id TEXT,
  l_loginName TEXT,
  l_date TEXT,
  year TEXT,
  month TEXT
)
SERVER cosn_server
OPTIONS (
  FORMAT 'parquet',
  FOLDERNAME '$数据目录/',
  distribute 'shard',
  PARTITION 'year,month'
);
查询外表并将数据导入内部表
INSERT INTO 内部表 SELECT * FROM 外部表;
dblink 外表
创建 dblink
CREATE DATABASE LINK abc
CONNECT TO "TBASE"
IDENTIFIED BY 'tbase2018'
USING '(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.17)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ora12c)
  )
)';
查询 dblink 系统表
SELECT * FROM pg_dblink;
访问远程表
SELECT * FROM t1@abc;
删除 dblink
DROP DATABASE LINK abc;
tbase_dblink
创建 postgres_fdw
CREATE EXTENSION postgres_fdw;
创建 dblink
SET dblink_types = 'postgresql';
CREATE DATABASE LINK tbase_dblink
CONNECT TO "tbase"
IDENTIFIED BY 'tbase@2017'
USING '(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.23)(PORT = 11379))
  (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = postgres)(SCHEMA_NAME = public))
  (COLLATE = YES)(DEFAULT = YES)(NOT_NULL = YES)
)';
查询 dblink 系统表
SELECT * FROM pg_dblink WHERE dblinkname='tbase_dblink';
访问远程表
SELECT * FROM t2@tbase_dblink;
删除 dblink
DROP DATABASE LINK tbase_dblink;
使用 IF NOT EXISTS 创建表
CREATE TABLE IF NOT EXISTS t(id INT, mc TEXT);
指定模式创建表
CREATE TABLE public.t(id INT, mc TEXT);
使用查询结果创建数据表
CREATE TABLE t(id INT, mc TEXT) DISTRIBUTE BY SHARD(mc);
INSERT INTO t VALUES(1, 'tbase');
CREATE TABLE t_as AS SELECT * FROM t;
删除数据表
DROP TABLE t;
DROP TABLE public.t;
DROP TABLE IF EXISTS t;
DROP TABLE tbase_schema.t1 CASCADE;
删除分区子表
DROP TABLE t_time_range_part_1;
添加分区子表
从最后添加分区表子表
ALTER TABLE t1_pt ADD PARTITIONS 2;
添加中间的分区表子表
CREATE TABLE t_time_range_part_1 (
  f1 BIGINT NOT NULL,
  f2 TIMESTAMP WITHOUT TIME ZONE,
  f3 BIGINT
);
ALTER TABLE t_time_range_part_1 ADD CONSTRAINT t_time_range_pkey_part_1 PRIMARY KEY (f1);
CREATE INDEX t_time_range_f2_idx_part_1 ON t_time_range_part_1(f2);
INSERT INTO t_time_range VALUES(1, '2017-10-1', 1);
SELECT * FROM t_time_range;
创建和删除索引
普通索引
postgres=# create index t_appoint_id_idx on t_appoint_col using btree(id); 
CREATE INDEX
唯一索引
创建唯一索引
﻿
postgres=# create unique index t_first_col_share_id_uidx on t_first_col_share using btree(id);
CREATE INDEX
非 shard key 字段不能建立唯一索引
postgres=# create unique index t_first_col_share_nickname_uidx on t_first_col_share using btree(nickname);
错误信息：
ERROR: Unique index of partitioned table must contain the hash/modulo distribution column.
表达式索引
创建表t_upper
postgres=# create table t_upper(id int,mc text);
创建索引t_upper_mc
postgres=# create index t_upper_mc on t_upper(mc);
插入数据并分析
postgres=# insert into t_upper select t,md5(t::text) from generate_series(1,10000) as t;
postgres=# analyze t_upper;
查询计划，使用表达式索引。
postgres=# explain select * from t_upper where upper(mc)=md5('1');
条件索引
创建表t_sex
postgres=# create table t_sex(id int,sex text);
创建索引t_sex_sex_idx
postgres=# create index t_sex_sex_idx on t_sex (sex);
插入数据并分析
postgres=# insert into t_sex select t,'男' from generate_series(1,1000000) as t;
postgres=# insert into t_sex select t,'女' from generate_series(1,100) as t;
postgres=# analyze t_sex;
查询计划，使用条件索引。
postgres=# explain select * from t_sex where sex ='女';
gist 索引
创建表 t_trgm 并创建 gist 索引
postgres=# create table t_trgm (id int,trgm text,no_trgm text);
postgres=# create index t_trgm_trgm_idx on t_trgm using gist(trgm gist_trgm_ops);
注意：仅行存表支持
gin 索引
pg_trgm 索引
删除索引 t_trgm_trgm_idx
postgres=# drop index t_trgm_trgm_idx;
创建 gin 索引 t_trgm_trgm_idx
postgres=# create index t_trgm_trgm_idx on t_trgm using gin(trgm gin_trgm_ops);
注意：仅行存表支持
jsonb 索引
创建表 t_jsonb 并创建 jsonb 索引
postgres=# create table t_jsonb(id int,f_jsonb jsonb);
postgres=# create index t_jsonb_f_jsonb_idx on t_jsonb using gin(f_jsonb);
数组索引
创建表 t_array 并插入数据
postgres=# create table t_array(id int, mc text[]);
postgres=# insert into t_array select t,('{'||md5(t::text)||'}')::text[] from generate_series(1,1000000) as t;
创建数组索引 t_array_mc_idx
postgres=# create index t_array_mc_idx on t_array using gin(mc);
Btree_gin 任意字段索引
创建表 gin_mul 并插入数据
postgres=# create table gin_mul(f1 int, f2 int, f3 timestamp, f4 text, f5 numeric, f6 text);
postgres=# insert into gin_mul select random()*5000, random()*6000, now()+((3000060000*random())||' sec')::interval , md5(random()::text), round((random()*100000)::numeric,2), md5(random()::text) from generate_series(1,1000000);
创建 btree_gin 扩展并创建索引 gin_mul_gin_idx
postgres=# create extension btree_gin;
postgres=# create index gin_mul_gin_idx on gin_mul using gin(f1,f2,f3,f4,f5,f6);
多字段索引
创建一个包含多个字段的索引，可以提高查询性能，尤其是在使用`OR`查询条件时。但需要注意，Bitmap扫描最多只支持两个不同字段的条件。
CREATE TABLE t_mul_idx (f1 int, f2 int, f3 int, f4 int);
CREATE INDEX t_mul_idx_idx ON t_mul_idx(f1, f2, f3);
多字段索引使用注意事项
当使用OR查询条件时，Bitmap 扫描最多支持两个不同字段的条件。
查询计划示例
查询使用多个字段的索引：
EXPLAIN SELECT * FROM t_mul_idx WHERE f1=1 OR f2=2;
当查询条件超过两个字段时，将使用Seq Scan而非Bitmap Heap Scan：
EXPLAIN SELECT * FROM t_mul_idx WHERE f1=1 OR f2=2 OR f3=3;
索引与性能
如果查询的返回字段全部在索引中，可以实现 Index Only Scan，减少 I/O 开销。
在插入操作中，多字段索引的性能通常优于单字段索引。
全局索引
全局索引（Global Index）用于解决 MPP 分布式数据库中的数据定位问题，特别是在缺少分布键的情况下。
支持特性
普通 shard 表全局索引：版本 >= 5.06.2
分区表全局索引：版本 >= 5.06.3
分布式全局索引：版本 >= 5.06.3
创建全局索引
CREATE TABLE t1 (f1 int, f2 int);
CREATE GLOBAL INDEX t1_f2_idx ON t1(f2);
查看索引类型
\\d+ t1
全局索引执行计划
EXPLAIN SELECT * FROM t1 WHERE f2=1;
全局唯一索引
CREATE GLOBAL UNIQUE INDEX t1_f2_gidx ON t1(f2);
全局索引不支持的功能
不支持truncate操作。
不支持reindex全局索引。
不支持CONCURRENTLY创建全局索引。
不支持在带有全局索引的表上执行vacuum full。
删除索引
DROP INDEX t_appoint_id_idx;
修改表结构
修改表名
postgres=# ALTER TABLE t RENAME TO tbase;
给表或字段添加注释
postgres=# COMMENT ON TABLE tbase IS 'TBase 分布式关系型数据库系统';
postgres=# COMMENT ON COLUMN tbase.nickname IS 'TBase 呢称是大象';
给表增加字段
postgres=# ALTER TABLE tbase ADD COLUMN age INTEGER;
修改字段类型
postgres=# ALTER TABLE tbase ALTER COLUMN age TYPE FLOAT8;
修改字段默认值
postgres=# ALTER TABLE tbase ALTER COLUMN age SET DEFAULT 0.0;
删除字段
postgres=# ALTER TABLE tbase DROP COLUMN age;
添加主键
postgres=# ALTER TABLE t ADD CONSTRAINT t_id_pkey PRIMARY KEY (id);
删除主键
postgres=# ALTER TABLE t DROP CONSTRAINT t_id_pkey;
重建主键
postgres=# CREATE UNIQUE INDEX CONCURRENTLY t_id_temp_idx ON t (id);
postgres=# ALTER TABLE t DROP CONSTRAINT t_pkey, ADD CONSTRAINT t_pkey PRIMARY KEY USING INDEX t_id_temp_idx;
添加外键
CREATE TABLE t_p (f1 INT NOT NULL, f2 INT, PRIMARY KEY (f1));
CREATE TABLE t_f (f1 INT NOT NULL, f2 INT);
postgres=# ALTER TABLE t_f ADD CONSTRAINT t_f_f1_fkey FOREIGN KEY (f1) REFERENCES t_p (f1);
删除外键
postgres=# ALTER TABLE t_f DROP CONSTRAINT t_f_f1_fkey;
修改表所属模式
postgres=# ALTER TABLE t SET SCHEMA public;
修改表所属用户
postgres=# ALTER TABLE tbase OWNER TO pgxz;
修改字段名
postgres=# ALTER TABLE tbase RENAME COLUMN city TO cityname;
修改表的填充率
postgres=# ALTER TABLE t1 SET (fillfactor=80);
添加触发器
INSERT 触发器
postgres=# CREATE OR REPLACE FUNCTION t_trigger_insert_trigger_func() RETURNS TRIGGER AS $body$
BEGIN
  IF NEW.f2 < 0 THEN
    NEW.f2 := 0;
  END IF;
  RETURN NEW;
END;
$body$
LANGUAGE plpgsql;
﻿
postgres=# CREATE TRIGGER t_trigger_insert_trigger BEFORE INSERT ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_insert_trigger_func();
UPDATE 触发器
postgres=# CREATE OR REPLACE FUNCTION t_trigger_update_trigger_func() RETURNS TRIGGER AS $body$
BEGIN
  IF NEW.f2 < 0 THEN
    NEW.f2 := OLD.f2;
  END IF;
  RETURN NEW;
END;
$body$
LANGUAGE plpgsql;
﻿
postgres=# CREATE TRIGGER t_trigger_update_trigger BEFORE UPDATE ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_update_trigger_func();
DELETE 触发器
postgres=# CREATE OR REPLACE FUNCTION t_trigger_delete_trigger_func() RETURNS TRIGGER AS $body$
BEGIN
  IF OLD.f2 = 0 THEN
    RETURN NULL;
  END IF;
  RETURN OLD;
END;
$body$
LANGUAGE plpgsql;
﻿
postgres=# CREATE TRIGGER t_trigger_delete_trigger BEFORE DELETE ON t_trigger FOR EACH ROW EXECUTE PROCEDURE t_trigger_delete_trigger_func();
多个事件触发器
postgres=# CREATE TABLE t_trigger_mulevent(f1 INT, f2 INT);
CREATE OR REPLACE FUNCTION t_trigger_mulevent_func() RETURNS TRIGGER AS $body$
BEGIN
  IF NEW.f2 < 0 THEN
    NEW.f2 := 0;
  END IF;
  RETURN NEW;
END;
$body$
LANGUAGE plpgsql;
﻿
postgres=# CREATE TRIGGER t_trigger_insert_update_trigger BEFORE INSERT OR UPDATE ON t_trigger_mulevent FOR EACH ROW EXECUTE PROCEDURE t_trigger_mulevent_func();
删除触发器
postgres=# DROP TRIGGER t_trigger_insert_update_trigger ON t_trigger_mulevent;
postgres=# DROP FUNCTION t_trigger_mulevent_func();
视图创建和删除
创建视图
postgres=# create view t_range_view as select * from t_range;
CREATE VIEW
postgres=# select * from t_range_view;
f1
f2
f3
f4
1
2017-09-27 23:17:39.674318
1
﻿
2
2017-09-27 23:17:39.674318
50
﻿
2
2017-09-27 23:17:39.674318
110
﻿
1
2017-09-27 23:39:45.841093
151
﻿
3
2017-09-27 23:17:39.674318
100
﻿
(5 rows)
﻿
数据类型重定义
postgres=# create view t_range_view as select f1,f2::date from t_range;
CREATE VIEW
postgres=# select * from t_range_view;
f1
f2
1
2017-09-27
2
2017-09-27
2
2017-09-27
1
2017-09-27
3
2017-09-27
(5 rows)
﻿
数据类型重定义,以及取别名。
postgres=# create view t_range_view as select f1,f2::date as mydate from t_range;
CREATE VIEW
postgres=# select * from t_range_view;
f1
mydate
1
2017-09-27
2
2017-09-27
2
2017-09-27
1
2017-09-27
3
2017-09-27
(5 rows)
﻿
tbase 支持视图引用表或字段改名联动，不受影响。
postgres=# \\d+ t_view
View "tbase.t_view"
 Column | Type | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+----------+-------------
 id     | integer |           |          |         | plain   |
 mc     | text    |           |          |         | extended |
View definition:
 SELECT t.id,
 t.mc
 FROM t;
﻿
postgres=# alter table t rename to t_new;
ALTER TABLE
Time: 62.875 ms
﻿
postgres=# alter table t_new rename mc to mc_new;
ALTER TABLE
Time: 22.081 ms
﻿
postgres=# \\d+ t_view
View "tbase.t_view"
 Column | Type | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+----------+-------------
 id     | integer |           |          |         | plain   |
 mc     | text    |           |          |         | extended |
View definition:
 SELECT t_new.id,
 t_new.mc_new AS mc
 FROM t_new;
﻿
﻿
项目	备注
encoding	编码
lc_collate	排序规则
lc_ctype	分组规则
Column	Type	Modifiers	Storage	Stats target	Description
id	integer	not null default nextval('t_first_col_share_id_seq'::regclass)	plain
nickname	text		extended
Column	Type	Collation	Nullable	Default	Storage	Stats target	Description
f1	bigint				plain
f2	timestamp without time zone			now()	plain
f3	integer				plain
f1	f2	f3	f4
1	2017-09-27 23:17:39.674318	1
2	2017-09-27 23:17:39.674318	50
2	2017-09-27 23:17:39.674318	110
1	2017-09-27 23:39:45.841093	151
3	2017-09-27 23:17:39.674318	100
f1	mydate
1	2017-09-27
2	2017-09-27
2	2017-09-27
1	2017-09-27
3	2017-09-27
库/模式/表/索引/视图/物化视图等 DDL 操作

本页目录：

数据库管理

创建数据库

默认参数创建数据库

指定克隆库

指定所有者

指定编码

创建 GBK 编码数据库

创建 GB18030 编码数据库

指定排序规则

指定分组规则

配置数据可连接

配置连接数

配置数据库可以被复制

多个参数一起配置

修改数据库配置

修改数据库名称

修改连接数

修改数据库所有者

配置数据默认运行参数

Alter database 不支持的项目

删除数据库

删除数据库

模式管理

创建模式

修改模式属性

删除模式

配置用户访问模式权限

配置访问模式的顺序

创建和删除数据表

不指定 shard key 建表方式

指定 shard key 建表方式

指定 group 建表方式

逻辑分区表

range 分区表

默认分区表

MAXVALUE 分区

MINVALUE 分区

查看表结构

list 分区表

创建 default 分区

Hash 分区表

多级分区表

创建主表

创建二级表

创建三级表

数据库复制表

创建复制表

插入数据

查询数据

列存表管理

创建列存表

查看列存表结构

指定压缩类型

列存分区表

创建主表

创建子表

查看表结构

列存 Stash 表

创建 Stash 表

修改表为 Stash 表

外表管理

创建 hdfs_fdw 插件

创建 Server

创建 COS Server

创建 HDFS Server

创建融合桶 Server

创建 Postgres Server

配置 User Mapping

创建外表

创建非分区表

创建 TEXT 格式的表

创建 ORC 格式的表

创建分区表

查询外表并将数据导入内部表

dblink 外表

创建 dblink

查询 dblink 系统表

访问远程表
