Character types

The following table shows the various character types:

PostgreSQL provides two general text types—the char(n) and varchar(n) data types—where n is the number of characters allowed. In the char data type, if a value is less than the specified length, trailing spaces are padded at the end of the value. Operations on the char data types ignore the trailing spaces. Take a look at the following example:

postgres=#SELECT 'a'::CHAR(2) = 'a '::CHAR(2) AS "Trailing space is ignored" ,length('a'::CHAR(10));
 Trailing space is ignored | length 
---------------------------+--------
 t                         | 1
(1 row)

For both the char and varchar data types, if the string is longer than the maximum allowed length, an error will be raised in the case of INSERT or UPDATE unless the extra characters are all spaces. In the latter case, the string will be truncated. In the case of casting, extra characters will be truncated automatically without raising an error. The following example shows how mixing different data types might cause problems:

postgres=# SELECT
        'a '::VARCHAR(2) = 'a '::TEXT AS "Text and varchar",
        'a '::CHAR(2) = 'a '::TEXT AS "Char and text",
        'a '::CHAR(2) = 'a '::VARCHAR(2) AS "Char and varchar";
 Text and varchar | Char and text | Char and varchar 
------------------+---------------+------------------
 t                | f             |  t
(1 row)
postgres=# SELECT length ('a '::CHAR(2)), length ('a '::VARCHAR(2));
 length | length 
--------+--------
      1 | 2
(1 row)

The preceding example shows that 'a '::CHAR(2) equals 'a '::VARCHAR(2), but both have different lengths, which isn't logical. Also, it shows that 'a'::CHAR(2) isn't equal to 'a '::text. Finally, 'a '::VARCHAR(2) equals 'a'::text. The preceding example causes confusion because if variable a is equal to b, and b is equal to c, a should be equal to c according to mathematics.

The PostgreSQL text-storage size depends on several factors, namely, the length of the text value and the text decoding and compression. The text data type can be considered an unlimited varchar() type. The maximum text size that can be stored is 1 GB, which is the maximum column size.

For fixed-length strings, the character data type and the character varying data type consume the same amount of hard disk space. For variable-length strings, the character varying data type consumes less space, because the character type appends the string with space. The following code shows the storage consumption for fixed- and variable-length texts for the character and character varying data types. It simply creates two tables, populates the tables with fictional data using fixed- and variable-length strings, and gets the table size in a human-readable form:

CREATE TABLE char_size_test (
 size CHAR(10)
);
CREATE TABLE varchar_size_test(
 size varchar(10)
);
WITH test_data AS (
 SELECT substring(md5(random()::text), 1, 5) FROM generate_series (1, 1000000)
),char_data_insert AS (
 INSERT INTO char_size_test SELECT * FROM test_data
)INSERT INTO varchar_size_test SELECT * FROM test_data;

Use this code to get the table size:

postgres=# \dt+ varchar_size_test 
                          List of relations
 Schema | Name              | Type  | Owner    | Size  | Description 
--------+-------------------+-------+----------+-------+-------------
 public | varchar_size_test | table | postgres | 35 MB | 
(1 row)

postgres=# \dt+ char_size_test 
                        List of relations
 Schema | Name           | Type  | Owner    | Size  | Description 
--------+----------------+-------+----------+-------+-------------
 public | char_size_test | table | postgres | 42 MB | 
(1 row)

The varchar data type can be emulated by the text data type and a check constraint to check the text length. For example, the following code snippets are semantically equivalent:

CREATE TABLE emulate_varchar(
 test VARCHAR(4)
);
--semantically equivalent to
CREATE TABLE emulate_varchar (
 test TEXT,
 CONSTRAINT test_length CHECK (length(test) <= 4)
);

In PostgreSQL, there's no difference in performance between the different character types, so it's recommended you use the text data type. This allows the developer to react quickly to the changes in business requirements. For example, one common business case is changing the text length, such as changing the length of a customer ticket number from six to eight characters due to length limitation, or changing how certain information is stored in the database. In such a scenario, if the data type is text, this could be done by amending the check constraint without altering the table structure. The full documentation of character datatypes can be found at https://www.postgresql.org/docs/current/static/datatype-character.html.