更新时间：2025-05-29 GMT+08:00
扩展函数和操作符
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 32 33 34 35 36 37 38 39 40 41 42 43 |
/* 建表和数据初始化。 */ 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; /* 目的就是获得所有节点的详细执行计划。 */ gaussdb=# SET max_datanode_for_plan = 1; SET /* 没有安装扩展时候，nchar和text比较时候，由于没有bpchar和text索引操作符，nchar会隐式转换为text,即定长字符类型转换成变长字符类型，导致执行计划发生了变化，没有命中索引。 */ gaussdb=# EXPLAIN SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' '); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_nchar WHERE log_id::text = rpad(btrim('FE306991300002 '::text), 16, ' '::text) Datanode Name: datanode1 Seq Scan on logs_nchar (cost=0.00..2539.01 rows=500 width=50) Filter: ((log_id)::text = 'FE306991300002 '::text) (8 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扩展，与ORA保持一致）:
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_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 gaussdb=# SET max_datanode_for_plan = 1; SET gaussdb=# EXPLAIN SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' '); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_nchar WHERE log_id = rpad(btrim('FE306991300002 '::text), 16, ' '::text) Datanode Name: datanode1 [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) (9 rows) /* 此时，log_id隐式转换为bpchar类型时，和text类型比较时，能找到比较操作符以及索引信息，能命中索引。 */ 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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
/* 建表和数据初始化。 */ 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=# SET max_datanode_for_plan = 1; SET gaussdb=# EXPLAIN SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_text WHERE log_id = 'FE306991300002 '::bpchar::text Datanode Name: datanode1 [Bypass] Index Scan using idx_text_logid on logs_text (cost=0.00..8.27 rows=1 width=38) Index Cond: (log_id = 'FE306991300002'::text) (9 rows) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar; log_id | log_message --------+------------- (0 rows) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::text; log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) /* 删除表。 */ gaussdb=# DROP TABLE logs_text; |
text类型和bpchar类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）:
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_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=# SET max_datanode_for_plan = 1; SET gaussdb=# explain select * from logs_text t1 where t1.log_id ='FE306991300002 '::bpchar; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_text t1 WHERE log_id = 'FE306991300002 '::bpchar Datanode Name: datanode1 [Bypass] Index Scan using idx_text_logid on logs_text t1 (cost=0.00..8.27 rows=1 width=38) Index Cond: (log_id = 'FE306991300002 '::bpchar) (9 rows) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar; log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::text; log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) /* 删除表和扩展。 */ 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 47 48 49 50 51 52 53 54 |
/* 建表和数据初始化。 */ 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=# SET max_datanode_for_plan = 1; SET 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 --------------------------------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT t1.log_id, t1.log_message, t2.log_id, t2.log_message FROM public.logs_varchar2 t1, public.logs_char t2 WHERE t1.log_id::bpchar = t2.log_id Datanode Name: datanode1 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) (11 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扩展，与ORA保持一致）:
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 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
/* 建表和数据初始化。 */ 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 gaussdb=# SET max_datanode_for_plan = 1; SET /* 不推荐写法，由于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 ------------------------------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT t1.log_id, t1.log_message, t2.log_id, t2.log_message FROM public.logs_varchar2 t1, public.logs_char t2 WHERE t1.log_id::text = t2.log_id Datanode Name: datanode1 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) (11 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 --------------------------------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT t1.log_id, t1.log_message, t2.log_id, t2.log_message FROM public.logs_varchar2 t1, public.logs_char t2 WHERE t1.log_id::bpchar = t2.log_id Datanode Name: datanode1 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) (11 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 DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA'; CREATE DATABASE gaussdb=# \c ora_compatible_db; Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "ora_compatible_db" as user "gaozheng". ora_compatible_db=# CREATE EXTENSION pkg_bpchar_opc; CREATE EXTENSION ora_compatible_db=# CREATE TABLE t1_1(c1 char(5), c2 int); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE ora_compatible_db=# 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 ora_compatible_db=# INSERT INTO t1_1 VALUES('abc ', 0); INSERT 0 1 ora_compatible_db=# CREATE TABLE t1_2(c1 varchar(5)); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE ora_compatible_db=# INSERT INTO t1_2 VALUES('abc '); INSERT 0 1 ora_compatible_db=# CREATE /*+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) ora_compatible_db=# SET max_datanode_for_plan = 64; SET ora_compatible_db=# EXPLAIN CREATE /*+INDEXSCAN (t1_1 t1_1_pkey)*/ c1 FROM t1_1 WHERE c2 = 0 AND c1 IN (SELECT c1 FROM t1_2); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT/*+ IndexScan(t1_1 t1_1_pkey)*/ c1 FROM public.t1_1 WHERE c2 = 0 AND (c1 IN (SELECT t1_2.c1 FROM public.t1_2)) Datanode Name: dist1_datanode1 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) (13 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，有该扩展数据是开启了，没有则是没有开启。关闭扩展时，保持了前向兼容，开启扩展时，保持了与ORA数据库兼容等。开启pkg_bpchar_opc扩展后，需要设置max_datanode_for_plan参数（该参数可以控制生成FQS计划时设置显示DN上执行计划的个数），根据自身需求去设置。以设置max_datanode_for_plan参数为64为例，命令如下：
set max_datanode_for_plan = 64。
扩展功能为内部使用功能，不建议用户使用。
- 示例中所用的表结构都是以log_id为索引，共有log_id，log_message两个字段，表名下划线后面接的是log_id字段类型（例如：表名为logs_text,则log_id字段类型为text）。
|
接口名称 |
描述 |
|---|---|
|
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操作符。启用“~~”操作符需在GUC参数behavior_compat_options的取值中包含enable_bpcharlikebpchar_compare配置项。
返回值类型：boolean。
取值范围：
- 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 84 85 86 87 88 89 90 91 92 93 |
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 --------------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col !~~ (col)::text) (7 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 ------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col ~~ col) (7 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为ORA时，设置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配置项。
返回值类型：boolean。
取值范围：
- 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 84 85 |
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 --------------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col !~~ (col)::text) (7 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 ------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col !~~ col) (7 rows) gaussdb=# DROP TABLE op_test; DROP TABLE |
- 仅在数据库兼容性参数SQL_COMPATIBILITY为ORA时，设置GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段可以使用bpcharlikebpchar。
- 启用该特性，会影响BPCHAR类型进行NOT LIKE模式匹配时的结果集与执行计划。
- SET behavior_compat_options=''; 表示关闭该特性，SET behavior_compat_options='enable_bpcharlikebpchar_compare';表示开启该特性。
- 开启新特性后，定长匹配定长（bpchar匹配bpchar），需要指定左右两侧参数长度相同；模式匹配时，注意模式列长度与强制转换长度相同，避免过长后填补空格导致结果与预期存在差异。
父主题： 字符处理函数和操作符
