华为云用户手册

语法格式 1 2 CREATE GROUP group_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [ EXPIRED ] | DISABLE }; 其中可选项action子句语法为： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 where option can be: {SYSADMIN | NOSYSADMIN} | {MONADMIN | NOMONADMIN} | {OPRADMIN | NOOPRADMIN} | {POLADMIN | NOPOLADMIN} | {AUDITADMIN | NOAUDITADMIN} | {CREATEDB | NOCREATEDB} | {USEFT | NOUSEFT} | {CREATEROLE | NOCREATEROLE} | {INHERIT | NOINHERIT} | { LOG IN | NOLOGIN} | {REPLICATION | NOREPLICATION} | {INDEPENDENT | NOINDEPENDENT} | {VCADMIN | NOVCADMIN} | {PERSISTENCE | NOPERSISTENCE} | CONNECTION LIMIT connlimit | VALID BEGIN 'timestamp' | VALID UNTIL 'timestamp' | RESOURCE POOL 'respool' | USER GROUP 'groupuser' | PERM SPACE 'spacelimit' | NODE GROUP logic_group_name | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid | DEFAULT TABLESPACE tablespace_name | PROFILE DEFAULT | PROFILE profile_name | PGUSER

现象描述 查询与销售部所有员工的信息： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 --建表 CREATE TABLE staffs (staff_id NUMBER(6) NOT NULL, first_name VARCHAR2(20), last_name VARCHAR2(25), employment_id VARCHAR2(10), section_id NUMBER(4), state_name VARCHAR2(10), city VARCHAR2(10)); CREATE TABLE sections(section_id NUMBER(4), place_id NUMBER(4), section_name VARCHAR2(20)); CREATE TABLE states(state_id NUMBER(4)); CREATE TABLE places(place_id NUMBER(4), state_id NUMBER(4)); --优化前查询 EXPLAIN SELECT staff_id,first_name,last_name,employment_id,state_name,city FROM staffs,sections,states,places WHERE sections.section_name='Sales' AND staffs.section_id = sections.section_id AND sections.place_id = places.place_id AND places.state_id = states.state_id ORDER BY staff_id; --优化后查询 CREATE INDEX loc_id_pk ON places(place_id); CREATE INDEX state_c_id_pk ON states(state_id); EXPLAIN SELECT staff_id,first_name,last_name,employment_id,state_name,city FROM staffs,sections,states,places WHERE sections.section_name='Sales' AND staffs.section_id = sections.section_id AND sections.place_id = places.place_id AND places.state_id = states.state_id ORDER BY staff_id;

创建、修改和删除角色 非三权分立时，只有系统管理员和具有CREATEROLE属性的用户才能创建、修改或删除角色。三权分立下，只有初始用户和具有CREATEROLE属性的用户才能创建、修改或删除角色。 要创建角色，请使用CREATE ROLE。 要在现有角色中添加或删除用户，请使用ALTER ROLE。 要删除角色，请使用DROP ROLE。DROP ROLE只会删除角色，并不会删除角色中的成员用户帐户。

参数说明 关键字PUBLIC表示一个隐式定义的拥有所有角色的组。 权限类别和参数说明，请参见GRANT的参数说明。 任何特定角色拥有的特权包括直接授予该角色的特权、从该角色作为其成员的角色中得到的权限以及授予给PUBLIC的权限。因此，从PUBLIC收回SELECT特权并不一定会意味着所有角色都会失去在该对象上的SELECT特权，那些直接被授予的或者通过另一个角色被授予的角色仍然会拥有它。类似地，从一个用户收回SELECT后，如果PUBLIC仍有SELECT权限，该用户还是可以使用SELECT。 指定GRANT OPTION FOR时，只撤销对该权限授权的权力，而不撤销该权限本身。 如用户A拥有某个表的UPDATE权限，及WITH GRANT OPTION选项，同时A把这个权限赋予了用户B，则用户B持有的权限称为依赖性权限。当用户A持有的权限或者授权选项被撤销时，依赖性权限仍然存在，但如果声明了CASCADE，则所有依赖性权限都被撤销。 一个用户只能撤销由它自己直接赋予的权限。例如，如果用户A被指定授权（WITH ADMIN OPTION）选项，且把一个权限赋予了用户B，然后用户B又赋予了用户C，则用户A不能直接将C的权限撤销。但是，用户A可以撤销用户B的授权选项，并且使用CASCADE。这样，用户C的权限就会自动被撤销。另外一个例子：如果A和B都赋予了C同样的权限，则A可以撤销他自己的授权选项，但是不能撤销B的，因此C仍然拥有该权限。 如果执行REVOKE的角色持有的权限是通过多层成员关系获得的，则具体是哪个包含的角色执行的该命令是不确定的。在这种场合下，建议的方法是使用SET ROLE成为特定角色，然后执行REVOKE，否则可能导致删除了不想删除的权限，或者是任何权限都没有删除。

语法格式 回收指定表或视图上权限。 1 2 3 4 5 6 7 REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFEREN CES | ALTER | DROP | COMMENT | INDEX | VACUUM }[, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收表上指定字段权限。 1 2 3 4 5 6 REVOKE [ GRANT OPTION FOR ] { {{ SELECT | INSERT | UPDATE | REFERENCES | COMMENT } ( column_name [, ...] )}[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) } ON [ TABLE ] table_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定序列上权限。 1 2 3 4 5 6 7 REVOKE [ GRANT OPTION FOR ] { { SELECT | UPDATE | ALTER | DROP | COMMENT }[, ...] | ALL [ PRIVILEGES ] } ON { [ SEQUENCE ] sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定数据库上权限。 1 2 3 4 5 6 REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定域上权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON DOMAIN domain_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定客户端加密主密钥上的权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { USAGE | DROP } [, ...] | ALL [PRIVILEGES] } ON CLIENT_MASTER_KEYS client_master_keys_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定列加密密钥上的权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { USAGE | DROP } [, ...] | ALL [PRIVILEGES]} ON COLUMN_ENCRYPTION_KEYS column_encryption_keys_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定目录上权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { READ | WRITE } [, ...] | ALL [ PRIVILEGES ] } ON DIRECTORY directory_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定外部数据源上权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN DATA WRAPPER fdw_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定外部服务器上权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON FOREIGN SERVER server_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定函数上权限。 1 2 3 4 5 6 REVOKE [ GRANT OPTION FOR ] { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定过程语言上权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE lang_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定大对象上权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON LARGE OBJECT loid [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定模式上权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定表空间上权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { CREATE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON TABLESPACE tablespace_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定类型上权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON TYPE type_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收指定子集群上权限 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE | COMPUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] } ON NODE GROUP group_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]; 回收子集群的create权限时，会默认回收usage和compute权限。 回收Data Source对象上的权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [PRIVILEGES] } ON DATA SOURCE src_name [, ...] FROM {[GROUP] role_name | PUBLIC} [, ...] [ CASCADE | RESTRICT ]; 回收directory对象的权限。 1 2 3 4 5 REVOKE [ GRANT OPTION FOR ] { { READ | WRITE } [, ...] | ALL [PRIVILEGES] } ON DIRECTORY directory_name [, ...] FROM {[GROUP] role_name | PUBLIC} [, ...] [ CASCADE | RESTRICT ]; 按角色回收角色上的权限。 1 2 3 REVOKE [ ADMIN OPTION FOR ] role_name [, ...] FROM role_name [, ...] [ CASCADE | RESTRICT ]; 回收角色上的sysadmin权限。 1 REVOKE ALL { PRIVILEGES | PRIVILEGE } FROM role_name;

注意事项 非对象所有者试图在对象上REVOKE权限，命令按照以下规则执行： 如果授权用户没有该对象上的权限，则命令立即失败。 如果授权用户有部分权限，则只撤销那些有授权选项的权限。 如果授权用户没有授权选项，REVOKE ALL PRIVILEGES形式将发出一个错误信息，而对于其他形式的命令而言，如果是命令中指定名称的权限没有相应的授权选项，该命令将发出一个警告。 不允许对表分区进行REVOKE操作，对分区表进行REVOKE操作会引起告警。

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 --以默认方式启动事务。 postgres=# START TRANSACTION; postgres=# SELECT * FROM tpcds.reason; postgres=# END; --以默认方式启动事务。 postgres=# BEGIN; postgres=# SELECT * FROM tpcds.reason; postgres=# END; --以隔离级别为READ COMMITTED，读/写方式启动事务。 postgres=# START TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE; postgres=# SELECT * FROM tpcds.reason; postgres=# COMMIT;

参数说明 WORK | TRANSACTION BEGIN格式中的可选关键字，没有实际作用。 ISOLATION LEVEL 指定事务隔离级别，它决定当一个事务中存在其他并发运行事务时它能够看到什么数据。 在事务中第一个数据修改语句（INSERT，DELETE，UPDATE，FETCH，COPY）执行之后，事务隔离级别就不能再次设置。 取值范围： READ COMMITTED：读已提交隔离级别，只能读到已经提交的数据，而不会读到未提交的数据。这是缺省值。 READ UNCOMMITTED：读未提交隔离级别，可能会读到未提交的数据。提供这个隔离级别可用于在存在某协调节点CN故障等情况下应急使用，建议这种隔离级别下仅作只读操作，避免造成数据不一致。 REPEATABLE READ： 可重复读隔离级别，仅仅看到事务开始之前提交的数据，它不能看到未提交的数据，以及在事务执行期间由其它并发事务提交的修改。 SERIALIZABLE： GaussDB 目前功能上不支持此隔离级别，等价于REPEATABLE READ。 READ WRITE | READ ONLY 指定事务访问模式（读/写或者只读）。

语法格式 格式一：START TRANSACTION格式 1 2 3 4 5 6 7 START TRANSACTION [ { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ } | { READ WRITE | READ ONLY } } [, ...] ]; 格式二：BEGIN格式 1 2 3 4 5 6 7 BEGIN [ WORK | TRANSACTION ] [ { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ } | { READ WRITE | READ ONLY } } [, ...] ];

创建表 执行如下命令创建表。 1 2 3 4 5 6 7 8 9 postgres=# CREATE TABLE customer_t1 ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ) with (orientation = column,compression=middle) distribute by hash (c_last_name); 当结果显示为如下信息，则表示创建成功。 1 CREATE TABLE 其中c_customer_sk 、c_customer_id、c_first_name和c_last_name是表的字段名，integer、char(5)、char(6)和char(8)分别是这四个字段名称的类型。

使用INSERT多行插入 如果不能使用COPY命令，而您需要进行SQL插入，可以根据情况使用多行插入。如果您使用的是列存表，一次只插入一行或几行，则数据压缩效率低下。 多行插入是通过批量进行一系列插入而提高性能。下面的示例使用一条INSERT语句向一个三列表插入三行。这仍属于少量插入，只是用来说明多行插入的语法。创建表的步骤请参考创建和管理表。 向表customer_t1中插入多行数据： 1 2 3 4 postgres=# INSERT INTO customer_t1 VALUES (68, 'a1', 'zhou','wang'), (43, 'b1', 'wu', 'zhao'), (95, 'c1', 'zheng', 'qian'); 有关更多详情和示例，请参阅INSERT 。

使用INSERT批量插入 带SELECT子句使用批量插入操作来实现高性能数据插入。 如果需要将数据或数据子集从一个表移动到另一个表，可以使用INSERT和CREATE TABLE AS 命令。 如果从指定表插入数据到当前表，例如在数据库中创建了一个表customer_t1的备份表customer_t2，现在需要将表customer_t1中的数据插入到表customer_t2中，则可以执行如下命令。 1 2 3 4 5 6 7 8 postgres=# CREATE TABLE customer_t2 ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ); postgres=# INSERT INTO customer_t2 SELECT * FROM customer_t1; 上面的示例等价于： 1 postgres=# CREATE TABLE customer_t2 AS SELECT * FROM customer_t1;

使用GDS导入数据 数据倾斜会造成查询表性能下降。对于记录数超过千万条的表，建议在执行全量数据导入前，先导入部分数据，以进行数据倾斜检查和调整分布列，避免导入大量数据后发现数据倾斜，调整成本高。详细请参见查看数据倾斜状态。 为了优化导入速度，建议拆分文件，使用多GDS进行并行导入。另外，单个导入任务可以拆分成多个导入任务并发执行导入，多个导入任务使用同一GDS时可以使用-t参数打开GDS多线程并发执行导入。GDS建议挂载在不同物理盘以及不同网卡上，避免物理IO以及网络可能出现的瓶颈。 为了确保作业的正常运行，请注意根据GDS所承担的负载和并发度，在GDS所在的物理环境上配置充足的系统资源，其中包含但不限于：内存大小、句柄数量、GDS数据目录对应磁盘的空闲空间大小。如果GDS部署于GaussDB集群外部，请确保其物理环境配置与集群内部配置对齐。 在GDS IO与网卡未达到物理瓶颈前，可以考虑在GaussDB开启SMP进行加速。SMP开启之后会对对应的GDS产生成倍的压力。需要特别说明的是：SMP自适应衡量的标准是GaussDB的CPU压力，而不是GDS所承受的压力。 GDS与GaussDB通信要求物理网络畅通，并且尽量使用万兆网。千兆网无法承载高速的数据传输压力，极易出现断连。即使用千兆网时GaussDB无法提供通信保障。满足万兆网的同时，数据磁盘组I/O性能大于GDS单核处理能力上限（约400MB/s）时，方可寻求单文件导入速率最大化。 并发导入场景，与单表导入相似，至少应保证I/O性能大于网络最大速率。 数据服务器上，建议一个Raid只布1~2个GDS。 GDS跟DN的数据比例建议在1:3至1:6之间。 为了优化列存分区表的批量插入效率，在批量插入过程中会对数据进行缓存后再批量写盘。通过GUC参数“partition_mem_batch”和“partition_max_cache_size”，可以设置缓存个数以及缓存区大小。这两个参数的值越小，列存分区表的批量插入越慢。当然，越大的缓存个数和缓存分区，会带来越多的内存消耗。

使用gsql元命令导入数据 \copy命令在任何psql客户端登录数据库成功后可以执行导入数据。与COPY命令相比较，\copy命令不是读取或写入指定文件的服务器，而是直接读取或写入文件。 这个操作不如SQL COPY命令有效，因为所有的数据必须通过客户端/服务器的连接来传递。对于大量的数据来说SQL命令可能会更好。 有关如何使用\copy命令的更多信息，请参阅使用gsql元命令导入数据。 \COPY只适合小批量，格式良好的数据导入，不会对非法字符做预处理，也无容错能力，无法适用于含有异常数据的场景。导入数据应优先选择GDS或COPY。

数据库对象位置函数 pg_relation_filenode(relation regclass) 描述：指定关系的文件节点数。 返回值类型：oid 备注：pg_relation_filenode接受一个表、索引、序列或压缩表的OID或者名称，并且返回当前分配给它的"filenode"数。文件节点是关系使用的文件名称的基本组件。对大多数表来说，结果和pg_class.relfilenode相同，但对确定的系统目录来说， relfilenode为0而且这个函数必须用来获取正确的值。如果传递一个没有存储的关系，比如一个视图，那么这个函数返回NULL。 pg_relation_filepath(relation regclass) 描述：指定关系的文件路径名。 返回值类型：text 备注：pg_relation_filepath类似于pg_relation_filenode，但是它返回关系的整个文件路径名（相对于数据库集群的数据目录PGDATA）。 get_large_table_name(relfile_node text, threshold_size_gb int8) 描述：根据表的文件编码（relfile_node）查询对应的表大小（单位为GB）是否超过阈值（threshold_size_gb），如果超过则返回模式名和表名（形式为schemaname.tablename）， 否则返回字符串’null’。 返回值类型：text pg_filenode_relation(tablespacename, relname) 描述：获取到对应的tablespace和relfilenode所对应的表名。 返回类型：regclass pg_partition_filenode(partition_oid) 描述：获取到指定分区表的oid锁对应的filenode。 返回类型：oid

数据库对象尺寸函数 数据库对象尺寸函数计算数据库对象使用的实际磁盘空间。 pg_column_size(any) 描述：存储一个指定的数值需要的字节数（可能压缩过）。 返回值类型：int 备注：pg_column_size显示用于存储某个独立数据值的空间。 1 2 3 4 5 postgres=# SELECT pg_column_size(1); pg_column_size ---------------- 4 (1 row) pg_database_size(oid) 描述：指定OID代表的数据库使用的磁盘空间。 返回值类型：bigint pg_database_size(name) 描述：指定名称的数据库使用的磁盘空间。 返回值类型：bigint 备注：pg_database_size接受一个数据库的OID或者名称，然后返回该对象使用的全部磁盘空间。 示例： 1 2 3 4 5 postgres=# SELECT pg_database_size('postgres'); pg_database_size ------------------ 51590112 (1 row) pg_relation_size(oid) 描述：指定OID代表的表或者索引所使用的磁盘空间。 返回值类型：bigint get_db_source_datasize() 描述：估算当前数据库非压缩态的数据总容量 返回值类型：bigint 备注：（1）调用该函数前需要做analyze；（2）通过估算列存的压缩率计算非压缩态的数据总容量。 示例： 1 2 3 4 5 6 7 postgres=# analyze; ANALYZE postgres=# select get_db_source_datasize(); get_db_source_datasize ------------------------ 35384925667 (1 row) pg_relation_size(text) 描述：指定名称的表或者索引使用的磁盘空间。表名称可以用模式名修饰。 返回值类型：bigint pg_relation_size(relation regclass, fork text) 描述：指定表或索引的指定分叉树（'main'，'fsm'或'vm'）使用的磁盘空间。 返回值类型：bigint pg_relation_size(relation regclass) 描述：pg_relation_size(..., 'main')的简写。 返回值类型：bigint 备注：pg_relation_size接受一个表、索引、压缩表的OID或者名称，然后返回它们的字节大小。 pg_partition_size(oid,oid) 描述：指定OID代表的分区使用的磁盘空间。其中，第一个oid为表的OID，第二个oid为分区的OID。 返回值类型：bigint pg_partition_size(text, text) 描述：指定名称的分区使用的磁盘空间。其中，第一个text为表名，第二个text为分区名。 返回值类型：bigint pg_partition_indexes_size(oid,oid) 描述：指定OID代表的分区的索引使用的磁盘空间。其中，第一个oid为表的OID，第二个oid为分区的OID。 返回值类型：bigint pg_partition_indexes_size(text,text) 描述：指定名称的分区的索引使用的磁盘空间。其中，第一个text为表名，第二个text为分区名。 返回值类型：bigint pg_indexes_size(regclass) 描述：附加到指定表的索引使用的总磁盘空间。 返回值类型：bigint pg_size_pretty(bigint) 描述：将以64位整数表示的字节值转换为具有单位的易读格式。 返回值类型：text pg_size_pretty(numeric) 描述：将以数值表示的字节值转换为具有单位的易读格式。 返回值类型：text 备注：pg_size_pretty用于把其他函数的结果格式化成一种易读的格式，可以根据情况使用KB 、MB 、GB 、TB。 pg_table_size(regclass) 描述：指定的表使用的磁盘空间，不计索引（但是包含TOAST，自由空间映射和可见性映射）。 返回值类型：bigint pg_tablespace_size(oid) 描述：指定OID代表的表空间使用的磁盘空间。 返回值类型：bigint pg_tablespace_size(name) 描述：指定名称的表空间使用的磁盘空间。 返回值类型：bigint 备注： pg_tablespace_size接受一个数据库的OID或者名称，然后返回该对象使用的全部磁盘空间。 pg_total_relation_size(oid) 描述：指定OID代表的表使用的磁盘空间，包括索引和压缩数据。 返回值类型：bigint pg_total_relation_size(regclass) 描述：指定的表使用的总磁盘空间，包括所有的索引和TOAST数据。 返回值类型：bigint pg_total_relation_size(text) 描述：指定名称的表所使用的全部磁盘空间，包括索引和压缩数据。表名称可以用模式名修饰。 返回值类型：bigint 备注：pg_total_relation_size接受一个表或者一个压缩表的OID或者名称，然后返回以字节计的数据和所有相关的索引和压缩表的尺寸。 datalength(any) 描述：计算一个指定的数据需要的字节数（不考虑数据的管理空间和数据压缩，数据类型转换等情况）。 返回值类型：int 备注：datalength用于计算某个独立数据值的空间。 示例： postgres=# SELECT datalength(1); datalength ------------ 4 (1 row) 目前支持的数据类型及计算方式见下表： 数据类型 存储空间 数值类型 整数类型 TINYINT 1 SMALLINT 2 INTEGER 4 BINARY_INTEGER 4 BIGINT 8 任意精度型 DECIMAL 每4位十进制数占两个字节，小数点前后数字分别计算 NUMERIC 每4位十进制数占两个字节，小数点前后数字分别计算 NUMBER 每4位十进制数占两个字节，小数点前后数字分别计算 序列整型 SMALLSERIAL 2 SERIAL 4 BIGSERIAL 8 浮点类型 FLOAT4 4 DOUBLE PRECISION 8 FLOAT8 8 BINARY_DOUBLE 8 FLOAT[(p)] 每4位十进制数占两个字节，小数点前后数字分别计算 DEC[(p[,s])] 每4位十进制数占两个字节，小数点前后数字分别计算 INTEGER[(p[,s])] 每4位十进制数占两个字节，小数点前后数字分别计算 布尔类型 布尔类型 BOOLEAN 1 字符类型 字符类型 CHAR n CHAR(n) n CHARACTER(n) n NCHAR(n) n VARCHAR(n) n CHARACTER 字符实际字节数 VARYING(n) 字符实际字节数 VARCHAR2(n) 字符实际字节数 NVARCHAR2(n) 字符实际字节数 TEXT 字符实际字节数 CLOB 字符实际字节数 时间类型 时间类型 DATE 8 TIME 8 TIMEZ 12 TIMESTAMP 8 TIMESTAMPZ 8 SMALLDATETIME 8 INTERVAL DAY TO SECOND 16 INTERVAL 16 RELTIME 4 ABSTIME 4 TINTERVAL 12

手动锁定和解锁帐户 若管理员发现某帐户被盗、非法访问等异常情况，可手动锁定该帐户。当管理员认为帐户恢复正常后，可手动解锁该帐户。 以手动锁定和解锁用户joe为例，用户的创建请参见用户，命令格式如下： 手动锁定 1 2 postgres=# ALTER USER joe ACCOUNT LOCK; ALTER ROLE 手动解锁 1 2 postgres=# ALTER USER joe ACCOUNT UNLOCK; ALTER ROLE

删除不再使用的帐户 当确认帐户不再使用，管理员可以删除帐户。该操作不可恢复。 当删除的用户正处于活动状态时，此会话状态不会立马断开，用户在会话状态断开后才会被完全删除。 以删除帐户joe为例，命令格式如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 postgres=# DROP USER joe CASCADE; DROP ROLE 当在多个用户多个数据库多个schema赋予权限的场景下，drop某个用户，由于该用户拥有其他用户赋予的权限，因此直接删除会产生如下报错。 postgres=# drop user test1 cascade; ERROR: role "test1" cannot be dropped because some objects depend on it 在这种场景只能通过查看系统表找到该用户被其他用户赋予的权限，并且登录该用户手动删除其赋予的权限，才能执行drop user，下面通过一个例子来详细讲解如何处理这种场景。 登录系统库，查看该用户的OID postgres=# select oid from pg_roles where rolname='test1'; oid ------- 16386 (1 row) 通过查看pg_shdepend 该视图来获取该用户被赋予的权限，可以看到该用户存在被其他用户赋予的两个权限，先处理第一个。 postgres=# select * from pg_shdepend where refobjid='16386'; dbid | classid | objid | objsubid | refclassid | refobjid | deptype | objfile -------+---------+-------+----------+------------+----------+---------+--------- 16394 | 826 | 16400 | 0 | 1260 | 16386 | a | 16394 | 2615 | 16399 | 0 | 1260 | 16386 | a | 查看pg_database 视图获取所在数据库，根据结果可知在test数据库。 postgres=# select * from pg_database where oid='16394' datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility | datacl | datfrozenxid64 ---------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+-------------------------------------- -------------+---------------- test | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 16268 | 1985 | 1663 | MYSQL | {=Tc/wangwei,wangwei=CTc/wangwei,admi n=c/wangwei} | 1985 (1 row) 登录到test数据库 [wangwei@euler_phy_194 postgres]$ gsql -p 3730 -d test gsql ((GaussDB Kernel V500R002C00 build 43ff9cd7) compiled at 2021-04-17 14:30:45 commit 0 last mr 330 debug) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. 通过pg_class 查看权限的类型，where条件的oid即是之前pg_shdepend 视图中查到的classid。 test=# select * from pg_class where oid = 826; relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | rel cudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid64 | relbucket | relbucketkey ----------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+---- ------------+--------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------+----------------- -+----------------+----------+--------------+--------------+------------+--------------+----------------+-----------+-------------- pg_default_acl | 11 | 11810 | 0 | 10 | 0 | 16063 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | t | f | p | r | 4 | 0 | t | f | f | f | f | 0 | f | f | n | 0 | {=r/wangwei} | | n | 1985 | | (1 row) 可以看到该用户被其他用户授予了表或者视图的ACL默认权限，查看pg_default_acl视图，oid即是之前pg_shdepend 中的查到的objid。 test=# select * from pg_default_acl oid = 16400;; defaclrole | defaclnamespace | defaclobjtype | defaclacl | oid ------------+-----------------+---------------+---------------------------------------+------- 16395 | 16399 | r | {wangwei=arwd/admin,test1=arwd/admin} | 16400 (1 row) 根据该视图的defaclacl字段可以看到，test1用户被admin用户赋予了默认权限，下一步我们需要找到具体授予权限的对象(哪个表或者视图）。 通过pg_namespace 查看对象的名字，其中oid即是pg_default_acl 中查到的defaclnamespace，通过视图可以看出test1用户被admin用户授予了schema test_schema_1的权限。 test=# select * from pg_namespace where oid = 16399; nspname | nspowner | nsptimeline | nspacl | in_redistribution | nspblockchain ---------------+----------+-------------+---------------------------------------+-------------------+--------------- test_schema_1 | 10 | 0 | {wangwei=UC/wangwei,admin=UC/wangwei} | n | f (1 row) 用admin登录test数据库，执行ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema_1 revoke all privileges ON TABLES from test1;收回admin用户向test1赋予的scheme 默认权限。 test=# ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema_1 revoke all privileges ON TABLES from test1; ALTER DEFAULT PRIVILEGES 下面处理第二个权限，登录系统库，查看pg_database 视图获取所在数据库，根据结果可知在test数据库。 postgres=# select * from pg_database where oid='16394' datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility | datacl | datfrozenxid64 ---------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+-------------------------------------- -------------+---------------- test | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 16268 | 1985 | 1663 | MYSQL | {=Tc/wangwei,wangwei=CTc/wangwei,admi n=c/wangwei} | 1985 (1 row) 登录到test数据库。 [wangwei@euler_phy_194 postgres]$ gsql -p 3730 -d test 查看视图pg_class ，其中oid即是pg_shdepend 表中的第二行的classid。 postgres=# select * from pg_class where oid='2615'; relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcu descrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid64 | relbucket | relbucketkey --------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+------ ----------+--------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------+------------------+ ----------------+----------+--------------+--------------+------------+--------------+----------------+-----------+-------------- pg_namespace | 11 | 11787 | 0 | 10 | 0 | 15947 | 0 | 1 | 20 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | t | f | p | r | 6 | 0 | t | f | f | f | f | 0 | f | f | n | 0 | {=r/wangwei} | | n | 1985 | | (1 row) 根据结果可以看出是pg_namespace类型，查看pg_namespace系统视图，oid即是pg_shdepend 第二行objid。 test=# select * from pg_namespace where oid='16399'; nspname | nspowner | nsptimeline | nspacl | in_redistribution | nspblockchain ---------------+----------+-------------+-------------------------------------------------------+-------------------+--------------- test_schema_1 | 10 | 0 | {wangwei=UC/wangwei,admin=UC/wangwei,test1=U/wangwei} | n | f (1 row) 通过该视图的nspacl可以看出test1用户的test_schema_1 视图被权限被wangwei用户赋权，因此通过wangwei用户登录test库，执行revoke all on schema test_schema_1 from test1,回收赋予test1用户的权限。 test=# revoke all on schema test_schema_1 from test1; REVOKE 至此test1用户的被其他用户赋予的权限已经全部清除了，接下来只要执行drop user命令就可以了。 postgres=# drop user test1 cascade; DROP ROLE

自动锁定和解锁帐户 为了保证帐户安全，如果用户输入密码次数超过一定次数（failed_login_attempts），系统将自动锁定该帐户，默认值为10。次数设置越小越安全，但是在使用过程中会带来不便。 当帐户被锁定时间超过设定值（password_lock_time），则当前帐户自动解锁，默认值为1天。时间设置越长越安全，但是在使用过程中会带来不便。 参数password_lock_time的整数部分表示天数，小数部分可以换算成时、分、秒，如：password_lock_time=1.5，表示1天零12小时。 当failed_login_attempts设置为0时，表示不限制密码错误次数。当password_lock_time设置为0时，表示即使超过密码错误次数限制导致帐户锁定，也会在短时间内自动解锁。因此，只有两个配置参数都为正数时，才可以进行常规的密码失败检查、帐户锁定和解锁操作。 这两个参数的默认值都符合安全标准，用户可以根据需要重新设置参数，提高安全等级。建议用户使用默认值。 配置failed_login_attempts和password_lock_time参数的方法请参考用户指南。 由于配置对外提供接口服务的CN数量不同，数据库在帐户锁定上提供两种模式供用户选择： 单CN模式：集群部署时配置一个CN对外提供接口服务。 多CN模式：集群部署时配置多个CN对外提供接口服务。 安全模式下单CN具备帐户锁定机制。高并发模式下提供多个CN，每个节点都具备帐户锁定机制，但各个节点的帐户锁定信息并不共享，每个节点帐户锁定是独立裁定的，在高并发的需求下，用户可以选择此模式，但要控制CN个数，以控制密码暴力破解风险。另外各节点的自动解锁时间依据的是各节点操作系统的时钟，用户集群部署时要确保各集群节点时间同步保持一致性，可以使用NTP来配置，否则会有各节点帐户解锁时间不一致的风险。

操作步骤 使用CREATE TABLE LIKE语句创建表customer_t的副本customer_t_copy。 1 postgres=# CREATE TABLE customer_t_copy (LIKE customer_t); 使用INSERT INTO…SELECT语句向副本填充原始表中的数据。 1 postgres=# INSERT INTO customer_t_copy (SELECT * FROM customer_t); 删除原始表。 1 postgres=# DROP TABLE customer_t; 使用ALTER TABLE语句将副本重命名为原始表名称。 1 postgres=# ALTER TABLE customer_t_copy RENAME TO customer_t;

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 --SELECT语句，用一个游标读取一个表。开始一个事务。 postgres=# START TRANSACTION; --建立一个名为cursor1的游标。 postgres=# CURSOR cursor1 FOR SELECT * FROM tpcds.customer_address ORDER BY 1; --抓取头3行到游标cursor1里。 postgres=# FETCH FORWARD 3 FROM cursor1; ca_address_sk | ca_address_id | ca_street_number | ca_street_name | ca_street_type | ca_suite_number | ca_city | ca_county | ca_state | ca_zip | ca_country | ca_gmt_offset | ca_location_type ---------------+------------------+------------------+--------------------+-----------------+-----------------+-----------------+-----------------+----------+------------+---------------+---------------+---------------------- 1 | AAAAAAAABAAAAAAA | 18 | Jackson | Parkway | Suite 280 | Fairfield | Maricopa County | AZ | 86192 | United States | -7.00 | condo 2 | AAAAAAAACAAAAAAA | 362 | Washington 6th | RD | Suite 80 | Fairview | Taos County | NM | 85709 | United States | -7.00 | condo 3 | AAAAAAAADAAAAAAA | 585 | Dogwood Washington | Circle | Suite Q | Pleasant Valley | York County | PA | 12477 | United States | -5.00 | single family (3 rows) --关闭游标并提交事务。 postgres=# CLOSE cursor1; --结束一个事务。 postgres=# END; --VALUES子句，用一个游标读取VALUES子句中的内容。开始一个事务。 postgres=# START TRANSACTION; --建立一个名为cursor2的游标。 postgres=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1; --抓取头2行到游标cursor2里。 postgres=# FETCH FORWARD 2 FROM cursor2; column1 | column2 ---------+--------- 0 | 3 1 | 2 (2 rows) --关闭游标并提交事务。 postgres=# CLOSE cursor2; --结束一个事务。 postgres=# END; --WITH HOLD游标的使用，开启事务。 postgres=# START TRANSACTION; --创建一个with hold游标。 postgres=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.customer_address ORDER BY 1; --抓取头2行到游标cursor1里。 postgres=# FETCH FORWARD 2 FROM cursor1; ca_address_sk | ca_address_id | ca_street_number | ca_street_name | ca_street_type | ca_suite_number | ca_city | ca_county | ca_state | ca_zip | ca_country | ca_gmt_offset | ca_location_type ---------------+------------------+------------------+--------------------+-----------------+-----------------+-----------------+-----------------+----------+------------+---------------+---------------+---------------------- 1 | AAAAAAAABAAAAAAA | 18 | Jackson | Parkway | Suite 280 | Fairfield | Maricopa County | AZ | 86192 | United States | -7.00 | condo 2 | AAAAAAAACAAAAAAA | 362 | Washington 6th | RD | Suite 80 | Fairview | Taos County | NM | 85709 | United States | -7.00 | condo (2 rows) --结束事务。 postgres=# END; --抓取下一行到游标cursor1里。 postgres=# FETCH FORWARD 1 FROM cursor1; ca_address_sk | ca_address_id | ca_street_number | ca_street_name | ca_street_type | ca_suite_number | ca_city | ca_county | ca_state | ca_zip | ca_country | ca_gmt_offset | ca_location_type ---------------+------------------+------------------+--------------------+-----------------+-----------------+-----------------+-----------------+----------+------------+---------------+---------------+---------------------- 3 | AAAAAAAADAAAAAAA | 585 | Dogwood Washington | Circle | Suite Q | Pleasant Valley | York County | PA | 12477 | United States | -5.00 | single family (1 row) --关闭游标。 postgres=# CLOSE cursor1;

注意事项 如果游标定义了NO SCROLL，则不允许使用例如FETCH BACKWARD之类的反向抓取。 NEXT，PRIOR，FIRST，LAST，ABSOLUTE，RELATIVE形式在恰当地移动游标之后抓取一条记录。如果后面没有数据行，就返回一个空的结果，此时游标就会停在查询结果的最后一行之后（向后查询时）或者第一行之前（向前查询时）。 FORWARD和BACKWARD形式在向前或者向后移动的过程中抓取指定的行数，然后把游标定位在最后返回的行上；或者是，如果count大于可用的行数，则在所有行之后（向后查询时）或者之前（向前查询时）。 RELATIVE 0，FORWARD 0，BACKWARD 0都要求在不移动游标的前提下抓取当前行，也就是重新抓取最近刚抓取过的行。除非游标定位在第一行之前或者最后一行之后，这个动作都应该成功，而在那两种情况下，不返回任何行。 当FETCH的游标上涉及列存表时，不支持BACKWARD、PRIOR、FIRST等涉及反向获取操作。

功能描述 FETCH通过已创建的游标来检索数据。 每个游标都有一个供FETCH使用的关联位置。游标的关联位置可以在查询结果的第一行之前，或者在结果中的任意行，或者在结果的最后一行之后： 游标刚创建完之后，关联位置在第一行之前的。 在抓取了一些移动行之后，关联位置在检索到的最后一行上。 如果FETCH抓取完了所有可用行，它就停在最后一行后面，或者在反向抓取的情况下是停在第一行前面。 FETCH ALL或FETCH BACKWARD ALL将总是把游标的关联位置放在最后一行或者在第一行前面。

参数说明 direction_clause 定义抓取数据的方向。 取值范围： NEXT（缺省值） 从当前关联位置开始，抓取下一行。 PRIOR 从当前关联位置开始，抓取上一行。 FIRST 抓取查询的第一行（和ABSOLUTE 1相同）。 LAST 抓取查询的最后一行（和ABSOLUTE -1相同）。 ABSOLUTE count 抓取查询中第count行。 ABSOLUTE抓取不会比用相对位移移动到需要的数据行更快，因为下层的实现必须遍历所有中间的行。 count取值范围：有符号的整数 count为正数，就从查询结果的第一行开始，抓取第count行。当count小于当前游标位置时，涉及到rewind操作，暂不支持。 count为负数或0，涉及到反向扫描操作，暂不支持。 RELATIVE count 从当前关联位置开始，抓取随后或前面的第count行。 取值范围：有符号的整数 count为正数就抓取当前关联位置之后的第count行。 count为负数或0，涉及到反向扫描操作，暂不支持。 如果当前行没有数据的话，RELATIVE 0返回空。 count 抓取随后的count行（和FORWARD count一样）。 ALL 从当前关联位置开始，抓取所有剩余的行（和FORWARD ALL一样）。 FORWARD 抓取下一行（和NEXT一样）。 FORWARD count 与RELATIVE count的效果相同，从当前关联位置开始，抓取随后或前面的第count行。 FORWARD ALL 从当前关联位置开始，抓取所有剩余行。 BACKWARD 从当前关联位置开始，抓取前面一行(和PRIOR一样) 。 BACKWARD count 从当前关联位置开始，抓取前面的count行（向后扫描）。 取值范围：有符号的整数 count为正数就抓取当前关联位置之前的第count行。 count为负数就抓取当前关联位置之后的第abs（count）行。 如果有数据的话，BACKWARD 0重新抓取当前行。 BACKWARD ALL 从当前关联位置开始，抓取所有前面的行（向后扫描） 。 { FROM | IN } cursor_name 使用关键字FROM或IN指定游标名称。 取值范围：已创建的游标的名称。

语法格式 FETCH [ direction { FROM | IN } ] cursor_name; 其中direction子句为可选参数。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 NEXT | PRIOR | FIRST | LAST | ABSOLUTE count | RELATIVE count | count | ALL | FORWARD | FORWARD count | FORWARD ALL | BACKWARD | BACKWARD count | BACKWARD ALL

模式匹配操作符 数据库提供了三种独立的实现模式匹配的方法：SQL LIKE操作符、SIMILAR TO操作符和POSIX-风格的正则表达式。除了这些基本的操作符外，还有一些函数可用于提取或替换匹配子串并在匹配位置分离一个串。 LIKE 描述：判断字符串是否能匹配上LIKE后的模式字符串。如果字符串与提供的模式匹配，则LIKE表达式返回为真（NOT LIKE表达式返回假），否则返回为假（NOT LIKE表达式返回真）。 匹配规则： 此操作符只有在它的模式匹配整个串的时候才能成功。如果要匹配在串内任何位置的序列，该模式必须以百分号开头和结尾。 下划线 （_）代表（匹配）任何单个字符； 百分号（%）代表任意串的通配符。 要匹配文本里的下划线或者百分号，在提供的模式里相应字符必须前导逃逸字符。逃逸字符的作用是禁用元字符的特殊含义，缺省的逃逸字符是反斜线，也可以用ESCAPE子句指定一个不同的逃逸字符。 要匹配逃逸字符本身，写两个逃逸字符。例如要写一个包含反斜线的模式常量，那你就要在SQL语句里写两个反斜线。 参数standard_conforming_strings设置为off时，在文串常量中写的任何反斜线都需要被双写。因此，写一个匹配单个反斜线的模式实际上要在语句里写四个反斜线。（你可以通过用ESCAPE选择一个不同的逃逸字符来避免这种情况，这样反斜线就不再是LIKE的特殊字符了。但仍然是字符文本分析器的特殊字符，所以你还是需要两个反斜线。）我们也可以通过写ESCAPE ''的方式不选择逃逸字符，这样可以有效地禁用逃逸机制，但是没有办法关闭下划线和百分号在模式中的特殊含义。 关键字ILIKE可以用于替换LIKE，区别是LIKE大小写敏感，ILIKE大小写不敏感。 操作符~~等效于LIKE，操作符~~*等效于ILIKE。 示例： 1 2 3 4 5 postgres=# SELECT 'abc' LIKE 'abc' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' LIKE 'a%' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' LIKE '_b_' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' LIKE 'c' AS RESULT; result ----------- f (1 row) SIMILAR TO 描述：SIMILAR TO操作符根据自己的模式是否匹配给定串而返回真或者假。他和LIKE非常类似，只不过他使用SQL标准定义的正则表达式理解模式。 匹配规则： 和LIKE一样，此操作符只有在它的模式匹配整个串的时候才能成功。如果要匹配在串内任何位置的序列，该模式必须以百分号开头和结尾。 下划线 （_）代表（匹配）任何单个字符； 百分号（%）代表任意串的通配符。 SIMILAR TO也支持下面这些从POSIX正则表达式借用的模式匹配元字符。 元字符 含义 | 表示选择（两个候选之一） * 表示重复前面的项零次或更多次 + 表示重复前面的项一次或更多次 ? 表示重复前面的项零次或一次 {m} 表示重复前面的项刚好m次 {m,} 表示重复前面的项m次或更多次 {m,n} 表示重复前面的项至少m次并且不超过n次 () 把多个项组合成一个逻辑项 [...] 声明一个字符类，就像POSIX正则表达式一样 前导逃逸字符可以禁止所有这些元字符的特殊含义。逃逸字符的使用规则和LIKE一样。 正则表达式函数： 支持使用函数•substring(string from pattern for escape)截取匹配SQL正则表达式的子字符串。 示例： 1 2 3 4 5 postgres=# SELECT 'abc' SIMILAR TO 'abc' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' SIMILAR TO 'a' AS RESULT; result ----------- f (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' SIMILAR TO '%(b|d)%' AS RESULT; result ----------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' SIMILAR TO '(b|c)%' AS RESULT; result ----------- f (1 row) POSIX正则表达式 描述：正则表达式是一个字符序列，它是定义一个串集合 （一个正则集）的缩写。 如果一个串是正则表达式描述的正则集中的一员时， 我们就说这个串匹配该正则表达式。 POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的含义。表 1 正则表达式匹配操作符列出了所有可用于POSIX正则表达式模式匹配的操作符。 表1 正则表达式匹配操作符 操作符 描述 例子 ~ 匹配正则表达式，大小写敏感 'thomas' ~ '.*thomas.*' ~* 匹配正则表达式，大小写不敏感 'thomas' ~* '.*Thomas.*' ！~ 不匹配正则表达式，大小写敏感 'thomas' !~ '.*Thomas.*' ！~* 不匹配正则表达式，大小写不敏感 'thomas' !~* '.*vadim.*' 匹配规则： 与LIKE不同，正则表达式允许匹配串里的任何位置，除非该正则表达式显式地挂接在串的开头或者结尾。 除了上文提到的元字符外， POSIX正则表达式还支持下列模式匹配元字符。 元字符 含义 ^ 表示串开头的匹配 $ 表示串末尾的匹配 . 匹配任意单个字符 正则表达式函数： POSIX正则表达式支持下面函数。 substring(string from pattern)函数提供了抽取一个匹配POSIX正则表达式模式的子串的方法。 regexp_replace(string, pattern, replacement [,flags ])函数提供了将匹配POSIX正则表达式模式的子串替换为新文本的功能。 regexp_matches(string text, pattern text [, flags text])函数返回一个文本数组，该数组由匹配一个POSIX正则表达式模式得到的所有被捕获子串构成。 regexp_split_to_table(string text, pattern text [, flags text])函数把一个POSIX正则表达式模式当作一个定界符来分离一个串。 regexp_split_to_array(string text, pattern text [, flags text ])和regexp_split_to_table类似，是一个正则表达式分离函数，不过它的结果以一个text数组的形式返回。 正则表达式分离函数会忽略零长度的匹配，这种匹配发生在串的开头或结尾或者正好发生在前一个匹配之后。这和正则表达式匹配的严格定义是相悖的，后者由regexp_matches实现，但是通常前者是实际中最常用的行为。 示例： 1 2 3 4 5 postgres=# SELECT 'abc' ~ 'Abc' AS RESULT; result -------- f (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' ~* 'Abc' AS RESULT; result -------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' !~ 'Abc' AS RESULT; result -------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'abc'!~* 'Abc' AS RESULT; result -------- f (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' ~ '^a' AS RESULT; result -------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' ~ '(b|d)'AS RESULT; result -------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'abc' ~ '^(b|c)'AS RESULT; result -------- f (1 row) 虽然大部分的正则表达式搜索都能很快地执行，但是正则表达式仍可能被人为地弄成需要任意长的时间和任意量的内存进行处理。不建议从非安全模式来源接受正则表达式搜索模式，如果必须这样做，建议加上语句超时限制。使用SIMILAR TO模式的搜索具有同样的安全性危险， 因为SIMILAR TO提供了很多和POSIX-风格正则表达式相同的能力。LIKE搜索比其他两种选项简单得多，因此在接受非安全模式来源搜索时要更安全些。 父主题： 函数和操作符

基本文本匹配 GaussDB的全文检索基于匹配算子@@，当一个tsvector(document)匹配到一个tsquery(query)时，则返回true。其中，tsvector(document)和tsquery(query)两种数据类型可以任意排序。 1 2 3 4 5 postgres=# SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery AS RESULT; result ---------- t (1 row) 1 2 3 4 5 postgres=# SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector AS RESULT; result ---------- f (1 row) 正如上面例子表明，tsquery不仅是文本，且比tsvector包含的要多。tsquery包含已经标注化为词条的搜索词，同时可能是使用AND、OR、或NOT操作符连接的多个术语。详细请参见文本搜索类型。函数to_tsquery和plainto_tsquery对于将用户书写文本转换成适合的tsquery是非常有用的，比如将文本中的词标准化。类似地，to_tsvector用于解析和标准化文档字符串。因此，实际中文本搜索匹配看起来更像这样： 1 2 3 4 5 postgres=# SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat') AS RESULT; result ---------- t (1 row) 需要注意的是，下面这种方式是不可行的： 1 2 3 4 5 postgres=# SELECT 'fat cats ate fat rats'::tsvector @@ to_tsquery('fat & rat')AS RESULT; result ---------- f (1 row) 由于tsvector没有对rats进行标准化，所以rats不匹配rat。 @@操作符也支持text输入，允许一个文本字符串的显示转换为tsvector或者在简单情况下忽略tsquery。可用形式是： 1 2 3 4 tsvector @@ tsquery tsquery @@ tsvector text @@ tsquery text @@ text 我们已经看到了前面两种，形式text @@ tsquery等价于to_tsvector(text) @@ tsquery，而text @@ text等价于to_tsvector(text) @@ plainto_tsquery(text)。 父主题： 介绍

语法格式 设置外表属性 1 2 ALTER FOREIGN TABLE [ IF EXISTS ] table_name OPTIONS ( {[ ADD | SET | DROP ] option ['value']}[, ... ]); 设置新的所有者 1 2 ALTER FOREIGN TABLE [ IF EXISTS ] tablename OWNER TO new_owner;

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 --创建外部表。 postgres=# CREATE FOREIGN TABLE tpcds.customer_ft ( c_customer_sk integer , c_customer_id char(16) , c_current_cdemo_sk integer , c_current_hdemo_sk integer , c_current_addr_sk integer , c_first_shipto_date_sk integer , c_first_sales_date_sk integer , c_salutation char(10) , c_first_name char(20) , c_last_name char(30) , c_preferred_cust_flag char(1) , c_birth_day integer , c_birth_month integer , c_birth_year integer , c_birth_country varchar(20) , c_login char(13) , c_email_address char(50) , c_last_review_date char(10) ) SERVER gsmpp_server OPTIONS ( location 'gsfs://10.185.179.143:5000/customer1*.dat', FORMAT 'TEXT' , DELIMITER '|', encoding 'utf8', mode 'Normal') READ ONLY; --修改外表属性，删除mode选项。 postgres=# ALTER FOREIGN TABLE tpcds.customer_ft options(drop mode); --删除外部表。 postgres=# DROP FOREIGN TABLE tpcds.customer_ft;

优化建议 UNLOGGED UNLOGGED表和表上的索引因为数据写入时不通过WAL日志机制，写入速度远高于普通表。因此，可以用于缓冲存储复杂查询的中间结果集，增强复杂查询的性能。 UNLOGGED表无主备机制，在系统故障或异常断点等情况下，会有数据丢失风险，因此，不可用来存储基础数据。 TEMPORARY | TEMP 临时表只在当前会话可见，会话结束后会自动删除。 除了当前CN外，其他CN对于该临时表不可见。 LIKE 新表自动从这个表中继承所有字段名及其数据类型和非空约束，新表与源表之间在创建动作完毕之后是完全无关的。 LIKE INCLUDING DEFAULTS 源表上的字段缺省表达式只有在指定INCLUDING DEFAULTS时，才会复制到新表中。缺省是不包含缺省表达式的，即新表中的所有字段的缺省值都是NULL。 LIKE INCLUDING CONSTRAINTS 源表上的CHECK约束仅在指定INCLUDING CONSTRAINTS时，会复制到新表中，而其他类型的约束永远不会复制到新表中。非空约束总是复制到新表中。此规则同时适用于表约束和列约束。 LIKE INCLUDING INDEXES 如果指定了INCLUDING INDEXES，则源表上的索引也将在新表上创建，默认不建立索引。 LIKE INCLUDING STORAGE 如果指定了INCLUDING STORAGE，则复制列的STORAGE设置会复制到新表中，默认情况下不包含STORAGE设置。 LIKE INCLUDING COMMENTS 如果指定了INCLUDING COMMENTS，则源表列、约束和索引的注释会复制到新表中。默认情况下，不复制源表的注释。 LIKE INCLUDING PARTITION 如果指定了INCLUDING PARTITION，则源表的分区定义会复制到新表中，同时新表将不能再使用PARTITION BY子句。默认情况下，不拷贝源表的分区定义。 LIKE INCLUDING RELOPTIONS 如果指定了INCLUDING RELOPTIONS，则源表的存储参数（即源表的WITH子句）会复制到新表中。默认情况下，不复制源表的存储参数。 LIKE INCLUDING DISTRIBUTION 如果指定了INCLUDING DISTRIBUTION，则源表的分布信息会复制到新表中，包括分布类型和分布列，同时新表将不能再使用DISTRIBUTE BY子句。默认情况下，不拷贝源表的分布信息。 LIKE INCLUDING ALL INCLUDING ALL包含了INCLUDING DEFAULTS、INCLUDING CONSTRAINTS、INCLUDING INDEXES、INCLUDING STORAGE、INCLUDING COMMENTS、INCLUDING PARTITION、INCLUDING RELOPTIONS和INCLUDING DISTRIBUTION的内容。 ORIENTATION ROW 创建行存表，行存储适合于OLTP业务，此类型的表上交互事务比较多，一次交互会涉及表中的多个列，用行存查询效率较高。 ORIENTATION COLUMN 创建列存表，列存储适合于 数据仓库 业务，此类型的表上会做大量的汇聚计算，且涉及的列操作较少。 DISTRIBUTE BY 事实表或者数据量较大的维度表建议创建为分布表。对指定的列进行Hash，通过映射，把数据分布到指定DN。语法为:distribute by hash(column_name)。 数据量较小的维度表建议创建为复制表。表的每条记录存在所有数据节点（DN）中，即每个数据节点都有完整的表数据。语法为: distribute by replication。