Table 3-4. Character Types
| Type Name | Storage | Description |
|---|---|---|
| character(n), char(n) | (4+n) bytes | Fixed-length blank padded |
| character varying(n), varchar(n) | (4+n) bytes | Variable-length with limit |
| text | (4+n) bytes | Variable unlimited length |
SQL defines two primary character types: character(n) and character varying(n), where n is a positive integer. Both of these types can store strings up to n characters 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 the SQL standard.) If the string to be stored is shorter than the declared length, values of type character will be space-padded; values of type character varying will simply store the shorter string.
Note: Prior to PostgreSQL 7.2, strings that were too long were silently truncated, no error was raised.
The notations char(n) and varchar(n) are aliases for character(n) and character varying(n), respectively. character without length specifier is equivalent to character(1); if character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.
In addition, PostgreSQL supports the more general text type, which stores strings of any length. Unlike character varying, text does not require an explicit declared upper limit on the size of the string. Although the type text is not in the SQL standard, many other RDBMS packages have it as well.
Refer to Section 1.1.2.1 for information about the syntax of string literals, and to Chapter 4 for information about available operators and functions.
Tip: There are no performance differences between these three types, apart from the increased storage size when using the blank-padded type.
Example 3-1. Using the character types
CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- (1)
a | char_length
------+-------------
ok | 4
CREATE 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)
SELECT b, char_length(b) FROM test2;
b | char_length
-------+-------------
ok | 2
good | 5There are two other fixed-length character types in Postgres. The name type exists only for storage of internal catalog names and is not intended for use by the general user. Its length is currently defined as 32 bytes (31 characters plus terminator) but should be referenced using the macro NAMEDATALEN. The length is set at compile time (and is therefore adjustable for special uses); the default maximum length may change in a future release. The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used in the system catalogs as a poor-man's enumeration type.