Varchar vs Text in PostgreSQL

1. the TEXT Data Type in PostgreSQL
2. the VARCHAR Data Type in PostgreSQL
3. VARCHAR vs TEXT in PostgreSQL

There are multiple data types in PostgreSQL to store different kinds of data like integers, dates, strings, etc. However, the string data type has three main types in PostgreSQL: CHAR, VARCHAR, and TEXT.

In this article, we will discuss the VARCHAR and TEXT in detail and compare their differences.

the TEXT Data Type in PostgreSQL

The datatype has no limit on how much text it can store. Therefore, you can insert the text of any kind into the field with this data type, and it will not show any error.

A simple keyword TEXT is used with the variable name preceding the keyword to declare a field of this data type. It is explained below in detail using some examples.

CREATE TABLE text_example (
    id int PRIMARY KEY,
    data TEXT NOT NULL
);

This table has INT and TEXT as two data types to store data. Now let’s insert some data.

INSERT INTO text_example
VALUES
(1,' This text has no size limit because stored in TEXT datatype');

SELECT * FROM text_example;

The following output shows that it is successfully stored in the variable, which is why the TEXT datatype is used in most cases.

the VARCHAR Data Type in PostgreSQL

This data type has two variants. VARCHAR, also called character varying (n), and VARCHAR(n). VARCHAR basically works like the TEXT data type with a different name if no length specifier is defined.

As no limit is defined, you can store unlimited text in it. We can easily demonstrate it by using the previous example and changing the TEXT data type to VARCHAR.

CREATE TABLE varchar_example (
    id int PRIMARY KEY,
    data VARCHAR NOT NULL
);

This table has INT and VARCHAR as two datatypes to store data. Now let’s insert some data.

INSERT INTO varchar_example
VALUES
(1,' This text has no size limit because stored in VARCHAR datatype');

SELECT * FROM varchar_example;

The following output shows that it is successfully stored in the variable:

As we can see, the data is inserted successfully, just like the TEXT data type.

Now, coming to the VARCHAR(n), things get a little bit interesting as now you can limit the number of characters inserted into VARCHAR(n) the data type. An error will be displayed if you try to store more than n characters in a VARCHAR(n) data type.

However, if the characters inserted (that exceed the limit) are all spaces, then there will be no error, and those spaces will be truncated with the string. It can be demonstrated through examples code and their outputs as follows:

CREATE TABLE Nvarchar_example (
    id int PRIMARY KEY,
    data VARCHAR(15) NOT NULL
);

This table has INT and VARCHAR(n) as two data types to store data. Now let’s insert some data.

INSERT INTO Nvarchar_example VALUES (1, 'Size is fifteen');
SELECT * FROM Nvarchar_example;

The following output shows that it is successfully stored in the variable:

As you can see, we did not exceed the specified limit in this case. Hence, we successfully stored the string. However, the example below will demonstrate what will happen if that is not the situation.

CREATE TABLE Nvarchar_example2 (
    id int PRIMARY KEY,
    data VARCHAR(10) NOT NULL
);

Now let’s insert some data.

INSERT INTO Nvarchar_example2 VALUES (1, 'Size is fifteen');

The output, as you can see, shows an error regarding the size of the data type:

One significant reason for using VARCHAR is that it allows you to restrict the number of characters in a datatype. It is useful when you need to put a constraint on inserting a string.

PostgreSQL will throw an error preventing from adding more characters than the length specifier’s defined limit.

VARCHAR vs TEXT in PostgreSQL

Now, after describing their characteristics above, we think you will be able to judge more clearly when and when not to use either of these data types. Finally, we will discuss some common scenarios to strengthen your understanding.

In other databases, there is quite a lot of performance impact when using either of these datatypes, but in PostgreSQL, there isn’t any noticeable performance difference between the two types.

There may be some increased storage space or a few extra CPU cycles involved to validate the length specifier limit before inserting, but that is negligible.

As there is no real performance benefit involving the two data types, the only fundamental and essential point is whether you want to limit the size of the characters being inserted or not.

The VARCHAR(n) provides validation, and PostgreSQL will display an error message if you cross the defined limit.

Other than that, it is not recommended to use normal VARCHAR over TEXT as both provide equivalent functionalities, and TEXT has a different name that is easy to remember and associated with unlimited string length.