数据库管理
二、默认参数创建数据库
postgres=# create database tbase_db;CREATE DATABASE
三、指定克隆库
postgres=# create database tbase_db_template TEMPLATE template0;CREATE DATABASE
四、指定所有者
postgres=# create role pgxz with login;CREATE ROLEpostgres=# 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 | 分组规则 |
21、删除数据库
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 usersDETAIL: There is 1 other session using the database.
可以使用with (force)参数强制删除数据库：
postgres=# drop database tbase_db_template with(force);DROP DATABASE
模式管理
模式本质上是一个名字空间，在不同的数据库系统中有不同的叫法。在 Oracle 中通常称为用户，在 SQL Server 中称为框架，在 MySQL 中称为数据库。模式中可以包含表、数据类型、函数以及操作符。对象名称可以在不同模式中重名，访问特定模式中的对象时，可以使用“模式名.对象名”的格式。
22、创建模式
·标准语句：
postgres=# create schema tbase; CREATE SCHEMA
·扩展语法，当模式不存在时才创建：
postgres=# create schema if not exists tbase;NOTICE: schema "tbase" already exists, skippingCREATE SCHEMA
·指定模式的所属用户：
postgres=# create schema tbase_pgxz AUTHORIZATION pgxz;CREATE SCHEMA
使用 \\dn 命令列出模式及其所有者：
List of schemasName | Owner————+——-tbase_pgxz | pgxz(1 row)
23、修改模式属性
·修改模式名：
postgres=# alter schema tbase rename to tbase_new;ALTER SCHEMA
·修改所有者：
postgres=# alter schema tbase_pgxz owner to tbase;ALTER SCHEMA
24、删除模式
·删除模式命令：
postgres=# drop schema tbase_new;DROP SCHEMA
·当模式中存在对象时，删除会失败，并提示错误信息。可以使用 CASCADE 选项强制删除及其依赖对象：
postgres=# drop schema tbase_pgxz CASCADE;NOTICE: drop cascades to table tbase_pgxz.tDROP SCHEMA
25、配置用户访问模式权限
普通用户访问某个模式下的对象需要两个授权步骤：访问对象本身和访问模式。
·授权用户访问特定表：
postgres=# grant select on tbase.t to pgxz;GRANT
·授权用户使用特定模式：
postgres=# grant USAGE on SCHEMA tbase to pgxz;GRANT
26、配置访问模式的顺序
TCHouse-P 数据库使用 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 existpostgres=# select * from tbase_schema.t1;id | mc—-+—-(0 rows)
创建和删除数据表
27、不指定 shard key 建表方式
不指定 shard key 建表时，系统默认使用第一个字段作为表的 shard key。
postgres=# create table t_first_col_share(id serial not null, nickname text);CREATE TABLEpostgres=# \\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 来做分布键。
·总之一定要让数据尽可能的分布得足够散。
28、指定 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 TABLEpostgres=# \\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
29、指定 group 建表方式
指定 group 建表方式通过to group group_name语句指定。
postgres=# create table t (id integer, nc text) distribute by shard(id) to group default_group;CREATE TABLEpostgres=# \\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
31、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 操作。
32、默认分区表
没有默认分区表时插入会出错。
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 TABLEpostgres=# insert into t_native_range values(2,'2017-08-01',2);INSERT 0 1
33、MAXVALUE 分区
创建MAXVALUE分区。
postgres=# CREATE TABLE t_native_range_maxvalue PARTITION OF t_native_range for values from ('2017-11-01') to (maxvalue);CREATE TABLEpostgres=# insert into t_native_range values(1,'2017-11-01',1);INSERT 0 1
所有比2017-11-01大的数据都存储到子表t_native_range_maxvalue。
34、MINVALUE 分区
创建 MINVALUE 分区。
postgres=# CREATE TABLE t_native_range_minvalue PARTITION OF t_native_range for values from (minvalue) to ('2017-09-01');CREATE TABLEpostgres=# insert into t_native_range values(1,'2017-08-01',1);INSERT 0 1
35、查看表结构
查看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
36、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
37、创建 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 TABLEpostgres=# insert into t_native_list values(1,'上海',1,current_date);INSERT 0 1
38、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)
40、创建主表
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
41、创建二级表
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
42、创建三级表
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
TBase 支持1级和2级分区混用，分区不需要都平级。
43、数据库复制表
复制表在所有 dn 节点都存储一份相同的数据。创建复制表后，可以插入数据，并在不同节点上查询到相同的结果。复制表不支持触发器和外键。
44、创建复制表
CREATE TABLE t_rep (id INT, mc TEXT) DISTRIBUTE BY REPLICATION TO GROUP default_group;
45、插入数据
INSERT INTO t_rep VALUES(1, 'TBase'), (2, 'pgxz');
46、查询数据
EXECUTE DIRECT ON (dn001) 'SELECT * FROM t_rep';EXECUTE DIRECT ON (dn002) 'SELECT * FROM t_rep';
47、列存表管理
列存表只在 TBase-v3 中支持。
48、创建列存表
CREATE TABLE t_col_test (f1 INT, f2 VARCHAR(32), f3 DATE) WITH (orientation='column');
49、查看列存表结构
\\d+ t_col_test
50、指定压缩类型
·所有列使用统一的压缩级别：
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);
52、创建主表
CREATE TABLE t_native_range ( f1 BIGINT, f2 TIMESTAMP DEFAULT NOW(), f3 INTEGER) PARTITION BY RANGE (f2) WITH (orientation=column, compression=low);
53、创建子表
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);
54、查看表结构
\\d+ t_native_range
55、列存 Stash 表
列存表的 Stash 功能允许将单条插入或更新操作的数据首先存储在 Stash 中，然后根据设置将数据持久化存储至列存储结构。
56、创建 Stash 表
CREATE TABLE t_stash (f1 INT, f2 INT) WITH (orientation=column, stash_enabled=on);
57、修改表为 Stash 表
ALTER TABLE t_stash SET (stash_enabled=off);
59、创建 hdfs_fdw 插件
CREATE EXTENSION hdfs_fdw;
61、创建 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');
62、创建 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');
63、创建融合桶 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');
64、创建 Postgres Server
CREATE SERVER postgres_fdw_postgres_db FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host '172.16.64.14', port '11345', dbname 'postgres');
65、配置 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;
67、创建非分区表
CREATE FOREIGN TABLE test_csv( id INT, name TEXT)SERVER $ServerNameOPTIONS ( FORMAT 'csv', FOLDERNAME '$数据目录/', distribute 'shard');
68、创建 TEXT 格式的表
CREATE FOREIGN TABLE test_text( id INT, name TEXT)SERVER $ServerNameOPTIONS ( FORMAT 'text', DELIMITER '$列分隔符', FOLDERNAME '$数据目录/', distribute 'shard');
69、创建 ORC 格式的表
CREATE FOREIGN TABLE orc_table( a BIGINT, b TEXT, c FLOAT)SERVER $ServerNameOPTIONS ( FORMAT 'orc', FOLDERNAME '$数据目录/', distribute 'shard');
70、创建分区表
CREATE FOREIGN TABLE login_logs_parquet( l_id TEXT, l_loginName TEXT, l_date TEXT, year TEXT, month TEXT)SERVER cosn_serverOPTIONS ( FORMAT 'parquet', FOLDERNAME '$数据目录/', distribute 'shard', PARTITION 'year,month');
71、查询外表并将数据导入内部表
INSERT INTO 内部表 SELECT * FROM 外部表;
73、创建 dblink
CREATE DATABASE LINK abcCONNECT TO "TBASE"IDENTIFIED BY 'tbase2018'USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora12c) ))';
74、查询 dblink 系统表
SELECT * FROM pg_dblink;
75、访问远程表
SELECT * FROM t1@abc;
76、删除 dblink
DROP DATABASE LINK abc;
78、创建 postgres_fdw
CREATE EXTENSION postgres_fdw;
79、创建 dblink
SET dblink_types = 'postgresql';CREATE DATABASE LINK tbase_dblinkCONNECT 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))';
80、查询 dblink 系统表
SELECT * FROM pg_dblink WHERE dblinkname='tbase_dblink';
81、访问远程表
SELECT * FROM t2@tbase_dblink;
82、删除 dblink
DROP DATABASE LINK tbase_dblink;
83、使用 IF NOT EXISTS 创建表
CREATE TABLE IF NOT EXISTS t(id INT, mc TEXT);
84、指定模式创建表
CREATE TABLE public.t(id INT, mc TEXT);
85、使用查询结果创建数据表
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;
86、删除数据表
DROP TABLE t;DROP TABLE public.t;DROP TABLE IF EXISTS t;DROP TABLE tbase_schema.t1 CASCADE;
87、删除分区子表
找到分区子表名，直接删除子表。
DROP TABLE t_time_range_part_1;
创建和删除索引
88、普通索引
postgres=# create index t_appoint_id_idx on t_appoint_col using btree(id); CREATE INDEX
89、唯一索引
创建唯一索引
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.
90、表达式索引
创建表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');
91、条件索引
创建表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 ='女';
92、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);
注意：仅行存表支持
94、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);
注意：仅行存表支持
95、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);
96、数组索引
创建表 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);
97、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);
98、多字段索引
创建一个包含多个字段的索引，可以提高查询性能，尤其是在使用`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);
99、多字段索引使用注意事项
·当使用OR查询条件时，Bitmap 扫描最多支持两个不同字段的条件。
100、查询计划示例
·查询使用多个字段的索引：
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;
101、索引与性能
·如果查询的返回字段全部在索引中，可以实现 Index Only Scan，减少 I/O 开销。
·在插入操作中，多字段索引的性能通常优于单字段索引。
102、删除索引
DROP INDEX t_appoint_id_idx;
修改表结构
103、修改表名
postgres=# ALTER TABLE t RENAME TO tbase;
104、给表或字段添加注释
postgres=# COMMENT ON TABLE tbase IS 'TBase 分布式关系型数据库系统';postgres=# COMMENT ON COLUMN tbase.nickname IS 'TBase 呢称是大象';
105、给表增加字段
postgres=# ALTER TABLE tbase ADD COLUMN age INTEGER;
106、修改字段类型
postgres=# ALTER TABLE tbase ALTER COLUMN age TYPE FLOAT8;
107、修改字段默认值
postgres=# ALTER TABLE tbase ALTER COLUMN age SET DEFAULT 0.0;
108、删除字段
postgres=# ALTER TABLE tbase DROP COLUMN age;
109、添加主键
postgres=# ALTER TABLE t ADD CONSTRAINT t_id_pkey PRIMARY KEY (id);
110、删除主键
postgres=# ALTER TABLE t DROP CONSTRAINT t_id_pkey;
111、重建主键
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;
112、添加外键
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);
113、删除外键
postgres=# ALTER TABLE t_f DROP CONSTRAINT t_f_f1_fkey;
114、修改表所属模式
postgres=# ALTER TABLE t SET SCHEMA public;
115、修改表所属用户
postgres=# ALTER TABLE tbase OWNER TO pgxz;
116、修改字段名
postgres=# ALTER TABLE tbase RENAME COLUMN city TO cityname;
117、修改表的填充率
postgres=# ALTER TABLE t1 SET (fillfactor=80);
119、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();
120、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();
121、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();
122、多个事件触发器
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();
123、删除触发器
postgres=# DROP TRIGGER t_trigger_insert_update_trigger ON t_trigger_mulevent;postgres=# DROP FUNCTION t_trigger_mulevent_func();
视图创建和删除
124、创建视图
postgres=# create view t_range_view as select * from t_range;CREATE VIEWpostgres=# 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 VIEWpostgres=# 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 VIEWpostgres=# 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_viewView "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 TABLETime: 62.875 mspostgres=# alter table t_new rename mc to mc_new;ALTER TABLETime: 22.081 mspostgres=# \\d+ t_viewView "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;
125、删除视图
postgres=# drop table t;DROP TABLEpostgres=# create table t (id int,mc text);CREATE TABLEpostgres=# create view t_view as select * from t;CREATE VIEWpostgres=# create view t_view_1 as select * from t_view;CREATE VIEWpostgres=# create view t_view_2 as select * from t_view; CREATE VIEWpostgres=# drop view t_view_2;DROP VIEW#使用cascade强制删除依赖对象postgres=# drop view t_view; ERROR: cannot drop view t_view because other objects depend on itDETAIL: view t_view_1 depends on view t_viewHINT: Use DROP … CASCADE to drop the dependent objects too.postgres=# drop view t_view cascade;NOTICE: drop cascades to view t_view_1DROP VIEW