7.2. Character Types

Table 7-3 shows the general-purpose character types available in EnterpriseDB.

The two primary character types are CHAR [ (n) ] and VARCHAR(n), where n is a positive integer. Both of these types can store strings up to n characters in length. In the case of type CHAR, n defaults to 1, if omitted. An attempt to store a longer string into a column of these types will result in an error, unless the excess characters are all spaces, in which case the string will be truncated to the maximum length. If the string to be stored is shorter than the declared length, values of type CHAR will be space-padded; values of type VARCHAR will simply store the shorter string.

Values of type CHAR are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type CHAR, and they will be removed when converting a CHAR value to one of the other string types. Note that trailing spaces are semantically significant in VARCHAR values.

A third character type used for storing large character strings is the TEXT data type. TEXT is semantically equivalent to VARCHAR except no length limit is specified. Generally, use TEXT over VARCHAR if the maximum string length is not known.

The longest possible character string that can be stored in a TEXT type is about 1 GB.

The storage requirement for data of these three types is 4 bytes plus the actual string, and in case of CHAR plus the padding. Long strings are compressed by the system automatically, so the physical requirement on disk may be less. Long values are also stored in background tables so they do not interfere with rapid access to the shorter column values.

Refer to Section 3.1.2.1 for information about the syntax of string literals, and to Chapter 8 for information about available operators and functions. The database character set determines the character set used to store textual values; for more information on character set support, refer to Section 34.2.

Data type CHARACTER is a synonym for CHAR. Types CHAR VARYING, CHARACTER VARYING, and VARCHAR2 are synonyms for VARCHAR.

The following are synonyms that can be used for TEXT: CLOB, LONG, LONG VARCHAR, LONGTEXT, LVARCHAR, and MEDIUMTEXT.