Character Types
Compared with the original openGauss, Dolphin modifies the character types as follows:
- The meaning of n of the CHARACTER/NCHAR type is modified. n indicates the character length instead of the byte length.
- During comparison of all character data types, spaces at the end are ignored, for example, in the WHERE and JOIN scenarios. For example, 'a'::text = 'a'::text is true. For the VARCHAR, VARCHAR2, NVARCHAR2, NVARCHAR, TEXT, and CLOB types, HASH JOIN and HASH AGG ignore spaces at the end only when string_hash_compatible is set to on.
- The optional modifier (n) is added for TEXT. That is, the usage of TEXT(n) is supported. n is meaningless and does not affect any performance.
- The TINYTEXT(n)/MEDIUMTEXT(n)/LONGTEXT(n) data type is added, which is the alias of TEXT. n is meaningless and does not affect any performance.
Table 1 Character types
Example:
--Create a table.
openGauss=# CREATE TABLE char_type_t1
(
CT_COL1 CHARACTER(4),
CT_COL2 TEXT(10),
CT_COL3 TINYTEXT(11),
CT_COL4 MEDIUMTEXT(12),
CT_COL5 LONGTEXT(13)
);
--View a table structure.
openGauss=# \d char_type_t1
Table "public.char_type_t1"
Column | Type | Modifiers
---------+--------------+-----------
ct_col1 | character(4) |
ct_col2 | text |
ct_col3 | text |
ct_col4 | text |
ct_col5 | text |
--Insert data.
openGauss=# INSERT INTO char_type_t1 VALUES ('Four characters');
openGauss=# INSERT INTO char_type_t1 VALUES('e ');
--View data.
openGauss=# SELECT CT_COL1,length(CT_COL1) FROM char_type_t1;
ct_col1 | length
----------+--------
Four characters | 4
e | 1
(2 rows)
--Filter data.
openGauss=# SELECT CT_COL1 FROM char_type_t1 WHERE CT_COL1 = 'e';
ct_col1
---------
e
(1 row)
openGauss=# SELECT CT_COL1 FROM char_type_t1 WHERE CT_COL1 = 'e ';
ct_col1
---------
e
(1 row)
--Delete the table.
openGauss=# DROP TABLE char_type_t1;
Feedback