PostgreSQL Character Data Types

Character types store data in text form, and PostgreSQL provides three character data types: CHAR, VARCHAR, and TEXT.

Usages of PostgreSQL character types

The following illustrates the usages of the three character types in PostgreSQL:

• The CHAR(n) stores a fixed-length string. If the length is insufficient, use blank padding.
• The VARCHAR(n) stores variable-length text with length limit.
• The VARCHAR stores variable-length text without length limit. It is as same as TEXT exactly.
• The TEXT stores variable-length text without length limit.

CHAR(n) and VARCHAR(n) can store up to n characters.

For character types with limited length, if the written content exceeds the limited length, PostgreSQL adopts the following strategy:

• If there are only spaces beyond the length, the extra spaces are truncated.
• If the part beyond the length is not just a space, an error message will be happened.

In PostgreSQL, there is no performance difference between the three character types, so you just need to choose the appropriate type for your needs. Normally you should use TEXT or VARCHAR.

PostgreSQL character type Examples

These examples illustrates how CHAR, VARCHAR and TEXT data types work.

Create a new table for testing with the following statement :

CREATE TABLE test_character_type (
  char_1 CHAR(1),
  varchar_10 VARCHAR(10),
  txt TEXT
);

CHAR Example

Insert a new row into the table that exceeds the length of char_1 column using the following statement:

INSERT INTO test_character_type (char_1)
VALUES('OK')
RETURNING *;

PostgreSQL gave an error:

ERROR:  value too long for type character(1)

This is because the data type of the char_1 column is CHAR(1) and we were trying to insert a string of 2 characters into the column: 'OK'.

Use the following statement to insert a new row into the table that exceeds the length of the char_1 column with spaces:

INSERT INTO test_character_type (char_1)
VALUES('Y    ')
RETURNING *;

 char_1 | varchar_10 | txt
--------+------------+-----
 Y      |            |

Insertion is successful here. This is because the excess is spaces, and PostgreSQL automatically truncates spaces.

VARCHAR Example

Insert a new row into the table that exceeds varchar_10 the column :

INSERT INTO test_character_type (varchar_10)
VALUES('Hello World')
RETURNING *;

PostgreSQL issues an error:

ERROR:  value too long for type character varying(10)

This is because the data type of the varchar_10 column is VARCHAR(10) and we were trying to insert a string with 11 characters into the column: 'Hello World'.

Use the following statement to insert a new row into the table that exceeds the length of the varchar_10 column with spaces:

INSERT INTO test_character_type (varchar_10)
VALUES('HelloWorld    ')
RETURNING *;

 char_1 | varchar_10 | txt
--------+------------+-----
        | HelloWorld |
(1 row)

Insertion is successful here. This is because the excess is spaces, and PostgreSQL automatically truncates spaces.

TEXT Example

Insert a new row into the table using the following statement:

INSERT INTO test_character_type (txt)
VALUES('TEXT column can store a string of any length')
RETURNING txt;

                     txt
----------------------------------------------
 TEXT column can store a string of any length
(1 row)

For a TEXT column, you can insert strings of any length.

Summarize

PostgreSQL provides three character data types: CHAR, VARCHAR, and TEXT. CHAR is a fixed-length character type, while VARCHAR and TEXT are variable-length character types.