华为云用户手册

选择分布方案 【建议】表的分布方式的选择一般遵循以下原则： 表2 表的分布方式及使用场景 分布方式 描述 适用场景 Hash 表数据通过Hash方式散列到集群中的所有DN上。 数据量较大的事实表。 Replication 集群中每一个DN都有一份全量表数据。 维度表、数据量较小的事实表。 Range 表数据对指定列按照范围进行映射，分布到对应DN。 用户需要自定义分布规则的场景。 List 表数据对指定列按照具体值进行映射，分布到对应DN。 用户需要自定义分布规则的场景。 当指定Hash、Range或List分布时，创建主键和唯一索引必须包含分布列。 当被参照表指定Hash、Range或List分布时，参照表的外键必须包含分布列。 典型的分布表定义如下： 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 --定义一个表，表中每行存在所有DN中。 CREATE TABLE warehouse_d1 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) )DISTRIBUTE BY REPLICATION; --定义一个表，使用HASH分布。 CREATE TABLE warehouse_d2 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2), CONSTRAINT W_CONSTR_KEY3 UNIQUE(W_WAREHOUSE_SK) )DISTRIBUTE BY HASH(W_WAREHOUSE_SK); --定义一个表，使用RANGE分布 CREATE TABLE warehouse_d3 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) )DISTRIBUTE BY RANGE(W_WAREHOUSE_ID) ( SLICE s1 VALUES LESS THAN (10) DATANODE dn1, SLICE s2 VALUES LESS THAN (20) DATANODE dn2, SLICE s3 VALUES LESS THAN (30) DATANODE dn3, SLICE s4 VALUES LESS THAN (MAXVALUE) DATANODE dn4 ); --定义一个表，使用LIST分布 CREATE TABLE warehouse_d4 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) )DISTRIBUTE BY LIST(W_COUNTRY) ( SLICE s1 VALUES ('USA') DATANODE dn1, SLICE s2 VALUES ('CANADA') DATANODE dn2, SLICE s3 VALUES ('UK') DATANODE dn3, SLICE s4 VALUES (DEFAULT) DATANODE dn4 );

示例 示例1：Union中的待定类型解析。这里，unknown类型文本'b'将被解析成text类型。 1 2 3 4 5 6 openGauss=# SELECT text 'a' AS "text" UNION SELECT 'b'; text ------ a b (2 rows) 示例2：简单Union中的类型解析。文本1.2的类型为numeric，而且integer类型的1可以隐含地转换为numeric，因此使用这个类型。 1 2 3 4 5 6 openGauss=# SELECT 1.2 AS "numeric" UNION SELECT 1; numeric --------- 1 1.2 (2 rows) 示例3：转置Union中的类型解析。这里，因为类型real不能被隐含转换成integer，但是integer可以隐含转换成real，那么联合的结果类型将是real。 1 2 3 4 5 6 openGauss=# SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL); real ------ 1 2.2 (2 rows) 示例4：TD模式下，coalesce参数输入int和varchar类型，那么解析成varchar类型。ORA模式下会报错。 --在oracle模式下，创建oracle兼容模式的数据库oracle_1。 openGauss=# CREATE DATABASE oracle_1 dbcompatibility = 'ORA'; --切换数据库为oracle_1。 openGauss=# \c oracle_1 --创建表t1。 oracle_1=# CREATE TABLE t1(a int, b varchar(10)); --删除表。 oracle_1=# DROP TABLE t1; --切换数据库为postgres。 oracle_1=# \c postgres --在TD模式下，创建TD兼容模式的数据库td_1。 openGauss=# CREATE DATABASE td_1 dbcompatibility = 'TD'; --切换数据库为td_1。 openGauss=# \c td_1 --创建表t2。 td_1=# CREATE TABLE t2(a int, b varchar(10)); --查看coalesce参数输入int和varchar类型的查询语句的执行计划。 td_1=# EXPLAIN VERBOSE select coalesce(a, b) from t2; QUERY PLAN --------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Output: (COALESCE((t2.a)::character varying, t2.b)) Node/s: All datanodes Remote query: SELECT COALESCE(a::character varying, b) AS "coalesce" FROM public.t2 (4 rows) --删除表。 td_1=# DROP TABLE t2; --切换数据库为postgres。 td_1=# \c postgres --删除Oracle和TD模式的数据库。 openGauss=# DROP DATABASE oracle_1; openGauss=# DROP DATABASE td_1; 示例5：ORA模式下，将整个表达式最终的返回值类型定为result1的数据类型，或者与result1同类型范畴的更高精度的数据类型。 --在ORA模式下，创建ORA兼容模式的数据库ora_1。 openGauss=# CREATE DATABASE ora_1 dbcompatibility = 'A'; --切换数据库为ora_1。 openGauss=# \c ora_1 --开启Decode兼容性参数。 set sql_beta_feature='a_style_coerce'; --创建表t1。 ora_1=# CREATE TABLE t1(c_int int, c_float8 float8, c_char char(10), c_text text, c_date date); --插入数据。 ora_1=# INSERT INTO t1 VALUES(1, 2, '3', '4', date '12-10-2010'); --result1类型为char，defresult类型为text，text精度更高，返回值的类型由char更新为text。 ora_1=# SELECT decode(1, 2, c_char, c_text) AS result, pg_typeof(result) FROM t1; result | pg_typeof --------+----------- 4 | text (1 row) --result1类型为int，属于数值类型范畴，返回值的类型置为numeric。 ora_1=# SELECT decode(1, 2, c_int, c_float8) AS result, pg_typeof(result) FROM t1; result | pg_typeof --------+----------- 2 | numeric (1 row) --不存在defresult数据类型向result1数据类型之间的隐式转换，报错处理。 ora_1=# SELECT decode(1, 2, c_int, c_date) FROM t1; ERROR: CASE types integer and timestamp without time zone cannot be matched LINE 1: SELECT decode(1, 2, c_int, c_date) FROM t1; ^ CONTEXT: referenced column: c_date --关闭Decode兼容性参数。 set sql_beta_feature='none'; --删除表。 ora_1=# DROP TABLE t1; DROP TABLE --切换数据库为postgres。 ora_1=# \c postgres --删除ORA模式的数据库。 openGauss=# DROP DATABASE ora_1; DROP DATABASE

UNION，CASE和相关构造解析 如果所有输入都是相同的类型，并且不是unknown类型，那么解析成这种类型。 如果所有输入都是unknown类型则解析成text类型（字符串类型范畴的首选类型）。否则，忽略unknown输入。 如果输入不属于同一个类型范畴，失败。（unknown类型除外） 如果输入类型是同一个类型范畴，则选择该类型范畴的首选类型。（例外：union操作会选择第一个分支的类型作为所选类型。） 系统表pg_type中typcategory表示数据类型范畴， typispreferred表示是否是typcategory分类中的首选类型。 把所有输入转换为所选的类型（对于字符串保持原有长度）。如果从给定的输入到所选的类型没有隐式转换则失败。 若输入中含json、txid_snapshot、sys_refcursor或几何类型，则不能进行union。

对于case和coalesce，在TD兼容模式下的处理 如果所有输入都是相同的类型，并且不是unknown类型，那么解析成这种类型。 如果所有输入都是unknown类型则解析成text类型。 如果输入字符串（包括unknown，unknown当text来处理）和数字类型，那么解析成字符串类型，如果是其他不同的类型范畴，则报错。 如果输入类型是同一个类型范畴，则选择该类型的优先级较高的类型。 把所有输入转换为所选的类型。如果从给定的输入到所选的类型没有隐式转换则失败。

注意事项 当分配的句柄并非环境句柄时，如果SQLAllocHandle返回的值为SQL_ERROR，则它会将OutputHandlePtr的值设置为SQL_NULL_HDBC、SQL_NULL_HSTMT或SQL_NULL_HDESC。之后，通过调用带有适当参数的SQLGetDiagRec，其中HandleType和Handle被设置为IntputHandle的值，可得到相关的SQLSTATE值，通过SQLSTATE值可以查出调用此函数的具体信息。

参数 表1 SQLAllocHandle参数 关键字 参数说明 HandleType 由SQLAllocHandle分配的句柄类型。必须为下列值之一： SQL_HANDLE_ENV（环境句柄） SQL_HANDLE_DBC（连接句柄） SQL_HANDLE_STMT（语句句柄） SQL_HANDLE_DESC（描述句柄） 申请句柄顺序为，先申请环境句柄，再申请连接句柄，最后申请语句句柄，后申请的句柄都要依赖它前面申请的句柄。 InputHandle 将要分配的新句柄的类型。 如果HandleType为SQL_HANDLE_ENV，则这个值为SQL_NULL_HANDLE。 如果HandleType为SQL_HANDLE_DBC，则这一定是一个环境句柄。 如果HandleType为SQL_HANDLE_STMT或SQL_HANDLE_DESC，则它一定是一个连接句柄。 OutputHandlePtr 输出参数：一个缓冲区的指针，此缓冲区以新分配的数据结构存放返回的句柄。

数值类型 表1列出了所有的可用类型。数字操作符和相关的内置函数请参见数字操作函数和操作符。 表1 整数类型 名称 描述 存储空间 范围 TINYINT 微整数，别名为INT1。 1字节 0 ~ 255 SMALLINT 小范围整数，别名为INT2。 2字节 -32,768 ~ +32,767 INTEGER 常用的整数，别名为INT4。 4字节 -2,147,483,648 ~ +2,147,483,647 BINARY_INTEGER 常用的整数INTEGER的别名，为兼容Oracle类型。 4字节 -2,147,483,648 ~ +2,147,483,647 BIGINT 大范围的整数，别名为INT8。 8字节 -9,223,372,036,854,775,808 ~ +9,223,372,036,854,775,807 示例： --创建具有TINYINT类型数据的表。 openGauss=# CREATE TABLE int_type_t1 ( IT_COL1 TINYINT ); --插入数据。 openGauss=# INSERT INTO int_type_t1 VALUES(10); --查看数据。 openGauss=# SELECT * FROM int_type_t1; it_col1 --------- 10 (1 row) --删除表。 openGauss=# DROP TABLE int_type_t1; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 --创建具有TINYINT,INTEGER,BIGINT类型数据的表。 openGauss=# CREATE TABLE int_type_t2 ( a TINYINT, b TINYINT, c INTEGER, d BIGINT ); --插入数据。 openGauss=# INSERT INTO int_type_t2 VALUES(100, 10, 1000, 10000); --查看数据。 openGauss=# SELECT * FROM int_type_t2; a | b | c | d -----+----+------+------- 100 | 10 | 1000 | 10000 (1 row) --删除表。 openGauss=# DROP TABLE int_type_t2; TINYINT、SMALLINT、INTEGER和BIGINT类型存储各种范围的数字，也就是整数。试图存储超出范围以外的数值将会导致错误。 常用的类型是INTEGER，因为它提供了在范围、存储空间、性能之间的最佳平衡。一般只有取值范围确定不超过SMALLINT的情况下，才会使用SMALLINT类型。而只有在INTEGER的范围不够的时候才使用BIGINT，因为前者相对快得多。 表2 任意精度型 名称 描述 存储空间 范围 NUMERIC[(p[,s])], DECIMAL[(p[,s])] 精度p取值范围为[1,1000]，标度s取值范围为[0,p]。 说明： p为总位数，s为小数位数。 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 未指定精度的情况下，小数点前最大131,072位，小数点后最大16,383位。 NUMBER[(p[,s])] NUMERIC类型的别名，为兼容Oracle数据类型。 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 未指定精度的情况下，小数点前最大131,072位，小数点后最大16,383位。 示例： --创建表。 openGauss=# CREATE TABLE decimal_type_t1 ( DT_COL1 DECIMAL(10,4) ); --插入数据。 openGauss=# INSERT INTO decimal_type_t1 VALUES(123456.122331); --查询表中的数据。 openGauss=# SELECT * FROM decimal_type_t1; dt_col1 ------------- 123456.1223 (1 row) --删除表。 openGauss=# DROP TABLE decimal_type_t1; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 --创建表。 openGauss=# CREATE TABLE numeric_type_t1 ( NT_COL1 NUMERIC(10,4) ); --插入数据。 openGauss=# INSERT INTO numeric_type_t1 VALUES(123456.12354); --查询表中的数据。 openGauss=# SELECT * FROM numeric_type_t1; nt_col1 ------------- 123456.1235 (1 row) --删除表。 openGauss=# DROP TABLE numeric_type_t1; 与整数类型相比，任意精度类型需要更大的存储空间，其存储效率、运算效率以及压缩比效果都要差一些。在进行数值类型定义时，优先选择整数类型。当且仅当数值超出整数可表示最大范围时，再选用任意精度类型。 使用Numeric/Decimal进行列定义时，建议指定该列的精度p以及标度s。 表3 序列整型 名称 描述 存储空间 范围 SMALLSERIAL 二字节序列整型。 2字节 -32,768 ~ +32,767 SERIAL 四字节序列整型。 4字节 -2,147,483,648 ~ +2,147,483,647 BIGSERIAL 八字节序列整型。 8字节 -9,223,372,036,854,775,808 ~ +9,223,372,036,854,775,807 示例： 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 --创建表。 openGauss=# CREATE TABLE smallserial_type_tab(a SMALLSERIAL); --插入数据。 openGauss=# INSERT INTO smallserial_type_tab VALUES(default); --再次插入数据。 openGauss=# INSERT INTO smallserial_type_tab VALUES(default); --查看数据。 openGauss=# SELECT * FROM smallserial_type_tab; a --- 1 2 (2 rows) --创建表。 openGauss=# CREATE TABLE serial_type_tab(b SERIAL); --插入数据。 openGauss=# INSERT INTO serial_type_tab VALUES(default); --再次插入数据。 openGauss=# INSERT INTO serial_type_tab VALUES(default); --查看数据。 openGauss=# SELECT * FROM serial_type_tab; b --- 1 2 (2 rows) --创建表。 openGauss=# CREATE TABLE bigserial_type_tab(c BIGSERIAL); --插入数据。 openGauss=# INSERT INTO bigserial_type_tab VALUES(default); --插入数据。 openGauss=# INSERT INTO bigserial_type_tab VALUES(default); --查看数据。 openGauss=# SELECT * FROM bigserial_type_tab; c --- 1 2 (2 rows) --删除表。 openGauss=# DROP TABLE smallserial_type_tab; openGauss=# DROP TABLE serial_type_tab; openGauss=# DROP TABLE bigserial_type_tab; SMALLSERIAL，SERIAL和BIGSERIAL类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。因此，创建一个整数字段，并且把它的缺省数值安排为从一个序列发生器读取。应用了一个NOT NULL约束以确保NULL不会被插入。在大多数情况下用户可能还希望附加一个UNIQUE或PRIMARY KEY约束避免意外地插入重复的数值，但这个不是自动的。最后，将序列发生器从属于那个字段，这样当该字段或表被删除的时候也一并删除它。目前只支持在创建表时候指定SERIAL列，不可以在已有的表中，增加SERIAL列。另外临时表也不支持创建SERIAL列。因为SERIAL不是真正的类型，也不可以将表中存在的列类型转化为SERIAL。 表4 浮点类型 名称 描述 存储空间 范围 REAL, FLOAT4 单精度浮点数，不精准。 4字节 -3.402E+38~3.402E+38 6位十进制数字精度。 DOUBLE PRECISION, FLOAT8 双精度浮点数，不精准。 8字节 -1.79E+308~1.79E+308， 15位十进制数字精度。 FLOAT[(p)] 浮点数，不精准。精度p取值范围为[1,53]。 说明： p为精度，表示二进制总位数。 4字节或8字节 根据精度p不同选择REAL或DOUBLE PRECISION作为内部表示。如不指定精度，内部用DOUBLE PRECISION表示。 BINARY_DOUBLE 是DOUBLE PRECISION的别名，为兼容Oracle类型。 8字节 -1.79E+308~1.79E+308，15位十进制数字精度。 DEC[(p[,s])] 精度p取值范围为[1,1000]，标度s取值范围为[0,p]。 说明： p为总位数，s为小数位位数。 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 未指定精度的情况下，小数点前最大131,072位，小数点后最大16,383位。 INTEGER[(p[,s])] 精度p取值范围为[1,1000]，标度s取值范围为[0,p]。 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 - 关于浮点类型的精度，目前只能保证直接读取时的精度位数。涉及分布式计算时，由于计算执行在各个DN节点上，并且最终汇聚到一个CN节点，因此误差可能会随计算节点数量增加而被放大。 示例： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 --创建表。 openGauss=# CREATE TABLE float_type_t2 ( FT_COL1 INTEGER, FT_COL2 FLOAT4, FT_COL3 FLOAT8, FT_COL4 FLOAT(3), FT_COL5 BINARY_DOUBLE, FT_COL6 DECIMAL(10,4), FT_COL7 INTEGER(6,3) )DISTRIBUTE BY HASH ( ft_col1); --插入数据。 openGauss=# INSERT INTO float_type_t2 VALUES(10,10.365456,123456.1234,10.3214, 321.321, 123.123654, 123.123654); --查看数据。 openGauss=# SELECT * FROM float_type_t2 ; ft_col1 | ft_col2 | ft_col3 | ft_col4 | ft_col5 | ft_col6 | ft_col7 ---------+---------+-------------+---------+---------+----------+--------- 10 | 10.3655 | 123456.1234 | 10.3214 | 321.321 | 123.1237 | 123.124 (1 row) --删除表。 openGauss=# DROP TABLE float_type_t2; 父主题： 数据类型

操作步骤 创建源表products，并插入数据。 1 2 3 4 5 6 7 8 9 10 11 openGauss=# CREATE TABLE products ( product_id INTEGER, product_name VARCHAR2(60), category VARCHAR2(60) ); openGauss=# INSERT INTO products VALUES (1502, 'olympus camera', 'electrncs'), (1601, 'lamaze', 'toys'), (1666, 'harry potter', 'toys'), (1700, 'wait interface', 'books'); 创建目标表newproducts,并插入数据。 1 2 3 4 5 6 7 8 9 10 11 12 openGauss=# CREATE TABLE newproducts ( product_id INTEGER, product_name VARCHAR2(60), category VARCHAR2(60) ); openGauss=# INSERT INTO newproducts VALUES (1501, 'vivitar 35mm', 'electrncs'), (1502, 'olympus ', 'electrncs'), (1600, 'play gym', 'toys'), (1601, 'lamaze', 'toys'), (1666, 'harry potter', 'dvd'); 使用MERGE INTO 语句将源表products的数据合并至目标表newproducts。 1 2 3 4 5 6 7 openGauss=# MERGE INTO newproducts np USING products p ON (np.product_id = p.product_id ) WHEN MATCHED THEN UPDATE SET np.product_name = p.product_name, np.category = p.category WHEN NOT MATCHED THEN INSERT VALUES (p.product_id, p.product_name, p.category) ; 上述语句中使用的参数说明，请见表1。更多信息，请参见MERGE INTO。 表1 MERGE INTO语句参数说明 参数 说明 举例 INTO 子句 指定需要更新或插入数据的目标表。 目标表支持指定别名。 目标表支持复制表，但复制表不能带有含volatile函数的列（如自增列）；如果enable_stream_operator=off，目标复制表需要带有主键或某一列满足unique not null约束。 取值：newproducts np 说明：名为newproducts，别名为np的目标表。 USING子句 指定源表。源表支持指定别名。 目标表是复制表时，源表也需要是复制表。 取值：products p 名为products，别名为p的源表。 ON子句 指定目标表和源表的关联条件。 关联条件中的字段不支持更新。 取值：np.product_id = p.product_id 说明：指定的关联条件为，目标表newproducts的product_id字段和源表products的product_id字段相等。 WHEN MATCHED子句 当源表和目标表中数据针对关联条件可以匹配上时，选择WHEN MATCHED子句进行UPDATE操作。 仅支持指定一个WHEN MATCHED子句。 WHEN MATCHED子句可缺省，缺省时，对于满足ON子句条件的行，不进行任何操作。 若目标表中存在分布列，则该列不支持更新。 取值：WHEN MATCHED THEN UPDATE SET np.product_name = p.product_name, np.category = p.category 说明：当满足ON子句条件时，将目标表newproducts的product_name、category字段的值替换为源表products相对应字段的值。 WHEN NOT MATCHED子句 当源表和目标表中数据针对关联条件无法匹配时，选择WHEN NOT MATCHED子句进行INSERT操作。 仅支持指定一个WHEN NOT MATCHED子句。 WHEN NOT MATCHED子句可缺省。 不支持INSERT子句中包含多个VALUES。 WHEN MATCHED和WHEN NOT MATCHED子句顺序可以交换，可以缺省其中一个，但不能同时缺省。 取值：WHEN NOT MATCHED THEN INSERT VALUES (p.product_id, p.product_name, p.category) 说明：将源表products中，不满足ON子句条件的行插入目标表products。 查询合并后的目标表newproducts。 1 openGauss=# SELECT * FROM newproducts; 返回信息如下： product_id | product_name | category ------------+----------------+----------- 1501 | vivitar 35mm | electrncs 1502 | olympus camera | electrncs 1666 | harry potter | toys 1600 | play gym | toys 1601 | lamaze | toys 1700 | wait interface | books (6 rows)

示例 示例1：创建GDS导出外表foreign_tpcds_reasons，待导出数据格式为CSV，用于接收数据服务器上的数据。 其中设置的导出模式信息如下所示： 规划数据服务器与集群处于同一内网，数据服务器IP为192.168.0.90，待导出的数据文件格式为CSV，选择并行导出模式为Remote模式。 假定启动GDS时，规划导出的数据文件存放目录为“/output_data/”，GDS侦听端口为5000，所以设置参数“location”为“gsfs://192.168.0.90:5000/”。 设置导出的数据格式信息，参数设置如下所示： 导出数据文件格式（format）为CSV。 编码格式（encoding）为UTF-8。 字段分隔符（delimiter）为E'\x20'。 引号字符（quote）为0x1b。 数据文件中空值（null）为没有引号的空字符串。 逃逸字符（escape）为默认值双引号。 数据文件是否包含标题行（header）为默认值false，即导出时数据文件第一行被识别为数据。 导出数据文件换行符样式（EOL）为0X0A。 创建的外表如下所示： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 openGauss=# CREATE FOREIGN TABLE foreign_tpcds_reasons ( r_reason_sk integer not null, r_reason_id char(16) not null, r_reason_desc char(100) ) SERVER gsmpp_server OPTIONS (LOCATION 'gsfs://192.168.0.90:5000/', FORMAT 'CSV', DELIMITER E'\x20', QUOTE E'\x1b', NULL '', EOL '0x0a' ) WRITE ONLY; 示例2：创建GDS导出外表foreign_tpcds_reasons，导出数据格式为FIXED，用于接收数据服务器上的数据。 其中设置的导出模式信息如下所示： 规划数据服务器与集群处于同一内网，数据服务器IP为192.168.0.90，计划导出的数据文件格式为CSV，选择并行导出模式为Remote模式。 假定启动GDS时，规划导出的数据文件存放目录为“/output_data/”，GDS侦听端口为5000，所以设置参数“location”为“gsfs://192.168.0.90:5000/”。 设置导出的数据格式信息，参数设置如下所示： 导出数据文件格式（format）为FIXED。 编码格式（encoding）为UTF-8。 数据文件是否包含标题行（header）为默认值false，即指定导出数据文件是不包含标题行。 定义每一个字段在数据文件中的位置POSITION(offset,length)。其中offset为该字段在文件中的起始位置，length为该字段的长度，单位为字节。 字段“r_reason_sk”，数据类型为integer，使用以下查询所得占用的最大字节数为2，所以设置的起始位置为1，长度为2。 字段“r_reason_id”，数据类型为character varying(16)，使用以下查询所得占用的最大字节数为16，所以设置的起始位置为字段“r_reason_sk”的offset+length=1+2=3，长度为16。 字段“r_reason_desc”，数据类型为character varying(100)，使用以下查询所得占用的最大字节数为100，所以设置的起始位置为字段“r_reason_id”的offset+length=3+16=19，长度为100。 1 2 3 4 5 openGauss=# SELECT max(lengthb(r_reason_sk)),max(lengthb(r_reason_id)),max(lengthb(r_reason_desc)) FROM reasons; max | max | max -----+-----+----- 2 | 16 | 100 (1 row) 导出数据文件换行符样式（EOL）为0X0A。 创建的外表如下所示： 1 2 3 4 5 6 7 8 9 10 11 12 openGauss=# CREATE FOREIGN TABLE foreign_tpcds_reasons ( r_reason_sk integer position(1,2), r_reason_id char(16) position(3,16), r_reason_desc char(100) position(19,100) ) SERVER gsmpp_server OPTIONS (LOCATION 'gsfs://192.168.0.90:5000/', FORMAT 'FIXED', ENCODING 'utf8', EOL '0x0a' ) WRITE ONLY;

操作步骤 根据规划导出数据中规划的路径确定外表参数location的值。 Remote模式 请通过URL方式设置参数“location”，用于指定导出的数据文件存放路径。 不需要指定文件名。 当有多个路径时，只有第一个路径有效。 示例： GDS数据服务器IP为192.168.0.90，假定启动GDS时设置的侦听端口为5000，设置的导出后文件存放目录为“/output_data/”。 根据以上情况，在创建外表时，指定参数“location”为“gsfs://192.168.0.90:5000/”。 Local模式 设置参数“location”，用于指定导出的数据文件存放路径，不需要指定文件名。 示例： 数据源文件可通过本地文件方式访问，计划将导出数据文件存放在“/output_data/”目录中。 根据以上情况，在创建外表时，指定参数“location”为“file:///output_data/”。 梳理待导出数据的格式信息，确定创建外表时使用的数据格式参数的值。格式参数详细介绍，请参见数据格式参数。 根据前面步骤确定的参数，创建GDS外表。外表的创建语法以及详细使用，请参考CREATE FOREIGN TABLE (导入导出)。

示例 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 --创建表空间。 openGauss=# CREATE TABLESPACE ds_location1 RELATIVE LOCATION 'test_tablespace/test_tablespace_1'; --创建用户joe。 openGauss=# CREATE ROLE joe IDENTIFIED BY 'xxxxxxxxxxxx'; --创建用户jay。 openGauss=# CREATE ROLE jay IDENTIFIED BY 'xxxxxxxxxxx'; --创建表空间，且所有者指定为用户joe。 openGauss=# CREATE TABLESPACE ds_location2 OWNER joe RELATIVE LOCATION 'test_tablespace/test_tablespace_2'; --把表空间ds_location1重命名为ds_location3。 openGauss=# ALTER TABLESPACE ds_location1 RENAME TO ds_location3; --改变表空间ds_location2的所有者。 openGauss=# ALTER TABLESPACE ds_location2 OWNER TO jay; --删除表空间。 openGauss=# DROP TABLESPACE ds_location2; openGauss=# DROP TABLESPACE ds_location3; --删除用户。 openGauss=# DROP ROLE joe; openGauss=# DROP ROLE jay;

注意事项 系统管理员或者继承了内置角色gs_role_tablespace权限的用户可以创建表空间。 不允许在一个事务块内部执行CREATE TABLESPACE。 执行CREATE TABLESPACE失败，如果内部创建目录（文件）操作成功了就会产生残留的目录（文件），重新创建时需要用户手动清理表空间指定的目录下残留的内容。如果在创建过程中涉及到数据目录下的表空间软连接残留，需要先将软连接的残留文件删除，再重新执行OM相关操作。 CREATE TABLESPACE不支持两阶段事务，如果部分节点执行失败，不支持回滚。 创建表空间前的准备工作参考下述参数说明。 在公有云场景下一般不建议用户使用自定义的表空间。 原因：用户自定义表空间通常配合主存（即默认表空间所在的存储设备，如磁盘）以外的其它存储介质使用，以隔离不同业务可以使用的IO资源，而在公有云场景下，存储设备都是采用标准化的配置，无其它可用的存储介质，自定义表空间使用不当不利于系统长稳运行以及影响整体性能，因此建议使用默认表空间即可。

语法格式 1 2 3 CREATE TABLESPACE tablespace_name [ OWNER user_name ] [ RELATIVE ] LOCATION 'directory' [ MAXSIZE 'space_size' ] [with_option_clause]; 其中普通表空间的with_option_clause为： 1 2 3 WITH ( {filesystem= { 'general'| "general" | general} | random_page_cost = { 'value ' | value } | seq_page_cost = { 'value ' | value }}[,...])

行存表 默认创建表的类型。数据按行进行存储，即一行数据是连续存储。适用于对数据需要经常更新的场景。 1 2 3 4 5 6 7 8 9 openGauss=# CREATE TABLE customer_t1 ( state_ID CHAR(2), state_NAME VARCHAR2(40), area_ID NUMBER ); --删除表 openGauss=# DROP TABLE customer_t1;

列存表 数据按列进行存储，即一列所有数据是连续存储的。单列查询IO小，比行存表占用更少的存储空间。适合数据批量插入、更新较少和以查询为主统计分析类的场景。列存表不适合点查询。 1 2 3 4 5 6 7 8 9 10 openGauss=# CREATE TABLE customer_t2 ( state_ID CHAR(2), state_NAME VARCHAR2(40), area_ID NUMBER ) WITH (ORIENTATION = COLUMN); --删除表 openGauss=# DROP TABLE customer_t2;

删除外表和目标表 执行以下命令，删除目标表product_info。 1 openGauss=# DROP TABLE product_info; 当结果显示为如下信息，则表示删除成功。 1 DROP TABLE 执行以下命令，删除外表product_info_ext。 1 openGauss=# DROP FOREIGN TABLE product_info_ext; 当结果显示为如下信息，则表示删除成功。 1 DROP FOREIGN TABLE

创建索引 为了加速文本搜索，可以创建GIN索引。 1 openGauss=# CREATE INDEX pgweb_idx_1 ON tsearch.pgweb USING gin(to_tsvector('english', body)); to_tsvector()函数有两个版本。只输一个参数的版本和输两个参数的版本。只输一个参数时，系统默认采用default_text_search_config所指定的分词器。 请注意：创建索引时必须使用to_tsvector的两参数版本。只有指定了分词器名称的全文检索函数才可以在索引表达式中使用。这是因为索引的内容必须不受default_text_search_config的影响，否则索引内容可能不一致。由于default_text_search_config的值可以随时调整，从而导致不同条目生成的tsvector采用了不同的分词器，并且没有办法区分究竟使用了哪个分词器。正确地转储和恢复这样的索引也是不可能的。 因为在上述创建索引中to_tsvector使用了两个参数，只有当查询时也使用了两个参数，且参数值与索引中相同时，才会使用该索引。也就是说，WHERE to_tsvector('english', body) @@ 'a & b' 可以使用索引，但WHERE to_tsvector(body) @@ 'a & b'不能使用索引。这确保只使用这样的索引——索引各条目是使用相同的分词器创建的。 索引中的分词器名称由另一列指定时可以建立更复杂的表达式索引。例如： 1 openGauss=# CREATE INDEX pgweb_idx_2 ON tsearch.pgweb USING gin(to_tsvector('ngram', body)); 其中body是pgweb表中的一列。当对索引的各条目使用了哪个分词器进行记录时，允许在同一索引中存在混合分词器。在某些场景下这将是有用的。例如，文档集合中包含不同语言的文档时。再次强调，打算使用索引的查询必须措辞匹配，例如，WHERE to_tsvector(config_name, body) @@ 'a & b'与索引中的to_tsvector措辞匹配。 索引甚至可以连接列： 1 openGauss=# CREATE INDEX pgweb_idx_3 ON tsearch.pgweb USING gin(to_tsvector('english', title || ' ' || body)); 另一个方法是创建一个单独的tsvector列控制to_tsvector的输出。下面的例子是title和body的连接， 当其它是NULL的时候，使用coalesce确保一个字段仍然会被索引： 1 2 openGauss=# ALTER TABLE tsearch.pgweb ADD COLUMN textsearchable_index_col tsvector; openGauss=# UPDATE tsearch.pgweb SET textsearchable_index_col = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')); 然后为加速搜索创建一个GIN索引： 1 openGauss=# CREATE INDEX textsearch_idx_4 ON tsearch.pgweb USING gin(textsearchable_index_col); 现在，就可以执行一个快速全文搜索了： 1 2 3 4 5 6 7 8 9 10 11 openGauss=# SELECT title FROM tsearch.pgweb WHERE textsearchable_index_col @@ to_tsquery('north & america') ORDER BY last_mod_date DESC LIMIT 10; title -------- Canada Mexico (2 rows) 相比于一个表达式索引，单独列方法的一个优势是：它没有必要在查询时明确指定分词器以便能使用索引。正如上面例子所示，查询可以依赖于default_text_search_config。另一个优势是搜索比较快速，因为它没有必要重新利用to_tsvector调用来验证索引匹配。表达式索引方法更容易建立，且它需要较少的磁盘空间，因为tsvector形式没有明确存储。 父主题： 表和索引

参数 表1 SQLSetConnectAttr参数 关键字 参数说明 ConnectionHandle 连接句柄。 Attribute 设置属性。 ValuePtr 指向对应Attribute的值。依赖于Attribute的值，ValuePtr是32位无符号整型值或指向以空结束的字符串。注意，如果ValuePtr参数是驱动程序指定值。ValuePtr可能是有符号的整数。 StringLength 如果ValuePtr指向字符串或二进制缓冲区，这个参数是*ValuePtr长度，如果ValuePtr指向整型，忽略StringLength。

原型 1 2 3 4 SQLRETURN SQLSetConnectAttr(SQLHDBC ConnectionHandle SQLINTEGER Attribute, SQLPOINTER ValuePtr, SQLINTEGER StringLength);

功能描述 ALTER INDEX用于修改现有索引的定义。 它有几种子形式： IF EXISTS 如果指定的索引不存在，则发出一个notice而不是error。 RENAME TO 只改变索引的名称。对存储的数据没有影响。 SET TABLESPACE 这个选项会改变索引的表空间为指定表空间，并且把索引相关的数据文件移动到新的表空间里。 SET ( { STORAGE_PARAMETER = value } [, ...] ) 改变索引的一个或多个索引方法特定的存储参数。 需要注意的是索引内容不会被这个命令立即修改，根据参数的不同，可能需要使用REINDEX重建索引来获得期望的效果。 RESET ( { storage_parameter } [, ...] ) 重置索引的一个或多个索引方法特定的存储参数为缺省值。与SET一样，可能需要使用REINDEX来完全更新索引。 [ MODIFY PARTITION index_partition_name ] UNUSABLE 用于设置表或者索引分区上的索引不可用。 REBUILD [ PARTITION index_partition_name ] 用于重建表或者索引分区上的索引。 RENAME PARTITION 用于重命名索引分区 MOVE PARTITION 用于修改索引分区的所属表空间。

语法格式 重命名表索引的名称。 1 2 ALTER INDEX [ IF EXISTS ] index_name RENAME TO new_name; 修改表索引的所属空间。 1 2 ALTER INDEX [ IF EXISTS ] index_name SET TABLESPACE tablespace_name; 修改表索引的存储参数。 1 2 ALTER INDEX [ IF EXISTS ] index_name SET ( {storage_parameter = value} [, ... ] ); 重置表索引的存储参数。 1 2 ALTER INDEX [ IF EXISTS ] index_name RESET ( storage_parameter [, ... ] ) ; 设置表索引或索引分区不可用。 1 2 ALTER INDEX [ IF EXISTS ] index_name [ MODIFY PARTITION index_partition_name ] UNUSABLE; 列存表不支持该语法。 重建表索引或索引分区。 1 2 ALTER INDEX index_name REBUILD [ PARTITION index_partition_name ]; 重命名索引分区。 1 2 ALTER INDEX [ IF EXISTS ] index_name RENAME PARTITION index_partition_name TO new_index_partition_name; 修改索引分区的所属表空间。 1 2 ALTER INDEX [ IF EXISTS ] index_name MOVE PARTITION index_partition_name TABLESPACE new_tablespace;

参数说明 index_name 要修改的索引名。 new_name 新的索引名。 取值范围：字符串，且符合标识符命名规范。 tablespace_name 表空间的名称。 取值范围：已存在的表空间。 storage_parameter 索引方法特定的参数名。 value 索引方法特定的存储参数的新值。根据参数的不同，这可能是一个数字或单词。 new_index_partition_name 新索引分区名。 index_partition_name 索引分区名。 new_tablespace 新表空间。

背景信息 索引可以提高数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。所以是否要为表增加索引，索引建立在哪些字段上，是创建索引前必须要考虑的问题。需要分析应用程序的业务处理、数据使用、经常被用作查询的条件或者被要求排序的字段来确定是否建立索引。 索引建立在数据库表中的某些列上。因此，在创建索引时，应该仔细考虑在哪些列上创建索引。 在经常需要搜索查询的列上创建索引，可以加快搜索的速度。 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。 在经常使用连接的列上创建索引，这些列主要是一些外键，可以加快连接的速度。 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。 在经常使用WHERE子句的列上创建索引，加快条件的判断速度。 为经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段建立索引。 索引创建成功后，系统会自动判断何时引用索引。当系统认为使用索引比顺序扫描更快时，就会使用索引。 索引创建成功后，必须和表保持同步以保证能够准确地找到新数据，这样就增加了数据操作的负荷。因此请定期删除无用的索引。

dynamic_library_path 参数说明：设置数据查找动态加载的共享库文件的路径。当需要打开一个可以动态装载的模块并且在CREATE FUNCTION或LOAD命令里面声明的名称没有目录部分时，系统将搜索这个目录以查找声明的文件，仅sysadmin用户可以访问。 用于dynamic_library_path的数值必须是一个冒号分隔（Windows下是分号分隔）的绝对路径列表。当一个路径名称以特殊变量$libdir为开头时，会替换为GaussDB发布提供的模块安装路径。例如： 1 dynamic_library_path = '/usr/local/lib/postgresql:/opt/testgs/lib:$libdir' 该参数属于SUSET类型参数，请参考表1中对应设置方法进行设置。 取值范围：字符串 设置为空字符串，表示关闭自动路径搜索。 默认值：$libdir

local_preload_libraries 参数说明：指定一个或多个共享库，它们在开始连接前预先加载。多个加载库之间用逗号分隔，除了双引号，所有的库名都转换为小写。 并非只有系统管理员才能更改此选项，因此只能加载安装的标准库目录下plugins子目录中的库文件，数据库管理员有责任确保该目录中的库都是安全的。local_preload_libraries中指定的项可以明确含有该目录，例如$libdir/plugins/mylib；也可以仅指定库的名称，例如mylib（等价于$libdir/plugins/mylib）。 与shared_preload_libraries不同，在会话开始之前加载模块与在会话中使用到该模块的时候临时加载相比并不具有性能优势。相反，这个特性的目的是为了调试或者测量在特定会话中不明确使用LOAD加载的库。例如针对某个用户将该参数设为ALTER USER SET来进行调试。 当指定的库未找到时，连接会失败。 每一个支持GaussDB的库都有一个“magic block”用于确保兼容性，因此不支持GaussDB的库不能通过这个方法加载。 该参数属于BACKEND类型参数，请参考表1中对应设置方法进行设置。 取值范围：字符串 默认值：空

删除表中数据 在使用表的过程中，可能会需要删除已过期的数据，删除数据必须从表中整行的删除。 SQL不能直接访问独立的行，只能通过声明被删除行匹配的条件进行。如果表中有一个主键，用户可以指定准确的行。用户可以删除匹配条件的一组行或者一次删除表中的所有行。 使用DELETE命令删除行，如果删除表customer_t1中所有c_customer_sk为3869的记录： 1 openGauss=# DELETE FROM customer_t1 WHERE c_customer_sk = 3869; 如果执行如下命令之一，会删除表中所有的行。 1 openGauss=# DELETE FROM customer_t1; 1 2 或： openGauss=# TRUNCATE TABLE customer_t1; 全表删除的场景下，建议使用truncate，不建议使用delete。 删除创建的表： 1 openGauss=# DROP TABLE customer_t1; 父主题： 创建和管理表

参数说明 SESSION 声明的参数只对当前会话起作用。如果SESSION和LOCAL都没出现，则SESSION为缺省值。 如果在事务中执行了此命令，命令的产生影响将在事务回滚之后消失。如果该事务已提交，影响将持续到会话的结束，除非被另外一个SET命令重置参数。 LOCAL 声明的参数只在当前事务中有效。在COMMIT或ROLLBACK之后，会话级别的设置将再次生效。 不论事务是否提交，此命令的影响只持续到当前事务结束。一个特例是：在一个事务里面，即有SET命令，又有SET LOCAL命令，且SET LOCAL在SET后面，则在事务结束之前，SET LOCAL命令会起作用，但事务提交之后，则是SET命令会生效。 TIME ZONE timezone 用于指定当前会话的本地时区。 取值范围：有效的本地时区。该选项对应的运行时参数名称为TimeZone，DEFAULT缺省值为PRC。 CURRENT_SCHEMA schema CURRENT_SCHEMA用于指定当前的模式。 取值范围：已存在模式名称。 SCHEMA schema 同CURRENT_SCHEMA。此处的schema是个字符串。 例如：set schema 'public'; NAMES encoding_name 用于设置客户端的字符编码。等价于set client_encoding to encoding_name。 取值范围：有效的字符编码。该选项对应的运行时参数名称为client_encoding，默认编码为UTF8。 XML OPTION option 用于设置XML的解析方式。 取值范围：CONTENT（缺省）、DOCUMENT config_parameter 可设置的运行时参数的名称。可用的运行时参数可以使用SHOW ALL命令查看。 部分通过SHOW ALL查看的参数不能通过SET设置。如max_datanodes。 value config_parameter的新值。可以声明为字符串常量、标识符、数字，或者逗号分隔的列表。DEFAULT用于把这些参数设置为它们的缺省值。

语法格式 设置所处的时区。 1 SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }; 设置所属的模式。 1 2 3 SET [ SESSION | LOCAL ] {CURRENT_SCHEMA { TO | = } { schema | DEFAULT } | SCHEMA 'schema'}; 设置客户端编码集。 1 SET [ SESSION | LOCAL ] NAMES encoding_name; 设置XML的解析方式。 1 SET [ SESSION | LOCAL ] XML OPTION { DOCUMENT | CONTENT }; 设置其他运行时参数。 1 2 3 SET [ LOCAL | SESSION ] { {config_parameter { { TO | = } { value | DEFAULT } | FROM CURRENT }}};

示例 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 CREATE OR REPLACE PROCEDURE proc_case_branch(pi_result in integer, pi_return out integer) AS BEGIN CASE pi_result WHEN 1 THEN pi_return := 111; WHEN 2 THEN pi_return := 222; WHEN 3 THEN pi_return := 333; WHEN 6 THEN pi_return := 444; WHEN 7 THEN pi_return := 555; WHEN 8 THEN pi_return := 666; WHEN 9 THEN pi_return := 777; WHEN 10 THEN pi_return := 888; ELSE pi_return := 999; END CASE; raise info 'pi_return : %',pi_return ; END; / CALL proc_case_branch(3,0); --删除存储过程 DROP PROCEDURE proc_case_branch;

topsql_retention_time 参数说明：设置历史TopSQL中gs_wlm_session_query_info_all和gs_wlm_operator_info表中数据 的保存时间。 该参数属于SIGHUP类型参数，请参考表1中对应设置方法进行设置。 取值范围：整型，0 ~ 3650，单位为天。● 值为0时，表示数据永久保存。 ● 值大于0时，表示数据能够保存的对应天数。 默认值：0