文档首页/ 云数据库 GaussDB/ 开发指南（集中式_V2.0-8.x）/ SQL参考/ 函数和操作符/ 字符处理函数和操作符/ 扩展函数和操作符
更新时间：2025-05-29 GMT+08:00
查看PDF
分享

扩展函数和操作符

pkg_bpchar_opc()

描述：扩展接口，用于新增bpchar和text或者text和bpchar策略比较操作符，为解决bpchar类型和text类型数据比较，无法命中索引问题。仅系统管理员可以安装扩展。

示例：

bpchar类型和text类型比较时（初始状态，前向兼容）: 
 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
 
/*
建表和数据初始化。
*/
gaussdb=# CREATE TABLE logs_nchar(log_id nchar(16), log_message text);
gaussdb=# INSERT INTO logs_nchar SELECT GENERATE_SERIES(1,100000),MD5(RANDOM());
gaussdb=# INSERT INTO logs_nchar VALUES ('FE306991300002  ','002');
gaussdb=# CREATE INDEX idx_nchar_logid on logs_nchar(log_id);
gaussdb=# VACUUM ANALYZE logs_nchar;

/*
没有安装扩展时候，nchar和text比较时候，由于没有bpchar和text索引操作符，nchar会隐式转换为text,即定长字符类型转换成变长字符类型，导致执行计划发生了变化，不能命中索引。
*/
gaussdb=# EXPLAIN SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002  '),16,' ');
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on logs_nchar  (cost=0.00..2236.01 rows=500 width=50)
   Filter: ((log_id)::text = 'FE306991300002  '::text)
(2 rows)

/*
表logs_nchar里log_id字段类型是nchar(16)，插入数据为'FE306991300002  '，隐式转换成text，进行比较时，会把后面空格去掉进行比较，即'FE306991300002'='FE306991300002  '，所以不命中数据。
*/
gaussdb=# SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002  '),16,' ');
 log_id | log_message 
--------+-------------
(0 rows)

/*
删除表。
*/
gaussdb=# DROP TABLE logs_nchar;
bpchar类型和text类型比较时（安装pkg_bpchar_opc扩展，与A保持一致）: 
 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
 
/*
建表和数据初始化。
*/
gaussdb=# CREATE TABLE logs_nchar(log_id nchar(16), log_message text);
gaussdb=# INSERT INTO logs_nchar SELECT GENERATE_SERIES(1,100000),MD5(RANDOM());
gaussdb=# INSERT INTO logs_nchar VALUES ('FE306991300002  ','002');
gaussdb=# CREATE INDEX idx_nchar_logid on logs_nchar(log_id);
gaussdb=# VACUUM ANALYZE logs_nchar;

/*
系统管理员安装pkg_bpchar_opc扩展，数据库增加了bpchar和text类型比较操作符，以及索引相关内容。
*/
gaussdb=# CREATE EXTENSION pkg_bpchar_opc;
CREATE EXTENSION

/*
此时，log_id隐式转换为bpchar类型时，和text类型比较时，能找到比较操作符以及索引信息，能命中索引。
*/
gaussdb=# EXPLAIN SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002  '),16,' ');
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 [Bypass]
 Index Scan using idx_nchar_logid on logs_nchar  (cost=0.00..8.27 rows=1 width=50)
   Index Cond: (log_id = 'FE306991300002  '::text)
(3 rows)

gaussdb=# SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002  '),16,' ');
      log_id      | log_message 
------------------+-------------
 FE306991300002   | 002
(1 row)

/*
删除表和扩展。
*/
gaussdb=# DROP TABLE logs_nchar;
gaussdb=# DROP EXTENSION pkg_bpchar_opc;
text类型和bpchar类型比较时（初始状态，前向兼容）: 
 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
 
/*
建表和数据初始化。
*/
gaussdb=# CREATE TABLE logs_text(log_id nchar(16), log_message text);
gaussdb=# INSERT INTO logs_text SELECT GENERATE_SERIES(1,100000),MD5(RANDOM());
gaussdb=# INSERT INTO logs_text VALUES ('FE306991300002  ','002');
gaussdb=# CREATE INDEX idx_text_logid on logs_text(log_id);
gaussdb=# VACUUM ANALYZE logs_text;

gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002  '::bpchar;
 log_id | log_message 
--------+-------------
(0 rows)

gaussdb=# EXPLAIN SELECT * FROM logs_text WHERE log_id = 'FE306991300002  '::bpchar;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 [Bypass]
 Index Scan using idx_text_logid on logs_text  (cost=0.00..8.27 rows=1 width=37)
   Index Cond: (log_id = 'FE306991300002'::text)
(3 rows)

/*
删除表。
*/
gaussdb=# DROP TABLE logs_text;
text类型和bpchar类型比较时（安装pkg_bpchar_opc扩展，与A保持一致）: 
 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
 
/*
建表和数据初始化。
*/
gaussdb=# CREATE TABLE logs_text(log_id nchar(16), log_message text);
gaussdb=# INSERT INTO logs_text SELECT GENERATE_SERIES(1,100000),MD5(RANDOM());
gaussdb=# INSERT INTO logs_text VALUES ('FE306991300002  ','002');
gaussdb=# CREATE INDEX idx_text_logid on logs_text(log_id);
gaussdb=# VACUUM ANALYZE logs_text;

gaussdb=# CREATE EXTENSION pkg_bpchar_opc;
CREATE EXTENSION

gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002  '::bpchar;
      log_id      | log_message 
------------------+-------------
 FE306991300002   | 002
(1 row)

gaussdb=# EXPLAIN SELECT * FROM logs_text WHERE log_id = 'FE306991300002  '::bpchar;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 [Bypass]
 Index Scan using idx_text_logid on logs_text  (cost=0.00..8.27 rows=1 width=38)
   Index Cond: (log_id = 'FE306991300002  '::bpchar)
(3 rows)

/*
删除表和扩展。
*/
gaussdb=# DROP TABLE logs_text;
gaussdb=# DROP EXTENSION pkg_bpchar_opc;
hash join、text类型和bpchar类型比较时（初始状态，前向兼容）: 
 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
 
/*
建表和数据初始化。
*/
gaussdb=# CREATE TABLE logs_varchar2(log_id varchar2, log_message text);
gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300002  ','002');
gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300003  ','003');
gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300004  ','004');
gaussdb=# VACUUM ANALYZE logs_varchar2;

gaussdb=# CREATE TABLE logs_char(log_id char(16), log_message text);
gaussdb=# INSERT INTO logs_char VALUES ('FE306991300002  ','002');
gaussdb=# INSERT INTO logs_char VALUES ('FE306991300003  ','003');
gaussdb=# INSERT INTO logs_char VALUES ('FE306991300004  ','004');
gaussdb=# VACUUM ANALYZE logs_char;

gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id;
      log_id      | log_message |      log_id      | log_message 
------------------+-------------+------------------+-------------
 FE306991300002   | 002         | FE306991300002   | 002
 FE306991300003   | 003         | FE306991300003   | 003
 FE306991300004   | 004         | FE306991300004   | 004
(3 rows)

gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Hash Join  (cost=1.07..2.14 rows=3 width=42)
   Hash Cond: ((t1.log_id)::bpchar = t2.log_id)
   ->  Seq Scan on logs_varchar2 t1  (cost=0.00..1.03 rows=3 width=21)
   ->  Hash  (cost=1.03..1.03 rows=3 width=21)
         ->  Seq Scan on logs_char t2  (cost=0.00..1.03 rows=3 width=21)
(5 rows)

gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002  ';
      log_id      | log_message |      log_id      | log_message 
------------------+-------------+------------------+-------------
 FE306991300002   | 002         | FE306991300002   | 002
 FE306991300002   | 002         | FE306991300003   | 003
 FE306991300002   | 002         | FE306991300004   | 004
(3 rows)

/*
删除表。
*/
gaussdb=# DROP TABLE logs_varchar2;
gaussdb=# DROP TABLE logs_char;
hash join、text类型和bpchar类型比较时（安装pkg_bpchar_opc扩展，与A保持一致）: 
  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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
/*
建表和数据初始化。
*/
gaussdb=# CREATE TABLE logs_varchar2(log_id varchar2, log_message text);
gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300002  ','002');
gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300003  ','003');
gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300004  ','004');
gaussdb=# VACUUM ANALYZE logs_varchar2;

gaussdb=# CREATE TABLE logs_char(log_id char(16), log_message text);
gaussdb=# INSERT INTO logs_char VALUES ('FE306991300002  ','002');
gaussdb=# INSERT INTO logs_char VALUES ('FE306991300003  ','003');
gaussdb=# INSERT INTO logs_char VALUES ('FE306991300004  ','004');
gaussdb=# VACUUM ANALYZE logs_char;

gaussdb=# CREATE EXTENSION pkg_bpchar_opc;
CREATE EXTENSION

/*
不推荐写法，由于t1表的log_id的varchar2类型，受安装扩展影响，隐式转换为text类型，和t2表的log_id比较，t2表的log_id类型会由char隐式转换为bpchar类型，此时log_id后面的空格会被数据库去掉，即'FE306991300002'='FE306991300002  '，所以不命中数据。
*/
/*
错误示例：
*/
gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id;
 log_id | log_message | log_id | log_message 
--------+-------------+--------+-------------
(0 rows)
gaussdb=# explain SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Hash Join  (cost=1.07..2.14 rows=3 width=42)
   Hash Cond: ((t1.log_id)::text = t2.log_id)
   ->  Seq Scan on logs_varchar2 t1  (cost=0.00..1.03 rows=3 width=21)
   ->  Hash  (cost=1.03..1.03 rows=3 width=21)
         ->  Seq Scan on logs_char t2  (cost=0.00..1.03 rows=3 width=21)
(5 rows)

gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002  ';
      log_id      | log_message |      log_id      | log_message 
------------------+-------------+------------------+-------------
 FE306991300002   | 002         | FE306991300002   | 002
 FE306991300002   | 002         | FE306991300003   | 003
 FE306991300002   | 002         | FE306991300004   | 004
(3 rows)

/*
推荐写法，避免t1表的log_id的数据类型转换成text类型，比较时空格被保留，和t2表的log_id比较无法命中数据，将t1表类型强转成没安装扩展前的bpchar类型，即'FE306991300002'='FE306991300002'，所以命中数据。
*/
/*
正确示例：
/
gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id::bpchar = t2.log_id;
      log_id      | log_message |      log_id      | log_message 
------------------+-------------+------------------+-------------
 FE306991300002   | 002         | FE306991300002   | 002
 FE306991300003   | 003         | FE306991300003   | 003
 FE306991300004   | 004         | FE306991300004   | 004
(3 rows)

/*
执行计划和没安装扩展前是一致的。
*/
gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id::bpchar = t2.log_id;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Hash Join  (cost=1.07..2.14 rows=3 width=42)
   Hash Cond: ((t1.log_id)::bpchar = t2.log_id)
   ->  Seq Scan on logs_varchar2 t1  (cost=0.00..1.03 rows=3 width=21)
   ->  Hash  (cost=1.03..1.03 rows=3 width=21)
         ->  Seq Scan on logs_char t2  (cost=0.00..1.03 rows=3 width=21)
(5 rows)

gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002  ';
      log_id      | log_message |      log_id      | log_message 
------------------+-------------+------------------+-------------
 FE306991300002   | 002         | FE306991300002   | 002
 FE306991300002   | 002         | FE306991300003   | 003
 FE306991300002   | 002         | FE306991300004   | 004
(3 rows)

/*
索引的bpchar与text隐式转换。
*/
gaussdb=# CREATE EXTENSION pkg_bpchar_opc;
CREATE EXTENSION
gaussdb=# CREATE TABLE t1_1(c1 char(5), c2 int);
CREATE TABLE
gaussdb=# ALTER TABLE t1_1 ADD CONSTRAINT t1_1_pkey PRIMARY KEY (c1) WITH (storage_type=USTORE);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "t1_1_pkey" for table "t1_1"
ALTER TABLE
gaussdb=# INSERT INTO t1_1 VALUES('abc  ', 0);
INSERT 0 1
gaussdb=# CREATE TABLE t1_2(c1 varchar(5));
CREATE TABLE
gaussdb=# INSERT INTO t1_2 VALUES('abc  ');
INSERT 0 1
gaussdb=# SELECT /*+INDEXSCAN (t1_1 t1_1_pkey)*/ c1 FROM t1_1 WHERE c2 = 0 AND c1 IN (SELECT c1 FROM t1_2);
  c1   
-------
 abc  
(1 row)


gaussdb=# EXPLAIN SELECT /*+INDEXSCAN (t1_1 t1_1_pkey)*/ c1 FROM t1_1 WHERE c2 = 0 AND c1 IN (SELECT c1 FROM t1_2);
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Nested Loop  (cost=28.91..93.76 rows=4 width=24)
   ->  HashAggregate  (cost=28.91..30.91 rows=200 width=24)
         Group By Key: (t1_2.c1)::text
         ->  Seq Scan on t1_2  (cost=0.00..25.13 rows=1513 width=24)
   ->  Index Scan using t1_1_pkey on t1_1  (cost=0.00..0.30 rows=1 width=24)
         Index Cond: (c1 = (t1_2.c1)::text)
         Filter: (c2 = 0)
(7 rows)

/*
删除表和扩展。
*/
gaussdb=# DROP TABLE t1_1;
gaussdb=# DROP TABLE t1_2;
gaussdb=# DROP TABLE logs_varchar2;
gaussdb=# DROP TABLE logs_char;
gaussdb=# DROP EXTENSION pkg_bpchar_opc;
  • 为了解决bpchar类型（包含多个后补空格）与text类型做等值匹配操作的时候无法正常匹配数据以及索引问题。
  • 涉及ubtree, btree，比较符号包含： >, >=, <, <=, <>,=。
  • 影响面涉及字符类型之间的隐式转换，例如：变长和定长数据类型比较时，变长会优先转换为text类型，而不是最初的bpchar类型。
  • 默认不开启pkg_bpchar_opc扩展。检验扩展有没有开启，可以查看系统表pg_extension，有该扩展数据是开启了，没有则是没有开启。关闭扩展时，保持了前向兼容，开启扩展时，保持了与A数据库兼容。扩展功能为内部使用功能，不建议用户使用。
  • 示例中所用的表结构都是以log_id为索引，共有log_id, log_message两个字段，表名下划线后面接的是log_id字段类型（例如：表名为logs_text,则log_id字段类型为text）。
表1 pkg_bpchar_opc支持的函数说明

接口名称

描述

pg_catalog.bpchar_text_lt

bpchar类型和text类型比较，左边数值是否小于右边的数值。

pg_catalog.bpchar_text_le

bpchar类型和text类型比较，左边数值是否小于等于右边的数值。

pg_catalog.bpchar_text_eq

bpchar类型和text类型比较，左边数值是否等于右边的数值。

pg_catalog.bpchar_text_ge

bpchar类型和text类型比较，左边数值是否大于等于右边的数值。

pg_catalog.bpchar_text_gt

bpchar类型和text类型比较，左边数值是否大于右边的数值。

pg_catalog.bpchar_text_ne

bpchar类型和text类型比较，左边数值是否不等于右边的数值。

pg_catalog.bpchar_text_cmp

bpchar类型和text类型的索引支持比较函数。

pg_catalog.text_bpchar_lt

text类型和bpchar类型比较，左边数值是否小于右边的数值。

pg_catalog.text_bpchar_le

text类型和bpchar类型比较，左边数值是否小于等于右边的数值。

pg_catalog.text_bpchar_eq

text类型和bpchar类型比较，左边数值是否等于右边的数值。

pg_catalog.text_bpchar_ge

text类型和bpchar类型比较，左边数值是否大于等于右边的数值。

pg_catalog.text_bpchar_gt

text类型和bpchar类型比较，左边数值是否大于右边的数值。

pg_catalog.text_bpchar_ne

text类型和bpchar类型比较，左边数值是否不等于右边的数值。

pg_catalog.text_bpchar_cmp

text类型和bpchar类型的索引支持比较函数。

pg_catalog.hashbpchartext

bpchar类型和text类型的hash支持比较函数。

pg_catalog.hashtextbpchar

text类型和bpchar类型的hash支持比较函数。

bpcharlikebpchar(BPCHAR, BPCHAR)

描述：判断第一个入参BPCHAR字符串是否LIKE第二个入参BPCHAR字符串。用于新增BPCHAR类型和BPCHAR类型的LIKE操作符，解决BPCHAR类型和BPCHAR类型数据比较无法返回正确结果集问题。启用“~~”操作符需在GUC参数behavior_compat_options的取值中包含enable_bpcharlikebpchar_compare配置项。

返回值类型：BOOL。

取值范围：

  • t：表示两个BPCHAR类型参数匹配。
  • f：表示两个BPCHAR类型参数不匹配。
示例： 
 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
 
gaussdb=#  SELECT bpcharlikebpchar('455'::BPCHAR(10), '455  '::BPCHAR);
 bpcharlikebpchar 
------------------
 f
(1 row)
gaussdb=#  SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455 '::BPCHAR(10));
 bpcharlikebpchar 
------------------
 t
(1 row)
gaussdb=#  SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(10));
 bpcharlikebpchar 
------------------
 t
(1 row)
gaussdb=#  SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(11));
 bpcharlikebpchar 
------------------
 f
(1 row)
gaussdb=#  CREATE TABLE op_test (
   col BPCHAR(2) DEFAULT NULL
 );
CREATE TABLE
gaussdb=#  CREATE INDEX op_index ON op_test(col);
CREATE INDEX
gaussdb=#  INSERT INTO op_test VALUES ('a');
INSERT 0 1
gaussdb=#  INSERT INTO op_test VALUES ('1');
INSERT 0 1
gaussdb=#  INSERT INTO op_test VALUES ('11');
INSERT 0 1
gaussdb=#  INSERT INTO op_test VALUES ('12');
INSERT 0 1
gaussdb=#  INSERT INTO op_test VALUES ('sd');
INSERT 0 1
gaussdb=#  INSERT INTO op_test VALUES ('aa');
INSERT 0 1
gaussdb=#  SHOW behavior_compat_options;
 behavior_compat_options 
-------------------------
(1 row)
--当behavior_compat_options不包含enable_bpcharlikebpchar_compare时，则未启用最新bpcharlikebpchar操作符，其bpchar匹配bpchar返回结果集非预期（正常应返回全部数据）。
gaussdb=#  EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
           QUERY PLAN           
--------------------------------
 Seq Scan on op_test
   Filter: (col ~~ (col)::text)
(2 rows)
gaussdb=#  SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
 col 
-----
 11
 12
 aa
 sd
(4 rows)
gaussdb=#  SET behavior_compat_options = 'enable_bpcharlikebpchar_compare';
SET
gaussdb=#  SHOW behavior_compat_options;
     behavior_compat_options     
---------------------------------
 enable_bpcharlikebpchar_compare
(1 row)
--开启参数后，将启用最新bpcharlikebpchar操作符，其匹配时返回行为符合预期行为。
gaussdb=#  EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
           QUERY PLAN           
--------------------------------
 Seq Scan on op_test
   Filter: (col ~~ col)
(2 rows)
gaussdb=#  SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
 col 
-----
 1 
 11
 12
 a 
 aa
 sd
(6 rows)
gaussdb=#  DROP TABLE op_test;
DROP TABLE
  • 仅在数据库兼容性参数SQL_COMPATIBILITY为A时，设置GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段可以使用bpcharlikebpchar。
  • 启用该特性，会影响BPCHAR类型进行LIKE模式匹配时的结果集与执行计划。
  • SET behavior_compat_options=''; 表示关闭该特性，SET behavior_compat_options='enable_bpcharlikebpchar_compare';表示开启该特性。
  • 开启新特性后，定长匹配定长（bpchar匹配bpchar），需要指定左右两侧参数长度相同；模式匹配时，注意模式列长度与强制转换长度相同，避免过长后填补空格导致结果与预期存在差异。

bpcharnlikebpchar(BPCHAR, BPCHAR)

描述：判断第一个入参BPCHAR字符串是否NOT LIKE第二个入参BPCHAR字符串。用于新增BPCHAR类型和BPCHAR类型的NOT LIKE操作符。启用“!~~”操作符需在GUC参数behavior_compat_options的取值中包含enable_bpcharlikebpchar_compare配置项。

返回值类型：BOOL。

取值范围：

  • t：表示两个BPCHAR类型参数匹配。
  • f：表示两个BPCHAR类型参数不匹配。

示例：

 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
 
gaussdb=#  SELECT bpcharnlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(11));
 bpcharnlikebpchar 
-------------------
 t
(1 row)

gaussdb=#  SELECT bpcharnlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(10));
 bpcharnlikebpchar 
-------------------
 f
(1 row)

gaussdb=#  SELECT bpcharnlikebpchar('455 '::BPCHAR(10), '455 '::BPCHAR);
 bpcharnlikebpchar 
-------------------
 t
(1 row)
gaussdb=#  CREATE TABLE op_test (
   col BPCHAR(2) DEFAULT NULL
 );
CREATE TABLE
gaussdb=#  CREATE INDEX op_index ON op_test(col);
CREATE INDEX
gaussdb=#  INSERT INTO op_test VALUES ('a');
INSERT 0 1
gaussdb=#  INSERT INTO op_test VALUES ('1');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('11');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('12');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('sd');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('aa');
INSERT 0 1
gaussdb=# SHOW behavior_compat_options;
 behavior_compat_options 
-------------------------
(1 row)
--当behavior_compat_options不包含enable_bpcharlikebpchar_compare时，则未启用最新bpcharnlikebpchar操作符，其BPCHAR匹配BPCHAR返回结果集非预期（正常应返回0条数据）。
gaussdb=# SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col;
 col 
-----
 1 
 a 
(2 rows)

gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col;
                QUERY PLAN                 
-------------------------------------------
 Index Only Scan using op_index on op_test
   Filter: (col !~~ (col)::text)
(2 rows)
gaussdb=# SET behavior_compat_options = 'enable_bpcharlikebpchar_compare';
SET
gaussdb=# SHOW behavior_compat_options;
     behavior_compat_options     
---------------------------------
 enable_bpcharlikebpchar_compare
(1 row)
--开启参数后，将启用最新bpcharnlikebpchar操作符，其匹配时返回行为符合预期行为。

gaussdb=# SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col;
 col 
-----
(0 rows)

gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col;
                QUERY PLAN                 
-------------------------------------------
 Index Only Scan using op_index on op_test
   Filter: (col !~~ col)
(2 rows)
gaussdb=# DROP TABLE op_test;
DROP TABLE
  • 仅在数据库兼容性参数SQL_COMPATIBILITY为A时，设置GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段可以使用bpcharlikebpchar。
  • 启用该特性，会影响BPCHAR类型进行NOT LIKE模式匹配时的结果集与执行计划。
  • SET behavior_compat_options=''; 表示关闭该特性，SET behavior_compat_options='enable_bpcharlikebpchar_compare';表示开启该特性。
  • 开启新特性后，定长匹配定长（bpchar匹配bpchar），需要指定左右两侧参数长度相同；模式匹配时，注意模式列长度与强制转换长度相同，避免过长后填补空格导致结果与预期存在差异。