Extension Functions and Operators
pkg_bpchar_opc()
Description: Serves as an extension API to add the comparison operator between bpchar and text or between text and bpchar policies, so as to solve the problem that indexes cannot be matched when data of the bpchar and text types is compared. Only a system administrator can install extensions.
Example:
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 a table and initialize data. */ 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; /* If no extension is installed, when nchar and text are compared, nchar is implicitly converted to text because there is no bpchar or text index operator. That is, the fixed-length character type is converted to the variable-length character type. As a result, the execution plan changes and the index cannot be matched. */ 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) /* The log_id column in the logs_nchar table is of nchar(16) type. The inserted data is 'FE306991300002 ', which is implicitly converted to text. During comparison, spaces are deleted, that is, 'FE306991300002'='FE306991300002 '. Therefore, the data is not matched. */ gaussdb=# SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' '); log_id | log_message --------+------------- (0 rows) /* Delete the table. */ gaussdb=# DROP TABLE logs_nchar; |
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 |
/* Create a table and initialize data. */ 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; /* A system administrator installs the pkg_bpchar_opc extension. The comparison operators of the bpchar and text types and index-related content are added to the database. */ gaussdb=# CREATE EXTENSION pkg_bpchar_opc; CREATE EXTENSION /* In this case, when log_id is implicitly converted to the bpchar type and compared with the text type, the comparison operator and index information can be found, and the index can be matched. */ 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) /* Delete the table and extension. */ gaussdb=# DROP TABLE logs_nchar; gaussdb=# DROP EXTENSION pkg_bpchar_opc; |
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 |
/* Create a table and initialize data. */ 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) /* Delete the table. */ gaussdb=# DROP TABLE logs_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 |
/* Create a table and initialize data. */ 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) /* Delete the table and extension. */ gaussdb=# DROP TABLE logs_text; gaussdb=# DROP EXTENSION pkg_bpchar_opc; |
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 |
/* Create a table and initialize data. */ 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) /* Delete the table. */ gaussdb=# DROP TABLE logs_varchar2; gaussdb=# DROP TABLE logs_char; |
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 |
/* Create a table and initialize data. */ 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 /* This format is not recommended. After the extension is installed, the varchar2 type of log_id in the t1 table is implicitly converted to the text type. When it is compared with the log_id in the t2 table, the char type of log_id in the t2 table is implicitly converted to the bpchar type. In this case, spaces after log_id is removed by the database, that is, 'FE306991300002'='FE306991300002 '. Therefore, no data is matched. */ /* Incorrect example: */ 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) /* This format is recommended to avoid the following problems: the data type of log_id in the t1 table is converted to the text type, spaces are reserved during comparison, and data cannot be matched when the data type of log_id in the t2 table is compared with the data type of log_id in the t1 table. The type of t1 table is forcibly converted to the bpchar type before the extension is installed, that is, 'FE306991300002' = 'FE306991300002'. Therefore, data is matched. */ /* Correct example: / 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) /* The execution plan is the same as that before the extension is installed. */ 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) /* Implicit conversion between bpchar and text of indexes. */ 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) /* Delete the table and extension. */ 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; |

- This solves the problem that data and indexes cannot be properly matched when equality matching is performed between the bpchar type (containing multiple spaces) and the text type.
- The UB-tree and B-tree are involved. The comparison symbols include >, >=, <, <=, <>, and =.
- The impact scope involves implicit conversion between character types. For example, when a variable-length data type is compared with a fixed-length data type, the variable-length data type is preferentially converted to the text type instead of the original bpchar type.
- The pkg_bpchar_opc extension is disabled by default. You can check whether the extension is enabled in the pg_extension system catalog. If the extension data exists, the extension is enabled. If the extension data does not exist, the extension is disabled. When extension is disabled, forward compatibility is maintained. When extension is enabled, compatibility with database A is maintained. The extended function is for internal use only. You are advised not to use it.
- In the example, the table structure uses log_id as the index and has two columns: log_id and log_message. The table name is followed by the log_id column type. For example, if the table name is logs_text, the log_id column type is text.
API |
Description |
---|---|
pg_catalog.bpchar_text_lt |
Compares the bpchar type with the text type to check whether the value on the left is less than the value on the right. |
pg_catalog.bpchar_text_le |
Compares the bpchar type with the text type to check whether the value on the left is less than or equal to the value on the right. |
pg_catalog.bpchar_text_eq |
Compares the bpchar type with the text type to check whether the value on the left is equal to the value on the right. |
pg_catalog.bpchar_text_ge |
Compares the bpchar type with the text type to check whether the value on the left is greater than or equal to the value on the right. |
pg_catalog.bpchar_text_gt |
Compares the bpchar type with the text type to check whether the value on the left is greater than the value on the right. |
pg_catalog.bpchar_text_ne |
Compares the bpchar type with the text type to check whether the value on the left is different from the value on the right. |
pg_catalog.bpchar_text_cmp |
Specifies that the index of the bpchar and text types supports comparison functions. |
pg_catalog.text_bpchar_lt |
Compares the text type with the bpchar type to check whether the value on the left is less than the value on the right. |
pg_catalog.text_bpchar_le |
Compares the text type with the bpchar type to check whether the value on the left is less than or equal to the value on the right. |
pg_catalog.text_bpchar_eq |
Compares the text type with the bpchar type to check whether the value on the left is equal to the value on the right. |
pg_catalog.text_bpchar_ge |
Compares the text type with the bpchar type to check whether the value on the left is greater than or equal to the value on the right. |
pg_catalog.text_bpchar_gt |
Compares the text type with the bpchar type to check whether the value on the left is greater than the value on the right. |
pg_catalog.text_bpchar_ne |
Compares the text type with the bpchar type to check whether the value on the left is different from the value on the right. |
pg_catalog.text_bpchar_cmp |
Specifies that the index of the text and bpchar types supports comparison functions. |
pg_catalog.hashbpchartext |
Specifies that the hash of the bpchar and text types supports comparison functions. |
pg_catalog.hashtextbpchar |
Specifies that the hash of the text and bpchar types supports comparison functions. |
bpcharlikebpchar(BPCHAR, BPCHAR)
Description: Determines whether the BPCHAR character string of the first input parameter is LIKE and whether the BPCHAR character string of the second input parameter is LIKE. The LIKE operator of the BPCHAR and BPCHAR types is added to solve the problem that the correct result set cannot be returned when data of the BPCHAR and BPCHAR types is compared. To enable the ~~ operator, ensure that the value of the GUC parameter behavior_compat_options contains the enable_bpcharlikebpchar_compare configuration item.
Return value type: Boolean
Value range:
- t: Two parameters of the BPCHAR type are matched.
- f: Two parameters of the BPCHAR type are not matched.
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) -- If behavior_compat_options does not contain enable_bpcharlikebpchar_compare, the latest bpcharlikebpchar operator is not enabled and the result set returned by the matching between bpchars is not the same as expected (all data should be returned in normal cases). 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) -- After this parameter is enabled, the latest bpcharlikebpchar operator is enabled, and the returned behavior meets the expected behavior during matching. 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 |

- bpcharlikebpchar can be used only when the database compatibility parameter SQL_COMPATIBILITY is set to A and the GUC parameter behavior_compat_options contains the enable_bpcharlikebpchar_compare column.
- If this feature is enabled, the result set and execution plan for the LIKE pattern matching of the BPCHAR types are affected.
- SET behavior_compat_options=''; indicates that this feature is disabled, and SET behavior_compat_options='enable_bpcharlikebpchar_compare' indicates that this feature is enabled.
- After the new feature is enabled, fixed-length matching is used (bpchar matches bpchar). The parameter lengths on the left and right sides must be the same. During pattern matching, ensure that the length of the pattern column is the same as the forcible conversion length to avoid the difference between the result and the expected result caused by filling spaces after the length is too long.
bpcharnlikebpchar(BPCHAR, BPCHAR)
Description: Determines whether the BPCHAR character string of the first input parameter is NOT LIKE and whether the BPCHAR character string of the second input parameter is NOT LIKE. It is used to add the BPCHAR type and the NOT LIKE operator of the BPCHAR type. To enable the !~~ operator, ensure that the value of the GUC parameter behavior_compat_options contains the enable_bpcharlikebpchar_compare configuration item.
Return value type: Boolean
Value range:
- t: Two parameters of the BPCHAR type are matched.
- f: Two parameters of the BPCHAR type are not matched.
Example:
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) -- If behavior_compat_options does not contain enable_bpcharlikebpchar_compare, the latest bpcharnlikebpchar operator is not enabled and the result set returned by the matching between bpchars is not the same as expected (no data record is returned in normal cases). 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) -- After this parameter is enabled, the latest bpcharnlikebpchar operator is enabled, and the returned behavior meets the expected behavior during matching. 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 |

- bpcharlikebpchar can be used only when the database compatibility parameter SQL_COMPATIBILITY is set to A and the GUC parameter behavior_compat_options contains the enable_bpcharlikebpchar_compare column.
- If this feature is enabled, the result set and execution plan for the NOT LIKE pattern matching of the BPCHAR types are affected.
- SET behavior_compat_options=''; indicates that this feature is disabled, and SET behavior_compat_options='enable_bpcharlikebpchar_compare' indicates that this feature is enabled.
- After the new feature is enabled, fixed-length matching is used (bpchar matches bpchar). The parameter lengths on the left and right sides must be the same. During pattern matching, ensure that the length of the pattern column is the same as the forcible conversion length to avoid the difference between the result and the expected result caused by filling spaces after the length is too long.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot