PostgreSQL Character Types: CHAR, VARCHAR, and TEXT

Summary: in this tutorial, we will introduce you to the PostgreSQL character data types including char, varchar, and text, and give you some tips to choose the right one to design the database tables.

Introduction to the PostgreSQL character types

PostgreSQL provides three primary character types: character(n) or char(n), character varying(n) or varchar(n), and text, where n is a positive integer.

Both char(n) and varchar(n) can store up to n characters in length. If you try to store a longer string in the column that is either char(n) or varchar(n), PostgreSQL will issue an error.

However, one exception is that if the excess characters are all spaces, PostgreSQL will truncate the spaces to the maximum length and store the string.

If a string casts to a char(n) or varchar(n) explicitly, PostgresQL will truncate the string to n characters before inserting into the table.

The text data type can store a string with unlimited length.

If you do not specify the n integer for the varchar data type, it behaves like the text data type. The performance of the varchar (without n) and text are the same.

The only advantage of specifying the length specifier for the varchar data type is that PostgreSQL will check and issue an error if you try to insert a longer string into the varchar(n) column.

Unlike varchar, The character or  char without the length specifier is the same as the character(1) or char(1).

Different from other database systems, in PostgreSQL, there is no performance difference among three character types. In most situation, you should use text or varchar, and varchar(n) if you want PostgreSQL to check for the length limit.

PostgreSQL character type example

Let’s take a look at an example to see how the char, varchar, and text data types work.

First, we create a new table for the demonstration.

Then, we insert a new row into the character_tests table.

PostgreSQL issued an error:

This is because the data type of the x column is char(1) and we tried to insert a string with three characters into this column. Let’s fix it.

Now we got a different error.

This is because we tried to insert a string with more than 10 characters into the y column with the varchar(10) data type.

The following statement inserts a new row into the character_tests table successfully.

Now you should know how to choose the right character data type for your database table. Most of the time, you should choose text or varchar without length specifier.