Introduction to the PostgreSQL character types

PostgreSQL provides three primary character types:

• CHARACTER(n) orCHAR(n)
• CHARACTER VARYING(n) orVARCHAR(n)
• TEXT

In this syntax,n is a positive integer that specifies the number of characters.

The following table illustrates the character types in PostgreSQL:

BothCHAR(n) andVARCHAR(n) can store up ton characters. If you attempt to store a string that has more thann characters, PostgreSQL will issue an error.

However, one exception is that if the excessive characters are all spaces, PostgreSQL truncates the spaces to the maximum length (n) and stores the trimmed characters.

If a string explicitlycasts to aCHAR(n) orVARCHAR(n), PostgreSQL will truncate the string ton characters before inserting it into the table.

TheTEXT data type can store a string with unlimited length.

If you do not specify the n integer for theVARCHAR data type, it behaves like theTEXT datatype. The performance of theVARCHAR (without the sizen) andTEXT are the same.

The advantage of specifying the length specifier for theVARCHAR data type is that PostgreSQL will issue an error if you attempt to insert a string that has more thann characters into theVARCHAR(n) column.

UnlikeVARCHAR, TheCHARACTER orCHAR without the length, specifier (n) is the same as theCHARACTER(1) orCHAR(1).

Different from other database systems, in PostgreSQL, there is no performance difference among the three character types.

In most cases, you should useTEXTorVARCHAR and use theVARCHAR(n) only when you want PostgreSQL to check the length.

PostgreSQL character type examples

Let’s take a look at an example to understand how theCHAR,VARCHAR, andTEXT data types work.

First,create a new table calledcharacter_tests:

CREATE TABLE character_tests (  id serial PRIMARY KEY,  x CHAR (1),  y VARCHAR (10),  z TEXT);

Then,insert a new row into thecharacter_tests table:

INSERT INTO character_tests (x, y, z)VALUES  (    'Yes','This is a test for varchar',    'This is a very long text for the PostgreSQL text column'  );

PostgreSQL issued an error:

ERROR:value too longfor type character(1)

This is because the data type of thex column ischar(1) and we attempted to insert a string with three characters into this column.

Let’s fix it:

INSERT INTO character_tests (x, y, z)VALUES  (    'Y',    'This is a test for varchar',    'This is a very long text for the PostgreSQL text column'  );

PostgreSQL issues a different error:

ERROR:value too longfor type character varying(10)

This is because we attempted to insert a string with more than 10 characters into the columny that has thevarchar(10) datatype.

The following statement inserts a new row into thecharacter_tests table successfully.

INSERT INTO character_tests (x,y, z)VALUES  (    'Y',    'varchar(n)',    'This is a very long text for the PostgreSQL text column'  )RETURNING *;

Output:

id | x |     y      |                            z----+---+------------+---------------------------------------------------------  1 | Y | varchar(n)| This is a very long text for the PostgreSQL text column(1 row)

Summary

• PostgreSQL supportsCHAR,VARCHAR, andTEXT data types. TheCHAR is a fixed-length character type while theVARCHAR andTEXT are varying length character types.
• UseVARCHAR(n) if you want to validate the length of the string (n) before inserting into or updating to a column.
• VARCHAR (without the length specifier) andTEXT are equivalent.

Last updated onFebruary 2, 2024

On this page

• Introduction to the PostgreSQL character types
• PostgreSQL character type examples
• Summary