Character types

Supported character types

The table below groups all supported character types by length behavior to help you choose the right type.

Variable-length, no limit

Variable-length, with limit

Fixed-length

Tip: Use text or character varying in most cases. character(n) is the slowest of the three SQL string types because of its additional storage costs from space padding, and it offers no performance advantage in PolarDB for PostgreSQL (Compatible with Oracle).

How character types work

SQL defines two basic character types: character varying(n) and character(n). In both cases, n is a positive integer that sets the maximum number of characters.

Storing strings in length-constrained types

• If a stored string exceeds n characters and the extra characters are not all spaces, an error is raised.

• If the excess characters are all spaces, the string is silently truncated to n characters (required by the SQL standard).

• An explicit cast to character varying(n) or character(n) truncates the value to n characters without raising an error. This is also required by the SQL standard.

Type aliases

• varchar(n) is an alias for character varying(n).

• char(n) is an alias for character(n).

• character without a length specifier is equivalent to character(1). This is an extension specific to PolarDB for PostgreSQL (Compatible with Oracle).

• character varying without a length specifier accepts strings of any length.

Trailing spaces

Trailing spaces are treated differently depending on the type:

• In character values, trailing spaces are semantically insignificant for comparisons and are removed when converting to other string types.

• In character varying and text values, trailing spaces are semantically significant, including in LIKE and regular expression pattern matching.

In collations where space ordering is significant, the treatment of trailing spaces can produce unexpected results. For example:

SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2)

This returns true, even though the C locale considers a space to be greater than a newline.

Storage

Storage overhead depends on string length:

• Strings up to 126 bytes: 1 byte overhead plus the actual byte length (including padded spaces for character).

• Strings longer than 126 bytes: 4 bytes overhead.

Long strings are automatically compressed, which reduces physical disk usage. Very long values are stored in separate background tables so they do not slow down access to shorter column values. The maximum storable string is 1 GB. The maximum value of n in a type declaration is less than 1 GB — but specifying large values of n is rarely useful because the number of characters and bytes can differ significantly in multibyte encodings.

For long strings without a specific upper limit, use text or character varying without a length specifier.

Examples

The following examples show how character and varchar handle padding and truncation.

CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- (1)

  a   | char_length
------+-------------
 ok   |           2

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)
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
SELECT b, char_length(b) FROM test2;

   b   | char_length
-------+-------------
 ok    |           2
 good  |           5
 too l |           5

Special internal types

Two additional fixed-length types are used internally by the database engine and are not intended for application use.

Do not use "char" or name in application tables. Use text or character varying instead.