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

库/模式/表/索引/视图/物化视图等 DDL 操作

最近更新时间:2024-07-26 11:44:21

我的收藏

数据库管理

创建数据库

要创建一个数据库,您必须是一个超级用户或者具有特殊的 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;