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

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 string. 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.

When sql_compatibility is set to 'B', if b_format_version is set to '5.7' and b_format_dev_version is set to 's1':

  • Type: n indicates the string length. The value range is [0,10485760]. If the precision n is not specified, the default precision is 1. Fixed-length string. Empty characters are filled in with blank spaces.
  • 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 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 bytes – Length of the first n columns. For example, the maximum length of (a int, b varchar) is 1,073,741,735 bytes (= 1 GB – 85 bytes – 4 bytes).

When sql_compatibility is set to 'B', if b_format_version is set to '5.7' and b_format_dev_version is set to 's1':

  • n indicates the byte 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.

The maximum value of n is 10485760 (10 MB).

If n is not specified, the maximum storage length is 1 GB – 85 bytes – Length of the first n columns. For example, the maximum length of (a int, b varchar) is 1,073,741,735 bytes (= 1 GB – 85 bytes – 4 bytes).

VARCHAR2(n)

Variable-length string. It is an alias for VARCHAR(n) type, which is compatible with Oracle.

The maximum value of n is 10485760 (10 MB).

If n is not specified, the maximum storage length is 1 GB – 85 bytes – Length of the first n columns. For example, the maximum length of (a int, b varchar) is 1,073,741,735 bytes (= 1 GB – 85 bytes – 4 bytes).

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 bytes – Length of the first n columns. For example, the maximum length of (a int, b varchar) is 1,073,741,735 bytes (= 1 GB – 85 bytes – 4 bytes).

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

Large text object, which is compatible with Oracle.

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.

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

If sql_compatibility is set to 'B', this parameter takes effect only when b_format_version is set to '5.7' and b_format_dev_version is set to 's1'.

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 this type may be less than 1 GB minus 1 byte.

  1. 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.
  2. NCHAR is the alias of the bpchar type, and VARCHAR2(n) is the alias of the VARCHAR(n) type.
  3. 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.
  4. In A-compatible mode, the received empty string is converted to null by default.

In GaussDB, there are two other fixed-length character types, as described 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 char type uses only 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