Character types v15
Name | Native | Alias | Description |
---|---|---|---|
CHAR[(n)] | ✅ | Fixed-length character string, blank padded to the size specified by n | |
CHARACTER[(n)] | ✅ | Fixed-length character string, blank padded to the size specified by n | |
CHARACTER VARYING[(n)] | ✅ | Variable-length character string, with limit | |
CLOB | Custom type for emulating Oracle CLOB, large variable-length up to 1GB (see Migration Handbook) | ||
LONG | ✅ | Alias for TEXT | |
NCHAR[(n)] | ✅ | Alias for CHARACTER | |
NVARCHAR[(n)] | ✅ | Alias for CHARACTER VARYING | |
NVARCHAR2[(n)] | ✅ | Alias for CHARACTER VARYING | |
STRING | ✅ | Alias for VARCHAR2 | |
TEXT | ✅ | Variable-length character string, unlimited | |
VARCHAR[(n)] | ✅ | Variable-length character string, with limit (considered deprecated, but supported for compatibility) | |
VARCHAR2[(n)] | ✅ | Alias for CHARACTER VARYING |
Overview
SQL defines two primary character types: CHARACTER VARYING(n)
and CHARACTER(n)
, where n
is a positive integer. These types can store strings up to n
characters in length. If you don't specify a value for n
, n
defaults to 1
. Assigning a value that exceeds the length of n
results in an error unless the excess characters are all spaces. In this case, the string is truncated to the maximum length. If the string to be stored is shorter than n
, values of type CHARACTER
are space padded to the specified width (n
) and are stored and displayed that way. Values of type CHARACTER VARYING
store the shorter string.
If you explicitly cast a value to CHARACTER VARYING(n)
or CHARACTER(n)
, an over-length value is truncated to n characters without raising an error.
The notations VARCHAR(n)
and CHAR(n)
are aliases for CHARACTER VARYING(n)
and CHARACTER(n)
, respectively. If specified, the length must be greater than zero and can't exceed 10485760. CHARACTER
without a length specifier is equivalent to CHARACTER(1)
. If CHARACTER VARYING
is used without a length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.
In addition, PostgreSQL provides the TEXT
type, which stores strings of any length. Although the type T$XT isn't in the SQL standard, several other SQL database management systems have it as well.
Values of type CHARACTER
are physically padded with spaces to the specified width n, and are stored and displayed that way. However, trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type CHARACTER
. In collations where whitespace is significant, this behavior can produce unexpected results. For example, SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2)
returns true, even though the C locale considers a space to be greater than a newline. Trailing spaces are removed when converting a CHARACTER
value to one of the other string types. Trailing spaces are semantically significant in CHARACTER VARYING
and TEXT
values and when using pattern matching, that is, LIKE
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
) can't be stored. For more information, see Character Set Support.
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 of CHARACTER
. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they don't interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1GB. (The maximum value that's allowed for n in the data type declaration is less than that. It isn't useful to change this value because with multibyte character encodings, the number of characters and bytes can be quite different. If you want to store long strings with no specific upper limit, use TEXT
or CHARACTER VARYING
without a length specifier, rather than making up an arbitrary length limit.)
Tip
There's no performance difference among these three types, apart from increased storage space when using the blank-padded type and a few extra CPU cycles to check the length when storing into a length-constrained column. While CHARACTER(n)
has performance advantages in some other database systems, there's no such advantage in PostgreSQL. In fact, CHARACTER(n)
is usually the slowest of the three because of its additional storage costs. In most situations, we recommend using TEXT
or CHARACTER VARYING
instead.
See the Postgres documentation on string constants for information about the syntax of string literals. See Functions and Operators for information about available operators and functions.
Example: Using the character types
PostgreSQL has two other fixed-length character types, shown in the following table. These aren't intended for general-purpose use, only for use in the internal system catalogs. The NAME
type is used to store identifiers. Its length is currently defined as 64 bytes (63 usable characters plus terminator) but must be referenced using the constant NAMEDATALEN
in C 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 from CHAR(1)
in that it uses only one byte of storage and therefore can store only a single ASCII character. It's used in the system catalogs as a simplistic enumeration type.
Special character types
Name | Storage size | Description |
---|---|---|
"CHAR" | 1 byte | Single-byte internal type |
NAME | 64 bytes | Internal type for object names |
You can store a large character string in a CLOB
type. CLOB
is semantically equivalent to VARCHAR2
except you don't specify a length limit. Generally, use a CLOB
type if you don't know the maximum string length.
The longest possible character string that you can store in a CLOB
type is about 1GB.
Note
The CLOB
data type is actually a DOMAIN
based on the PostgreSQL TEXT
data type. For information on a DOMAIN
, see the PostgreSQL core documentation.
Thus, use of the CLOB
type is limited by what can be done for TEXT
, such as a maximum size of approximately 1GB.
For larger amounts of data, instead of using the CLOB
data type, use the PostgreSQL large objects feature that relies on the pg_largeobject
system catalog. For information on large objects, see the PostgreSQL core documentation.