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
Name
Description
Storage Space
CHAR(n)
CHARACTER(n)
NCHAR(n)
Character string with fixed length. Empty characters are filled in with blank spaces. n indicates the string length. If it is not specified, the default precision 1 is used.
The maximum size is 10 MB.
TEXT(n),
TINYTEXT(n),
MEDIUMTEXT(n),
LONGTEXT(n)
Character string with variable length. n has no actual meaning and does not affect any performance.
The maximum size is 1 GB - 1 byte. However, the size of the column description header and the size of the tuple (less than 1 GB - 1 byte) where the column is located must also be considered. Therefore, the maximum size of the TEXT type may be less than 1 GB - 1 byte.
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;
Top comments (0)