Character Types

Name	Description
character varying(n), varchar(n)	variable-length with limit
character(n), char(n)	fixed-length, blank padded
text	variable unlimited length

Name	Storage Size	Description
“char”	1 byte	single-byte internal type
name	64 bytes	internal type for object names

SQL defines 2 primary character types - character varying(n) (varchar(n)) and character(n) (char(n)).

Both store strings up to n characters in length (n must be positive).

Strings longer than expected raise an error, unless the excess characters are all spaces, in which case the string will be truncated to the maximum length.

Explicitly casting a value to char(n) or varchar(n) silently truncates over-length values to n characters.

The database character set is selected when the database is created. The character with code zero can’t be stored.

Short strings (up to 126 bytes) have an overhead of 1 byte. Long strings have an overhead of 4 bytes.

Long strings are automatically compressed, to save disk space.

Very long values are stored in background tables to ensure rapid access to shorter values.

The longest possible character string that can be stored is 1 GB.

1. Variable-Length Types

1.1 Character Varying(n)

Strings shorter than declared are stored as they are.

Trailing spaces are semantically significant, as in text values and pattern matching.

Without n, accepts strings of any size (postgres extension).

mydb=> CREATE TABLE test2 (b varchar(5));
CREATE TABLE
mydb=> INSERT INTO test2 VALUES ('ok'), ('good       ');
INSERT 0 2
mydb=> INSERT INTO test2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
mydb=> INSERT INTO test2 VALUES ('too long'::varchar(5));
INSERT 0 1
mydb=> SELECT b, char_length(b) FROM test2;
   b   | char_length 
-------+-------------
 ok    |           2
 good  |           5
 too l |           5
(3 rows)

1.2 Text

The text type stores strings of any length.

It is not standard SQL, but has been implemented by several other DBMS as well.

2. Fixed-Length Types

2.1 Character(n)

Strings shorter than declared are space-padded. They are stored and displayed this way.

Trailing spaces are treated as semantically insignificant, and disregarded in char(n) - char(n) comparisons.

Trailing spaces are removed when converting to other string types.

Without n, is equivalent to char(1).

mydb=> CREATE TABLE test1 (a character(4));
CREATE TABLE
mydb=> INSERT INTO test1 VALUES ('ok');
INSERT 0 1
mydb=> INSERT INTO test1 VALUES ('good  ');
INSERT 0 1
mydb=> INSERT INTO test1 VALUES ('too long  ');
ERROR:  value too long for type character(4)
mydb=> SELECT a, char_length(a) FROM test1;
  a   | char_length 
------+-------------
 ok   |           2
 good |           4
(2 rows)

2.2 Name

Not for general use. Exists only for the storage of identifiers in internal system catalogs.

2.3 “Char”

Used internally as a simplistic enumeration type in system catalogs.