Character Types

Table 1 lists the character data types supported by GaussDB. For string operators and related built-in functions, see Character Processing Functions and Operators.

**Table 1** Character types
Name	Description	Storage Space
CHAR(n) CHARACTER(n) NCHAR(n)	Fixed-length character string, blank padded. n indicates the string length. If it is not specified, the default precision 1 is used.	The maximum value of n is 10485760 (10 MB).
VARCHAR(n) CHARACTER VARYING(n)	Variable-length string. In PostgreSQL-compatible mode, n indicates the string length. In other compatibility modes, n indicates the byte length.	The maximum value of n is 10485760 (10 MB). If n is not specified, the maximum storage length is 1 GB – 85 – 4 (space for storing length parameter) – Length of other columns. For example, in a table with columns (a int, b varchar, c int), the maximum length for the varchar column is 1 GB – 85 – 4 (space for storing length parameter) – 4 (length of column a int) – 4 (length of column c int) = 1,073,741,727‬. For details, see Example of the maximum storage length of the variable-length type.
VARCHAR2(n)	Variable-length string. It is the alias of the VARCHAR(n) type. n indicates the string length.	The maximum value of n is 10485760 (10 MB). If n is not specified, the maximum storage length is 1 GB – 85 – 4 (indicating space required for storing length of the data type) – Length of other columns. For example, in a table with columns (a int, b varchar2, c int), the maximum length for the varchar2 column is 1,073,741,727 = 1 GB – 85 – 4 (space required for storing length of the data type) – 4 (length of a int) – 4 (length of c int). For details, see Example of the maximum storage length of the variable-length type.
NVARCHAR2(n)	Variable-length string. In the SQL_ASCII character set, n indicates bytes. In the non-SQL_ASCII character set, n indicates characters.	The maximum value of n is 10485760 (10 MB). If n is not specified, the maximum storage length is 1 GB – 85 – 4 (indicating space required for storing length of the data type) – Length of other columns. For example, in a table with columns (a int, b nvarchar2, c int), the maximum length for the nvarchar2 column is 1,073,741,727 = 1 GB – 85 – 4 (space required for storing length of the data type) – 4 (length of a int) – 4 (length of c int). For details, see Example of the maximum storage length of the variable-length type.
CLOB	Big text object.	For Astore, the maximum size is 32 TB minus 1 byte. However, the size of the column description header and the size of the tuple (less than 32 TB minus 1 byte) where the column is located must also be considered. Therefore, the maximum size of the CLOB type may be less than 32 TB minus 1 byte. For Ustore, the maximum size is 1 GB minus 1 byte. However, the size of the column description header and the size of the tuple (less than 1 GB minus 1 byte) where the column is located must also be considered. Therefore, the maximum size of the CLOB type may be less than 1 GB minus 1 byte.
TEXT	Variable-length string.	The maximum storage length is 1 GB – 85 – 4 (indicating space required for storing length of the data type) – Length of other columns. For example, in a table with columns (a int, b text, c int), the maximum length for the text column is 1,073,741,727 = 1 GB – 85 – 4 (space required for storing length of the data type) – 4 (length of a int) – 4 (length of c int). For details, see Example of the maximum storage length of the variable-length type.

In addition to the restriction on the size of each column, the total size of each tuple cannot exceed 1 GB minus 85 bytes and is affected by the control header information of the column, the control header information of the tuple, and whether null fields exist in the tuple.
NCHAR is the alias of the bpchar type, and VARCHAR2(n) is the alias of the VARCHAR(n) type.
Only advanced package dbe_lob supports CLOBs whose size is greater than 1 GB. System functions do not support CLOBs whose size is greater than 1 GB.
In ORA-compatible mode, the received empty string is converted to null by default.
GaussDB supports a maximum of 1 GB data transfer, and the maximum size of the result string returned by the function is 1 GB.
For details about the character sets supported by GaussDB, see "ENCODING" in 7.15.61 CREATE DATABASE.

GaussDB has two other fixed-length character types, as shown in Table 2. The name type exists only for the storage of identifiers in the internal system catalogs and is not intended for use by general users. Its length is currently defined as 64 bytes (63 usable characters plus terminator). The type "char" only uses one byte of storage. It is internally used in the system catalogs as a simplistic enumeration type.

**Table 2** Special character types
Name	Description	Storage Space
name	Internal type for object names	64 bytes
"char"	Single-byte internal type	1 byte

Examples

Example of data insertion where the length exceeds the specified length of the type

-- Create a table.
gaussdb=# CREATE TABLE char_type_t1 
(
    CT_COL1 CHARACTER(4)
)DISTRIBUTE BY HASH (CT_COL1);

-- Insert data.
gaussdb=# INSERT INTO char_type_t1 VALUES ('ok');

-- Query data in the table.
gaussdb=# SELECT ct_col1, char_length(ct_col1) FROM char_type_t1;
 ct_col1 | char_length 
---------+-------------
 ok      |           4
(1 row)

-- Delete the table.
gaussdb=# DROP TABLE char_type_t1;

-- Create a table.
gaussdb=# CREATE TABLE char_type_t2  
(
    CT_COL1 VARCHAR(5)
)DISTRIBUTE BY HASH (CT_COL1);

-- Insert data.
gaussdb=# INSERT INTO char_type_t2 VALUES ('ok');

gaussdb=# INSERT INTO char_type_t2 VALUES ('good');

-- Specify the type length. An error is reported if an inserted string exceeds this length.
gaussdb=# INSERT INTO char_type_t2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
CONTEXT:  referenced column: ct_col1

-- Specify the type length. A string exceeding this length is truncated.
gaussdb=# INSERT INTO char_type_t2 VALUES ('too long'::varchar(5));

-- Query data.
gaussdb=# SELECT ct_col1, char_length(ct_col1) FROM char_type_t2;
 ct_col1 | char_length 
---------+-------------
 ok      |           2
 good    |           4
 too l   |           5
(3 rows)

-- Delete data.
gaussdb=# DROP TABLE char_type_t2;

Example of the maximum storage length of the variable-length type

The following uses varchar as an example, but the same applies to varchar2, nvarchar, nvarchar2, and text.

-- Create a table with three columns: int, varchar, and int. According to the calculation rule, the maximum storage length of varchar is 1,073,741,727 = 1 GB – 85 – 4 – 4 – 4.
gaussdb=# CREATE TABLE varchar_maxlength_test1 (a int, b varchar, c int) DISTRIBUTE BY HASH (a);

-- The length of varchar is 1,073,741,728, which exceeds the specified length. As a result, the insertion fails.
gaussdb=# insert into varchar_maxlength_test1 values(1, repeat('a', 1073741728), 1);
ERROR: invalid memory alloc request size 1073741824 in tuplesort.cpp:219

-- The length of varchar is 1,073,741,727, which meets the requirement. As a result, the insertion is successful.
gaussdb=# insert into varchar_maxlength_test1 values(1, repeat('a', 1073741727), 1);

-- Create a table with only the varchar column. According to the calculation rule, the maximum storage length of the varchar column is 1,073,741,735 = 1 GB – 85 – 4.
gaussdb=# CREATE TABLE varchar_maxlength_test2 (a varchar) DISTRIBUTE BY HASH (a);

-- The length of varchar is 1,073,741,736, which exceeds the specified length. As a result, the insertion fails.
insert into varchar_maxlength_test2 values(repeat('a', 1073741736));
ERROR:  invalid memory alloc request size 1073741824 in tuplesort.cpp:219

-- The length of varchar is 1,073,741,735, which meets the requirement. As a result, the insertion is successful.
insert into varchar_maxlength_test2 values(repeat('a', 1073741735));