华为云用户手册

tsquery tsquery类型表示一个检索条件，存储用于检索的词汇，并且使用布尔操作符&（AND），|（OR）和!（NOT）将这些词汇进行组合，圆括号用来强调操作符的分组。如果没有圆括号，（NOT）的优先级最高，其次是&（AND），最后是|（OR）。to_tsquery函数及plainto_tsquery函数会将单词转换为tsquery类型前进行规范化处理。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT 'fat & rat'::tsquery; tsquery --------------- 'fat' & 'rat' (1 row) SELECT 'fat & (rat | cat)'::tsquery; tsquery --------------------------- 'fat' & ( 'rat' | 'cat' ) (1 row) SELECT 'fat & rat & ! cat'::tsquery; tsquery ------------------------ 'fat' & 'rat' & !'cat' (1 row) tsquery中的词汇可以用一个或多个权重字母来标记，这些权重字母限制词汇只能与匹配权重的tsvector词汇进行匹配。 1 2 3 4 5 SELECT 'fat:ab & cat'::tsquery; tsquery ------------------ 'fat':AB & 'cat' (1 row) 同样，tsquery中的词汇可以用*标记来指定前缀匹配。例如：这个查询可以匹配tsvector中以“super”开始的任意单词。 1 2 3 4 5 SELECT 'super:*'::tsquery; tsquery ----------- 'super':* (1 row) 需注意，前缀匹配会首先被文本搜索分词器处理。例如：postgres中提取的词干是postgr，匹配到了postgraduate，也就意味着下面的结果为真： 1 2 3 4 5 SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' ) AS RESULT; result ---------- t (1 row) 1 2 3 4 5 SELECT to_tsquery('postgres:*'); to_tsquery ------------ 'postgr':* (1 row) to_tsquery函数会将单词转换为tsquery类型前进行规范化处理。'Fat:ab & Cats'规范化转为tsquery类型结果如下： 1 2 3 4 5 SELECT to_tsquery('Fat:ab & Cats'); to_tsquery ------------------ 'fat':AB & 'cat' (1 row)

tsvector tsvector类型表示一个检索单元，通常是一个数据库表中的一行文本字段或者这些字段的组合。 tsvector类型的值是唯一分词的分类列表，把一句话的词格式化为不同的词条，在进行分词处理的时候tsvector会按照一定的顺序录入，并自动去掉分词中重复的词条。 to_tsvector函数通常用于解析和标准化文档字符串。 通过tsvector把一个字符串按照空格进行分词，分词的顺序是按照字母和长短排序的，请看以下例子： 1 2 3 4 5 SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector; tsvector ---------------------------------------------------- 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat' (1 row) 如果词条中包含空格或标点符号，可以用引号包围： 1 2 3 4 5 SELECT $$the lexeme ' ' contains spaces$$::tsvector; tsvector ------------------------------------------- ' ' 'contains' 'lexeme' 'spaces' 'the' (1 row) 使用常规的单引号引起来的字符串，字符串中嵌入的单引号(')和反斜杠(\)必须双写进行转义： 1 2 3 4 5 SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector; tsvector ------------------------------------------------ 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the' (1 row) 词条位置常量也可以放到词汇中： 1 2 3 4 5 SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector; tsvector ------------------------------------------------------------------------------- 'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4 (1 row) 位置常量通常表示文档中源字的位置。位置信息可以用于进行排名。位置常量的范围是1到16383，最大值默认是16383。相同词的重复位会被忽略掉。 拥有位置的词汇可以进一步地被标注一个权重，它可以是A，B，C或D。 D是默认权重，因此输出中不会显示： 1 2 3 4 5 SELECT 'a:1A fat:2B,4C cat:5D'::tsvector; tsvector ---------------------------- 'a':1A 'cat':5 'fat':2B,4C (1 row) 权重通常被用来反映文档结构，如：将标题标记成与正文词不同。文本检索排序函数可以为不同的权重标记分配不同的优先级。 tsvector类型标准用法示例如下： 1 2 3 4 5 SELECT 'The Fat Rats'::tsvector; tsvector -------------------- 'Fat' 'Rats' 'The' (1 row) 但是对于英文全文检索应用来说，上面的单词会被认为非规范化的，所以需要通过to_tsvector函数对这些单词进行规范化处理： 1 2 3 4 5 SELECT to_tsvector('english', 'The Fat Rats'); to_tsvector ----------------- 'fat':2 'rat':3 (1 row)

md5(string) 描述：将string使用MD5加密，并以16进制数作为返回值。 MD5的安全性较低，不建议使用。 返回值类型：text 示例： 1 2 3 4 5 SELECT md5('ABC'); md5 ---------------------------------- 902fbdd2b1df0c4f70b4a5d23525e932 (1 row)

CONV(n, fromBase, toBase) 描述： 将给定的数值或者字符串转换成目标进制，并按照字符串的形式输出结果。若参数含有NULL值，返回NULL。进制表示范围为[-36, -2]&[2, 36]。 返回值类型：text 示例： 1 2 3 4 5 SELECT CONV(-1, 10, 16) as result; result ------------------ FFFFFFFFFFFFFFFF (1 row)

HEX(n) 描述：n可以是int类型也可以是字符串。返回n的十六进制字符串。若参数含有NULL值，返回NULL。 返回值类型：text 示例： 1 2 3 4 5 6 7 8 9 10 SELECT HEX(255) as result; result -------- FF (1 row) SELECT HEX('abc') as result; result -------- 616263 (1 row)

decode(string text, format text) 描述：将二进制数据从文本数据中解码。 返回值类型：bytea 示例： 1 2 3 4 5 6 7 8 9 10 11 SELECT decode('ZGF0YWJhc2U=', 'base64'); decode -------------- \x6461746162617365 (1 row) SELECT convert_from('\x6461746162617365','utf-8'); convert_from -------------- database (1 row)

UNHEX(n) 描述：执行HEX(n)的反向操作，n可以是int类型也可以是字符串，将参数中的每一对十六进制数字理解为一个数字，并将其转化为该数字代表的字符。若参数含有NULL值，返回NULL。该函数仅8.2.0及以上集群版本支持。 返回值类型：bytea 示例： 1 2 3 4 5 SELECT UNHEX('abc') as result; result -------- 0x0ABC (1 row)

STRCMP(text, text) 描述：比较两个字符串大小，若所有的字符串均相同，则返回0，若根据当前分类次序，第一个字符串小于第二个，则返回-1，其它情况返回1。若参数含有NULL值，返回NULL。 返回值类型：text 示例： 1 2 3 4 5 SELECT STRCMP('AA', 'AA'), STRCMP('AA', 'AB'), STRCMP('AA', 'A'); STRCMP | STRCMP | STRCMP ------------------------------ 0 | -1 | 1 (1 row)

convert(string bytea, src_encoding name, dest_encoding name) 描述：以dest_encoding指定的目标编码方式转化字符串bytea。src_encoding指定源编码方式，在该编码下，string必须是合法的。 返回值类型：bytea 示例： 1 2 3 4 5 SELECT convert('text_in_utf8', 'UTF8', 'GBK'); convert ---------------------------- \x746578745f696e5f75746638 (1 row) 如果源编码格式到目标编码格式的转化规则不存在，则字符串不进行任何转换直接返回，如GBK和LATIN1之间的转换规则是不存在的，具体转换规则可以通过查看系统表pg_conversion获得。 示例： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 show server_encoding; server_encoding ----------------- LATIN1 (1 row) SELECT convert_from('some text', 'GBK'); convert_from -------------- some text (1 row) db_latin1=# SELECT convert_to('some text', 'GBK'); convert_to ---------------------- \x736f6d652074657874 (1 row) db_latin1=# SELECT convert('some text', 'GBK', 'LATIN1'); convert ---------------------- \x736f6d652074657874 (1 row)

convert_to(string text, dest_encoding name) 描述：将字符串转化为dest_encoding的编码格式。 返回值类型：bytea 示例： 1 2 3 4 5 6 7 8 9 10 SELECT convert_to('some text', 'UTF8'); convert_to ---------------------- \x736f6d652074657874 (1 row) SELECT convert_to('database', 'gbk'); convert_to -------------------- \x6461746162617365 (1 row)

REGEXP_LIKE(source_string, pattern [, match_parameter]) 描述：正则表达式的模式匹配函数。 source_string为源字符串，pattern为正则表达式匹配模式。 match_parameter为匹配选项，可取值为： 'i'：大小写不敏感。 'c'：大小写敏感。 'n'：允许正则表达式元字符“.”匹配换行符。 'm'：将source_string视为多行。 若忽略match_parameter选项，默认为大小写敏感，“.”不匹配换行符，source_string视为单行。 返回值类型：boolean 示例： 1 2 3 4 5 SELECT regexp_like('ABC', '[A-Z]'); regexp_like ------------- t (1 row) 1 2 3 4 5 SELECT regexp_like('ABC', '[D-Z]'); regexp_like ------------- f (1 row) 1 2 3 4 5 SELECT regexp_like('abc', '[A-Z]','i'); regexp_like ------------- t (1 row) 1 2 3 4 5 SELECT regexp_like('abc', '[A-Z]'); regexp_like ------------- f (1 row)

string [NOT] LIKE pattern [ESCAPE escape-character] 描述：模式匹配函数。 如果pattern不包含百分号或者下划线，该模式只代表它本身，这时候LIKE的行为就像等号操作符。在pattern里的下划线（_）匹配任何单个字符；而一个百分号（%）匹配零或多个任何字符。 要匹配下划线或者百分号本身，在pattern里相应的字符必须前导逃逸字符。缺省的逃逸字符是反斜杠，但是用户可以用ESCAPE子句指定一个。要匹配逃逸字符本身，写两个逃逸字符。 返回值类型：boolean 示例： 1 2 3 4 5 SELECT 'AA_BBCC' LIKE '%A@_B%' ESCAPE '@' AS RESULT; result -------- t (1 row) 1 2 3 4 5 SELECT 'AA_BBCC' LIKE '%A@_B%' AS RESULT; result -------- f (1 row) 1 2 3 4 5 SELECT 'AA@_BBCC' LIKE '%A@_B%' AS RESULT; result -------- t (1 row)

convert_from(string bytea, src_encoding name) 描述：以数据库的编码方式转化字符串bytea。 src_encoding指定源编码方式，在该编码下，string必须是合法的。 返回值类型：text 示例： 1 2 3 4 5 6 7 8 9 10 SELECT convert_from('text_in_utf8', 'UTF8'); convert_from -------------- text_in_utf8 (1 row) SELECT convert_from('\x6461746162617365','gbk'); convert_from -------------- database (1 row)

replace(string varchar, search_string varchar, replacement_string varchar) 描述：把字符串string中所有子字符串search_string替换成子字符串replacement_string。 返回值类型：varchar 示例： 1 2 3 4 5 SELECT replace('jack and jue','j','bl'); replace ---------------- black and blue (1 row)

lpad(string varchar, length int[, repeat_string varchar]) 描述：在string的左侧添上一系列的repeat_string（缺省为空白）来组成一个总长度为n的新字符串。 如果string本身的长度比指定的长度length长，则本函数将把string截断并把前面长度为length的字符串内容返回。 返回值类型：varchar 示例： 1 2 3 4 5 SELECT lpad('PAGE 1',15,'*.'); lpad ----------------- *.*.*.*.*PAGE 1 (1 row) 1 2 3 4 5 SELECT lpad('hello world',5,'abcd'); lpad ------- hello (1 row)

concat(str1,str2) 描述：将字符串str1和str2连接并返回。 ORA和TD兼容模式下，返回结果为所有非NULL字符串的连接。 MySQL兼容模式下，入参中存在NULL时，返回结果为NULL。 返回值类型：varchar 示例： 1 2 3 4 5 SELECT concat('Hello', ' World!'); concat -------------- Hello World! (1 row)

regexp_substr(source_char, pattern) 描述：正则表达式的抽取子串函数。 返回值类型：varchar 示例： 1 2 3 4 5 SELECT regexp_substr('500 Hello World, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR"; REGEXPR_SUBSTR ------------------- , Redwood Shores, (1 row)

locate(substring,string[,position]) 描述：从字符串string的position（缺省时为1）所指的位置开始查找并返回第一次出现子串substring位置的值。以字符为计算单位。当string中不存在substring时，返回0。 返回值类型：integer 示例： 1 2 3 4 5 SELECT locate('ball','football'); locate -------- 5 (1 row) 1 2 3 4 5 SELECT locate('er','soccerplayer','6'); locate -------- 11 (1 row)

regexp_replace(string, pattern, replacement [,flags ]) 描述：替换匹配POSIX正则表达式的子字符串。 如果没有匹配pattern，那么返回不加修改的string串。 如果有匹配，则返回的string串里面的匹配子串将被replacement串替换掉。 replacement串可以包含\n， 其中\n是1到9， 表明string串里匹配模式里第n个圆括号子表达式的子串应该被插入， 并且它可以包含\&表示应该插入匹配整个模式的子串。 可选的flags参数包含零个或多个改变函数行为的单字母标记，见下表。 表1 flags参数的可选项 选项 描述 g 表示替换每一个匹配的子字符串而不仅仅是第一个（默认仅替换第一个匹配的子字符串） B 默认情况下使用Henry Spencer正则库及其正则语法。指定B选项后，表示优先选用boost regex正则库及其正则语法。 以下两种情况在指定了B选项时，也会自动转换为选择Henry Spencer正则库及其正则语法： flags同时指定了p、q、w、x中的任意个字符。 string或pattern参数中含有多字节字符。 b 表示按照BRE(POSIX Basic Regular Expression)匹配模式的规则进行匹配。 c 大小写敏感匹配 e 表示按照ERE(POSIX Extended Regular Expression)匹配模式的规则进行匹配。当b和e都未指定时，如果选用的是Henry Spencer正则库，则按照ARE(Advanced Regular Expression，类似于Perl Regular Expression)匹配模式的规则进行匹配；如果选用的是boost regex正则库，则按照Perl Regular Expression匹配模式的规则进行匹配。 i 大小写不敏感匹配 m 换行敏感匹配，与选项n同义。 n 换行敏感匹配。此选项生效时，换行符影响元字符(.、^、$和[^)的匹配。 p 部分换行敏感匹配，此选项生效时，换行符影响元字符(.和[^)的匹配。部分是相对选项n而言。 q 重置正则表达式为加双引号的文本字符串，所有都是普通字符。 s 非换行敏感匹配。 t 紧凑语法（缺省）。该选项生效时，所有字符都很重要。 w 反部分换行敏感匹配，此选项生效时，换行符影响元字符(^和$)的匹配。部分是相对选项n而言。 x 扩展语法。与紧凑语法相对，在扩展的语法中，正则表达式中的空白字符被忽略。空白字符包括空格、水平制表符、新行、和任何属于space字符表的字符。 返回值类型：varchar 示例： 1 2 3 4 5 6 7 8 9 10 SELECT regexp_replace('Thomas', '.[mN]a.', 'M'); regexp_replace ---------------- ThM (1 row) SELECT regexp_replace('foobarbaz','b(..)', E'X\\1Y', 'g') AS RESULT; result ------------- fooXarYXazY (1 row)

substr(string,from,count) 描述： 从参数string中抽取子字符串。 from表示抽取的起始位置。 count表示抽取的子字符串长度。 from为0时，按1处理。 from为正数时，抽取从from开始的count个字符。 from为负数时，抽取从倒数第n个开始的count个字符，n为from的绝对值。 count小于1时，返回null。 返回值类型：varchar 示例： from为正数时： 1 2 3 4 5 SELECT substr('ABCDEF',2,2); substr -------- BC (1 row) from为负数时： 1 2 3 4 5 SELECT substr('ABCDEF',-3,2); substr -------- DE (1 row)

trim([leading |trailing |both] [characters] from string) 描述：从字符串string的开头、结尾或两边删除只包含characters中字符（缺省是一个空白）的最长的字符串。 返回值类型：varchar 示例： 1 2 3 4 5 SELECT trim(BOTH 'x' FROM 'xTomxx'); btrim ------- Tom (1 row) 1 2 3 4 5 SELECT trim(LEADING 'x' FROM 'xTomxx'); ltrim ------- Tomxx (1 row) 1 2 3 4 5 SELECT trim(TRAILING 'x' FROM 'xTomxx'); rtrim ------- xTom (1 row)

substr(string,from) 描述： 从参数string中抽取子字符串。 from表示抽取的起始位置。 from为0时，按1处理。 from为正数时，抽取从from到末尾的所有字符。 from为负数时，抽取字符串的后n个字符，n为from的绝对值。 返回值类型：varchar 示例： from为正数时： 1 2 3 4 5 SELECT substr('ABCDEF',2); substr -------- BCDEF (1 row) from为负数时： 1 2 3 4 5 SELECT substr('ABCDEF',-2); substr -------- EF (1 row)

instr(string,substring[,position,occurrence]) 描述：从字符串string的position（缺省时为1）所指的位置开始查找并返回第occurrence（缺省时为1）次出现子串substring的位置的值。 当position为0时，返回0。 当position为负数时，从字符串倒数第n个字符往前逆向搜索。n为position的绝对值。 本函数以字符为计算单位，如一个汉字为一个字符。 返回值类型：integer 示例： 1 2 3 4 5 SELECT instr('corporate floor','or', 3); instr ------- 5 (1 row) 1 2 3 4 5 SELECT instr('corporate floor','or',-3,2); instr ------- 2 (1 row)

translate(string text, from text, to text) 描述：把在string中包含的任何匹配from中字符的字符转化为对应的在to中的字符。如果from比to长，删掉在from中出现的额外的字符。 返回值类型：text 示例： 1 2 3 4 5 SELECT translate('12345', '143', 'ax'); translate ----------- a2x5 (1 row)

rpad(string varchar, length int [, fill varchar]) 描述：使用填充字符fill（缺省时为空白），把string填充到length长度。如果string已经比length长则将其从尾部截断。 length参数在 GaussDB (DWS)中表示字符长度。一个汉字长度计算为一个字符。 返回值类型：varchar 示例： 1 2 3 4 5 SELECT rpad('hi',5,'xyza'); rpad ------- hixyz (1 row) 1 2 3 4 5 SELECT rpad('hi',5,'abcdefg'); rpad ------- hiabc (1 row)

substring(string from pattern) 描述：截取匹配POSIX正则表达式的子字符串。如果没有匹配它返回空值，否则返回文本中匹配模式的那部分。 返回值类型：text 示例： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SELECT substring('Thomas' from '...$'); substring ----------- mas (1 row) SELECT substring('foobar' from 'o(.)b'); result -------- o (1 row) SELECT substring('foobar' from '(o(.)b)'); result -------- oob (1 row) 如果POSIX正则表达式模式包含任何圆括号，那么将返回匹配第一对子表达式（对应第一个左圆括号的） 的文本。如果想在表达式里使用圆括号而又不想导致这个例外，那么可以在整个表达式外边加上一对圆括号。

regexp_split_to_array(string text, pattern text [, flags text ]) 描述：用POSIX正则表达式作为分隔符，分隔string。和regexp_split_to_table相同，不过regexp_split_to_array会把它的结果以一个text数组的形式返回。 返回值类型：text[] 示例： 1 2 3 4 5 SELECT regexp_split_to_array('hello world', E'\\s+'); regexp_split_to_array ----------------------- {hello,world} (1 row)

regexp_matches(string text, pattern text [, flags text]) 描述：返回string中所有匹配POSIX正则表达式的子字符串。如果pattern不匹配，该函数不返回行。如果模式不包含圆括号子表达式，则每一个被返回的行都是一个单一元素的文本数组，其中包括匹配整个模式的子串。如果模式包含圆括号子表达式，该函数返回一个文本数组，它的第n个元素是匹配模式的第n个圆括号子表达式的子串。 flags参数为可选参数，包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配，g表示替换每一个匹配的子字符串而不仅仅是第一个。 如果提供了最后一个参数，但参数值是空字符串（''），且数据库SQL兼容模式设置为ORA的情况下，会导致返回结果为空集。这是因为ORA兼容模式将''作为NULL处理，避免此类行为的方式有如下几种： 将数据库SQL兼容模式改为TD； 不提供最后一个参数，或最后一个参数不为空字符串。 返回值类型：setof text[] 示例： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SELECT regexp_matches('foobarbequebaz', '(bar)(beque)'); regexp_matches ---------------- {bar,beque} (1 row) SELECT regexp_matches('foobarbequebaz', 'barbeque'); regexp_matches ---------------- {barbeque} (1 row) SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g'); result -------------- {bar,beque} {bazil,barf} (2 rows)

regexp_split_to_table(string text, pattern text [, flags text]) 描述：用POSIX正则表达式作为分隔符，分隔string。如果没有与pattern的匹配，该函数返回string。如果有至少有一个匹配，对每一个匹配它都返回从上一个匹配的末尾（或者串的开头）到这次匹配开头之间的文本。当没有更多匹配时，它返回从上一次匹配的末尾到串末尾之间的文本。 flags参数包含零个或多个改变函数行为的单字母标记。i表示进行大小写无关的匹配，g表示替换每一个匹配的子字符串而不仅仅是第一个。 返回值类型：setof text 示例： 1 2 3 4 5 6 SELECT regexp_split_to_table('hello world', E'\\s+'); regexp_split_to_table ----------------------- hello world (2 rows)

substring(string from pattern for escape) 描述：截取匹配SQL正则表达式的子字符串。声明的模式必须匹配整个数据串，否则函数失败并返回空值。为了标识在成功的时候应该返回的模式部分，模式必须包含逃逸字符的两次出现，并且后面要跟上双引号（"）。匹配这两个标记之间的模式的文本将被返回。 返回值类型：text 示例： 1 2 3 4 5 SELECT substring('Thomas' from '%#"o_a#"_' for '#'); substring ----------- oma (1 row)