Movatterモバイル変換

Table 8.4 shows the general-purpose character types available inPostgreSQL.

SQL defines two primary character types:character varying(n) andcharacter(n), wheren is a positive integer. Both of these types can store strings up ton characters (not bytes) in length. 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. (This somewhat bizarre exception is required by theSQL standard.) However, if one explicitly casts a value tocharacter varying(n) orcharacter(n), then an over-length value will be truncated ton characters without raising an error. (This too is required by theSQL standard.) If the string to be stored is shorter than the declared length, values of typecharacter will be space-padded; values of typecharacter varying will simply store the shorter string.

In addition,PostgreSQL provides thetext type, which stores strings of any length. Although thetext type is not in theSQL standard, several other SQL database management systems have it as well.text isPostgreSQL's native string data type, in that most built-in functions operating on strings are declared to take or returntext notcharacter varying. For many purposes,character varying acts as though it were adomain overtext.

The type namevarchar is an alias forcharacter varying, whilebpchar (with length specifier) andchar are aliases forcharacter. Thevarchar andchar aliases are defined in theSQL standard;bpchar is aPostgreSQL extension.

If specified, the lengthn must be greater than zero and cannot exceed 10,485,760. Ifcharacter varying (orvarchar) is used without length specifier, the type accepts strings of any length. Ifbpchar lacks a length specifier, it also accepts strings of any length, but trailing spaces are semantically insignificant. Ifcharacter (orchar) lacks a specifier, it is equivalent tocharacter(1).

Values of typecharacter are physically padded with spaces to the specified widthn, and are stored and displayed that way. However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values of typecharacter. In collations where whitespace is significant, this behavior can produce unexpected results; for exampleSELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) returns true, even thoughC locale would consider a space to be greater than a newline. Trailing spaces are removed when converting acharacter value to one of the other string types. Note that trailing spacesare semantically significant incharacter varying andtext values, and when using pattern matching, that isLIKE and regular expressions.

The characters that can be stored in any of these data types are determined by the database character set, which is selected when the database is created. Regardless of the specific character set, the character with code zero (sometimes called NUL) cannot be stored. For more information refer toSection 24.3.

The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case ofcharacter. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB. (The maximum value that will be allowed forn in the data type declaration is less than that. It wouldn't be useful to change this because with multibyte character encodings the number of characters and bytes can be quite different. If you desire to store long strings with no specific upper limit, usetext orcharacter varying without a length specifier, rather than making up an arbitrary length limit.)

Refer toSection 4.1.2.1 for information about the syntax of string literals, and toChapter 9 for information about available operators and functions.

CREATE TABLE test1 (a character(4));INSERT INTO test1 VALUES ('ok');SELECT a, char_length(a) FROM test1; --(1)  a   | char_length------+------------- ok   |           2CREATE TABLE test2 (b varchar(5));INSERT INTO test2 VALUES ('ok');INSERT INTO test2 VALUES ('good      ');INSERT INTO test2 VALUES ('too long');ERROR:  value too long for type character varying(5)INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncationSELECT b, char_length(b) FROM test2;   b   | char_length-------+------------- ok    |           2 good  |           5 too l |           5

There are two other fixed-length character types inPostgreSQL, shown inTable 8.5. These are not intended for general-purpose use, only for use in the internal system catalogs. Thename type is used to store identifiers. Its length is currently defined as 64 bytes (63 usable characters plus terminator) but should be referenced using the constantNAMEDATALEN inC source code. The length is set at compile time (and is therefore adjustable for special uses); the default maximum length might change in a future release. The type"char" (note the quotes) is different fromchar(1) in that it only uses one byte of storage, and therefore can store only a single ASCII character. It is used in the system catalogs as a simplistic enumeration type.