Character types - PolarDB - Alibaba Cloud Documentation Center

This topic describes the common character types in PolarDB for PostgreSQL(Compatible with Oracle).

The following table lists the common character types.


Name	Description
CHAR[(n)]	The fixed-length string. If the length is less than n, the string is padded with spaces.
CLOB	The large variable-length string. The maximum size is 1 GB.
LONG	The variable-length string with unlimited length.
NVARCHAR(n)	The variable-length string with a limited length.
NVARCHAR2(n)	The variable-length string with a limited length.
STRING	The alias of VARCHAR2.
VARCHAR(n)	The variable-length string with a limited length. (This data type is provided for the purpose of compatibility and is not recommended.)
VARCHAR2(n)	The variable-length string with a limited length.
TEXT	The large variable-length string. The maximum size is 1 GB.

Note In the preceding table, n is a positive integer. These types can store strings that contain up to n characters in length. An attempt to assign a value that exceeds the length of n will result in an error, unless the excess characters are all spaces. In this case, the string will be truncated to the maximum length.

CHAR

For the CHAR type, if you do not specify a value for n, the default value is 1. If the string to be stored is shorter than n, a value of the CHAR type is space-padded to the specified length n, and is stored and displayed that way.

Padding spaces are insignificant in semantics. This indicates that trailing spaces are disregarded when you compare two CHAR values, and the spaces are removed when a CHAR value is converted to another string type.

If you explicitly cast a value to a CHAR(n) type, an over-length value is truncated to n characters without generating an error. This is the same as that specified by the SQL standards.

VARCHAR, VARCHAR2, NVARCHAR, and NVARCHAR2

If the string to be assigned is shorter than n, the VARCHAR, VARCHAR2, NVARCHAR, and NVARCHAR2 value types store the string of the actual length without padding spaces.

Note Trailing spaces of the VARCHAR values are significant in semantics.

If you explicitly cast a value to a VARCHAR type, an over-length value is truncated to n characters without generating an error. This is the same as that specified by the SQL standards.

CLOB

You can store a long string in the CLOB type. CLOB is equivalent to VARCHAR2 in semantics except that no length limit is specified for CLOB. We recommend that you use the CLOB type instead of the VARCHAR2 type if the maximum string length is unknown.

Note The maximum size of the character string that can be stored in the CLOB type is about 1 GB.

TEXT

Long strings can be stored in the TEXT type. The CLOB type is similar to the TEXT type and its length is unlimited. This type be used to store strings whose length is unknown.

Note The maximum size of the character string that can be stored in the TEXT type is about 1 GB.

Considerations

The storage requirement for data of these types is the actual string plus 1 byte if the string is less than 127 bytes. If the string is 127 bytes or larger, the data of these types is the actual string plus 4 bytes. For data of the CHAR type, the trailing spaces of a string also require the storage space. Long strings are automatically compressed by the system. In this case, less actual storage space is required on disks. Long values are stored in background tables so they do not interfere with rapid access to the shorter column values.

The database character set determines the character set used to store textual values.