Help Center> GaussDB> Distributed_8.x> SQL Reference> Data Types> Character Types

Updated on 2024-06-03 GMT+08:00

View PDF

Character Types

Table 1 lists the character 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).
CHAR(n) CHARACTER(n) NCHAR(n)	When sql_compatibility is set to 'MYSQL', if b_format_version is set to '5.7' and b_format_dev_version is set to 's1': Type: n indicates the character length. The value range is [0,10485760]. If the precision n is not specified, the default precision 1 is used. Fixed-length character string, blank padded. Input Data type of table columns and temporary variables: If the number of characters in an input string is within the range, the string can be entered normally. If the value of sql_mode contains strict_trans_tables, an error is reported. Otherwise, the string is truncated based on the maximum string length specified by n and an alarm is generated. Output Data type of table columns and temporary variables: If the value of sql_mode contains pad_char_to_full_length, the character string containing spaces at the end is output. Otherwise, the character string without spaces at the end is output. Function parameter and return value or stored procedure parameter: The length cannot be verified. For example, if the input parameter of a user-defined function is of the CHAR(5) type and a string '123456' is input, the parameter can be directly transferred without length verification.	The value contains a maximum of 10M characters.
VARCHAR(n) CHARACTER VARYING(n)	Variable-length string. In PG-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 – Length of the first n columns. For example, the maximum length of (a int, b varchar) is 1,073,741,735 (= 1 GB – 85 – 4).
VARCHAR(n) CHARACTER VARYING(n)	When sql_compatibility is set to 'MYSQL', if b_format_version is set to '5.7' and b_format_dev_version is set to 's1': n indicates the string length. The value range is [0,10485760]. If the precision n is not specified, there is no length limit by default. The length is the same as that of the TEXT type. Input Data type of table columns and temporary variables: If the number of characters in an input string is within the range, the string can be entered normally. If the value of sql_mode contains strict_trans_tables, an error is reported. Otherwise, the string is truncated based on the maximum string length specified by n and an alarm is generated. Output: The original character string is output.	If n is specified, the maximum storage length is 10 MB. If n is not specified, the maximum storage length is 1 GB – 85 – Length of the first n columns. For example, the maximum storage length of (a int, b varchar) is 1,073,741,735 (= 1 GB – 85 – 4)‬.
VARCHAR2(n)	Variable-length string. It is the alias of the VARCHAR(n) type.	The maximum value of n is 10485760 (10 MB). If n is not specified, the maximum storage length is 1 GB – 85 – Length of the first n columns. For example, the maximum length of (a int, b varchar) is 1,073,741,735 (= 1 GB – 85 – 4).
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 – Length of the first n columns. For example, the maximum length of (a int, b varchar) is 1,073,741,735 (= 1 GB – 85 – 4).
TEXT	Variable-length string.	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 TEXT type may be less than 1 GB minus 1 byte.
CLOB	Big text object.	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.
TINYTEXT MEDIUMTEXT LONGTEXT	When sql_compatibility is set to 'MYSQL', if b_format_version is set to '5.7' and b_format_dev_version is set to 's1', these types can be used. Converts a data type to the TEXT type. The application scenario is the same as that of the TEXT type.	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 TEXT type may be less than 1 GB minus 1 byte.

In addition to the restriction on the size of each column, the total size of each tuple cannot exceed 1 GB minus 1 byte 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 A-compatible mode, the received empty string is converted to null by default.

Table 2 lists the two other fixed-length character types supported by GaussDB. 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 char type uses only one byte of storage. It is internally used in the system catalogs as a simplistic enumeration type.