华为云用户手册

枚举类型的声明 枚举类型可以使用CREATE TYPE命令创建，例如： 1 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); 枚举类型被创建后，可以在表和函数定义中使用： 1 2 3 4 5 6 7 8 CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); CREATE TABLE person (name text, current_mood mood); INSERT INTO person VALUES ('Moe', 'happy'); SELECT * FROM person WHERE current_mood = 'happy'; name | current_mood ------+-------------- Moe | happy (1 row)

修改数组 更新数组 更新整个数组数据： UPDATE books SET price_by_quarter = '{30,30,30,30}' WHERE title = 'Robinson Crusoe'; 使用ARRAY表达式语法更新整个数组数据： UPDATE books SET price_by_quarter = ARRAY[30,30,30,30] WHERE title = 'Robinson Crusoe'; 更新数组中的一个元素： UPDATE books SET price_by_quarter[4] = 35 WHERE title = 'Robinson Crusoe'; 更新数组中的一个切片元素： UPDATE books SET price_by_quarter[1:2] = '{27,27}' WHERE title = 'Robinson Crusoe'; 一个已存储的数组值可以被通过对其还不存在的元素赋值来扩大大小。任何位于已存在的元素和新元素之间的位置都将被空值填充。例如，如果数组myarray目前有4个元素，使用UPDATE对myarray[6]赋值后它将有6个元素，其中myarray[5]为空值。目前，采用这种方式扩大数组只允许使用在一维数组上。 构建新数组 新的数组值也可以通过串接操作符“||”构建。串接操作符允许把一个单独的元素加入到一个一维数组的开头或末尾。也可接受两个N维数组，或者一个N维数组和一个N+1维数组。 SELECT ARRAY[1,2] || ARRAY[3,4]; ?column? ----------- {1,2,3,4} (1 row) SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]]; ?column? --------------------- {{5,6},{1,2},{3,4}} (1 row) 使用函数array_prepend、array_append或array_cat构建数组。 SELECT array_prepend(1, ARRAY[2,3]); array_prepend --------------- {1,2,3} (1 row) SELECT array_append(ARRAY[1,2], 3); array_append -------------- {1,2,3} (1 row) SELECT array_cat(ARRAY[1,2], ARRAY[3,4]); array_cat ----------- {1,2,3,4} (1 row) SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]); array_cat --------------------- {{1,2},{3,4},{5,6}} (1 row) SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]); array_cat --------------------- {{5,6},{1,2},{3,4}} (1 row)

数组值输入 输入数组值时要把一个数组值写成一个文字常数，将元素值用花括号包围并用逗号分隔。一个数组常量的一般格式如下： 1 '{ val1 delim val2 delim ... }' 其中，delim是类型的定界符，每个val可以是数组元素类型的一个常量或子数组。 一个数组常量的例子如下： 1 '{{1,2,3},{4,5,6},{7,8,9}}' 该常量是一个二维的，3乘3数组，它由3个整数子数组构成。 向表books插入数据并查询表books： 1 2 3 4 5 6 7 8 9 10 11 12 INSERT INTO books VALUES (1, 'One Hundred years of Solitude','{25,25,25,25}','{{"fiction"}, {"adventure"}}'), (2, 'Robinson Crusoe', '{30,32,32,32}', '{{"adventure"}, {"fiction"}}'), (3, 'Gone with the Wind', '{27,27,29,28}', '{{"romance"}, {"fantasy"}}'); SELECT * FROM books; id | title | price_by_quarter | tags ----+-------------------------------+------------------+------------------------- 1 | One Hundred years of Solitude | {25,25,25,25} | {{fiction},{adventure}} 2 | Robinson Crusoe | {30,32,32,32} | {{adventure},{fiction}} 3 | Gone with the Wind | {27,27,29,28} | {{romance},{fantasy}} (3 rows) 插入多维数组数据时，多维数组的每一维都必须有相匹配的长度。 使用ARRAY关键字插入数据： INSERT INTO books VALUES (1, 'One Hundred years of Solitude',ARRAY[25,25,25,25],ARRAY['fiction', 'adventure']), (2, 'Robinson Crusoe', ARRAY[30,32,32,32], ARRAY['adventure', 'fiction']), (3, 'Gone with the Wind', ARRAY[27,27,29,28], ARRAY['romance', 'fantasy']);

数组类型的定义 一个数组数据类型可以通过在数组元素的数据类型名称后面加上方括号（[]）来命名。 例如，创建表books，其中表示书本价格的列price的类型为一维integer类型数组，表示书本标签的列tag的类型为二维text类型数组。 1 CREATE TABLE books (id SERIAL PRIMARY KEY, title VARCHAR(100), price_by_quarter int[], tags TEXT[][]); CREATE TABLE语法可以指定数组的大小，例如： 1 CREATE TABLE test ( a int[3]); 当前的数据库实现会忽略语句中数组的大小限制，即其行为与未指定长度的数组相同。同时，也不会强制所声明的维度数。一个特定元素类型的数组全部被当作是相同的类型，而忽略其大小或维度数。 也可以使用关键词ARRAY来定义一维数组。表books中的列price使用ARRAY定义并指定数组大小，如下所示： 1 price_by_quarter int ARRAY[4] 使用ARRAY定义，不指定数组尺寸： 1 price_by_quarter int ARRAY

语法格式 1 ARRAY [ param ] 或 1 '{ param }' 其中参数param说明如下： param ：数组包含的值，允许出现零个或多个，多个值之间用逗号分隔，没有值可填写为NULL。 以'{ param }' 这种格式作为数组常量时，其中的字符串类型的元素不能再以单引号开始和结束，需要使用双引号。两个连续单引号转换为一个单引号。 以第一个元素的数据类型作为数组的数据类型，要求数组中所有元素的类型相同，或者能够相互转换。

reload_cold_partition(relname text) 描述：用于将指定冷热表的所有冷分区转换成热分区。返回的数值为执行该函数切换分区的个数。该函数仅8.3.0及以上版本支持。 参数： relname：表名(指定冷热表表名，使用其他非冷热表表名，并不会报错，返回值为0) 返回值类型：integer 1 2 3 4 5 SELECT reload_cold_partition('multi_temper_table'); reload_cold_partition --------------------- 4 (1 row)

refresh_hot_storage(relname text) 描述：用于将指定冷热表的所有分区数据全部刷到OBS上。返回的数值为执行该函数切换分区的个数。该函数仅8.2.1.100及以上版本支持。 参数： relname：表名(指定冷热表表名，使用其他非冷热表表名，并不会报错，返回值为0) 返回值类型：integer 1 2 3 4 5 SELECT refresh_hot_storage('multi_temper_table'); refresh_hot_storage --------------------- 4 (1 row)

refresh_hot_storage(relname text，partname text) 描述：用于将指定冷热表的分区数据刷到OBS上。返回的数值为执行该函数切换分区的个数。该函数仅8.2.1.100及以上版本支持。 参数： relname：表名(指定冷热表表名，使用其他非冷热表表名，并不会报错，返回值为0) partname：分区名(指定冷热表下的分区名) 返回值类型：integer 1 2 3 4 5 SELECT refresh_hot_storage('multi_temper_table','p1'); refresh_hot_storage --------------------- 1 (1 row)

pg_refresh_storage() 描述：切换所有冷热表，将符合冷热切换规则的数据由热数据切换至冷数据（OBS中）。 返回值类型：int 返回值字段： success_count int：切换成功的表个数 failed_count int：切换失败的表个数 示例： 1 2 3 4 5 SELECT * FROM pg_refresh_storage(); success_count | failed_count ---------------+-------------- 1 | 0 (1 row)

pg_lifecycle_table_data_distribute(table_name) 描述：查看某个冷热表的数据分布情况。 table_name为表名，不可缺省。 返回值：record 示例：根据节点数量形成多条记录，如下示例为只有一个dn节点时w1表数据分布情况。 1 2 3 4 5 SELECT * FROM pg_catalog.pg_lifecycle_table_data_distribute('w1'); schemaname | tablename | nodename | hotpartition | coldpartition | switchablepartition | hotdatasize | colddatasize | switchabledatasize ------------+-----------+----------+--------------+---------------+---------------------+-------------+--------------+-------------------- public | w1 | dn_1 | p2 | p1 | | 80 KB | 0 bytes | 0 bytes (1 row)

pg_lifecycle_node_data_distribute() 描述：查看所有冷热表数据分布情况。 返回值：record 示例：数据库中当前存在两个冷热表，其数据分布情况如下。 1 2 3 4 5 6 SELECT * FROM pg_catalog.pg_lifecycle_node_data_distribute(); schemaname | tablename | nodename | hotpartition | coldpartition | switchablepartition | hotdatasize | colddatasize | switchabledatasize ------------+-----------+----------+--------------+---------------+---------------------+-------------+--------------+-------------------- public | w1 | dn_1 | p2 | p1 | | 81920 | 0 | 0 public | w2 | dn_1 | p2 | p1 | | 81920 | 0 | 0 (2 rows)

reload_cold_partition(relname text，partname text) 描述：用于将指定冷热表的特定冷分区转换成热分区。返回的数值为执行该函数切换分区的个数。该函数仅8.3.0及以上版本支持。 参数： relname：表名(指定冷热表表名，使用其他非冷热表表名，并不会报错，返回值为0) partname：分区名(指定冷热表下的分区名) 返回值类型：integer 1 2 3 4 5 SELECT reload_cold_partition('multi_temper_table','p1'); reload_cold_partition --------------------- 1 (1 row)

pg_obs_cold_refresh_time(table_name, time) 描述：用来修改冷热表的冷数据切换至OBS上的时间，默认为每日0点。 table_name为冷热表表名，类型为name，time为数据切换任务调度时间，类型为Time。 返回值：SUC CES S，任务时间修改成功。 示例： 1 2 3 4 5 SELECT * FROM pg_obs_cold_refresh_time('lifecycle_table', '06:30:00'); pg_obs_cold_refresh_time -------------------------- SUCCESS (1 row)

jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean]) 描述：返回target，并插入new_value。如果path指定的target部分位于JSONB数组中，则new_value将在目标之前或insert_after为true（默认值为false）之后插入。如果在JSONB对象中由path指定的target部分，则仅当target不存在时才插入new_value。与面向路径的运算符一样，path中出现的负整数从JSON数组的末尾开始计数。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'); jsonb_insert ------------------------------- {"a": [0, "new_value", 1, 2]} (1 row)

jsonb_pretty(jsonb) 描述：以缩进的JSON文本形式返回。 返回类型：jsonb 示例： 1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT jsonb_pretty('{"a":{"b":{"c":1, "d":2}}, "e":3}'::jsonb); jsonb_pretty --------------------- { + "a": { + "b": { + "c": 1,+ "d": 2 + } + }, + "e": 3 + } (1 row)

json_to_tsvector(config regconfig, ] json, jsonb) 描述：将json格式转换为用于支持全文检索的文件格式tsvector。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT json_to_tsvector('{"a":1, "b":2, "c":3}'::json, to_jsonb('key'::text)); json_to_tsvector ------------------ 'b':2 'c':4 (1 row)

jsonb_delete_array(jsonb, VARIADIC text[]) 描述：删除jsonb数组中的多个元素。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_delete_array('["a", "b", "c"]'::jsonb , 'a', 'b'); jsonb_delete_array -------------------- ["c"] (1 row)

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean]) 描述：返回target，用path指定的部分被new_value替换，或者如果create_missing为true（默认值为true）且path指定的项不存在，则添加new_value。与面向路径的运算符一样，path中出现的负整数从JSON数组的末尾开始计数。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false); jsonb_set --------------------------------------------- [{"f1": [2, 3, 4], "f2": null}, 2, null, 3] (1 row)

jsonb_to_record(object-json) 描述：正如所有返回record的函数一样，调用者必须用一个AS子句显式地定义记录的结构。会将object-json的键值对进行拆分重组，把键当做列名，去匹配填充AS显示指定的记录的结构。 返回类型：record 示例： 1 2 3 4 5 SELECT * FROM jsonb_to_record('{"a":1,"b":"foo","c":"bar"}'::jsonb) as x(a int, b text, d text); a | b | d ---+-----+--- 1 | foo | (1 row)

jsonb_delete_path(jsonb, text[]) 描述：删除jsonb数组中指定路径的元素。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_delete_path('{"a":{"b":{"c":1, "d":2}}, "e":3}'::jsonb , array['a', 'b']); jsonb_delete_path ------------------- {"a": {}, "e": 3} (1 row)

jsonb_to_recordset(array-json) 描述：参考函数jsonb_to_record，对数组内个每个元素，执行上述函数的操作，因此这要求数组内的每个元素都得是object-jsonb。 返回类型：setof record 示例： 1 2 3 4 5 6 SELECT * FROM jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') AS x(a INT, b text, c boolean); a | b | c ---+-----+--- 1 | foo | 2 | bar | t (2 rows)

jsonb_populate_record(anyelement, object-jsonb [, bool]) 描述：$1必须是一个复合类型的参数。将会把object-json里的每个对键值进行拆分，以键当做列名，与$1中的列名进行匹配查找，并填充到$1的格式中。 返回类型：anyelement 示例： 1 2 3 4 5 SELECT * FROM jsonb_populate_record(null::jpop,'{"a":"blurfl","x":43.2}'); a | b | c --------+---+--- blurfl | | (1 row)

jsonb_populate_record_set(anyelement, array-jsonb [, bool]) 描述：参考上述函数json_populate_record、jsonb_populate_record，对$2数组的每一个元素进行上述参数函数的操作，因此这也要求$2数组的每个元素都是object-json类型。 返回类型：setof anyelement 示例： 1 2 3 4 5 6 SELECT * FROM json_populate_recordset(null::jpop, '[{"a":1,"b":2},{"a":3,"b":4}]'); a | b | c ---+---+--- 1 | 2 | 3 | 4 | (2 rows)

jsonb_exists_any(jsonb, text[]) 描述：同操作符?|，字符串数组$2里面是否存在的元素，在$1的顶层以key\elem\scalar的形式存在。 返回类型： 示例： 1 2 3 4 5 SELECT jsonb_exists_any('["1","2",3]', '{1, 2, 4}'); jsonb_exists_any ------------------ t (1 row)

jsonb_exists_all(jsonb, text[]) 描述：同操作符?&，字符串数组$2里面是否所有的元素，都在$1的顶层以key\elem\scalar的形式存在。 返回类型： bool 示例： 1 2 3 4 5 SELECT jsonb_exists_all('["1","2",3]', '{1, 2}'); jsonb_exists_all ------------------ t (1 row)

jsonb_concat(jsonb, jsonb) 描述：连接两个jsonb对象为一个jsonb。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_concat('{"a":1, "b":2}'::jsonb, '{"c":3, "d":4}'::jsonb); jsonb_concat ---------------------------------- {"a": 1, "b": 2, "c": 3, "d": 4} (1 row)

jsonb_object_keys(object-jsonb) 描述：返回对象中顶层的所有键。 返回类型：SETOF text 示例： 1 2 3 4 5 6 SELECT jsonb_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}, "f1":"abcd"}'); jsonb_object_keys ------------------- f1 f2 (2 rows)

jsonb_extract((jsonb, VARIADIC text[]) 描述：输入任意的object-jsonb类型、array-jsonb类型，返回$2所指路径的值。该函数仅9.1.0及以上集群版本支持。 返回类型：SETOF jsonb 示例： 1 2 3 4 5 6 7 8 9 10 11 12 SELECT jsonb_extract('{"f2":{"f3":1},"f4":[{"f5":99},{"f6":"stringy"}]}','f2','f3'); jsonb_extract --------------- 1 (1 row) SELECT jsonb_extract('{"f2":{"f3":1},"f4":[{"f5":99},{"f5":"stringy"}]}', 'f4','f5'); jsonb_extract --------------- 99 "stringy" (2 rows)

jsonb_build_object( [VARIADIC “any”] ) 描述：从一个可变参数列表构造出一个JSON对象，其入参必须为偶数个，两两一组组成键值对。注意键不可为null。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_build_object(1,2); jsonb_build_object -------------------- {"1": 2} (1 row)

jsonb_strip_nulls(jsonb) 描述：所有具有空值的对象字段均被省略。其他空值保持不变。 返回类型：jsonb 示例： 1 2 3 4 5 SELECT jsonb_strip_nulls('[{"f1":1,"f2":null},2,null,3]'); jsonb_strip_nulls ------------------------- [{"f1": 1}, 2, null, 3] (1 row)