August 1, 2023
Summary: in this tutorial, you will learn about the PostgreSQL character data types including
TEXT, and learn how to select the right character types for your tables.
Introduction to the PostgreSQL character types
PostgreSQL provides three primary character types:
CHARACTER VARYING(n) or
n is a positive integer.
The following table illustrates the character types in PostgreSQL:
||variable-length with length limit|
||fixed-length, blank padded|
||variable unlimited length|
VARCHAR(n)can store up to
n characters. If you try to store a string that has more than
n 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 characters.
If a string explicitly casts to a
VARCHAR(n), PostgreSQL will truncate the string to
n characters before inserting it into the table.
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 datatype. The performance of the
VARCHAR (without the size
TEXT are the same.
The advantage of specifying the length specifier for the
VARCHAR data type is that PostgreSQL will issue an error if you attempt to insert a string that has more than
n characters into the
CHAR without the length specifier (
n) is the same as the
Different from other database systems, in PostgreSQL, there is no performance difference among the three character types.
In most cases, you should use
VARCHAR. And you use the
VARCHAR(n) when you want PostgreSQL to check for the length.
PostgreSQL character type examples
Let’s take a look at an example to see how the
TEXT datatypes work.
First, create a new table called character_tests:
CREATE TABLE character_tests ( id serial PRIMARY KEY, x CHAR (1), y VARCHAR (10), z TEXT );
Then, insert a new row into the
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 long for type character(1)
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:
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 long for type character varying(10)
This is because we tried to insert a string with more than 10 characters into the column
y that has the
The following statement inserts a new row into the
character_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' ); SELECT * FROM character_tests;
id | x | y | z ----+---+------------+--------------------------------------------------------- 1 | Y | varchar(n) | This is a very long text for the PostgreSQL text column (1 row)
- PostgreSQL supports
TEXTdata types. The
CHARis fixed-length character type while the
TEXTare varying length character types.
VARCHAR(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) and