Postgres VARCHAR vs TEXT

Introduction

If you’re working with PostgreSQL, it’s important to have a solid understanding of the different data types so that you can choose the correct ones when creating your database tables. Two common character types used in PostgreSQL are the TEXT and VARCHAR types. Although there are many similarities between these two types, there are also some key differences. In this article, we’ll learn more about these character types and compare the differences between Postgres VARCHAR vs TEXT.

Prerequisites

Before we proceed with our Postgres VARCHAR vs TEXT comparison, let’s pause to review a few prerequisites that are required to follow along with the examples in this article:

• Ensure that PostgreSQL server has been properly installed and configured. The service needs to be running in the background.

Linux and Windows users can download PostgreSQL here:

• To start up PostgreSQL server in a Linux environment, use the following command:

• You can see if the service is running with the command shown below:

The output of this command will look like the following:

• If you’re working on a Windows machine, the following instructions can be used to start, stop and restart PostgreSQL server:

1. Open Control Panel
2. Open Administrative Tools
3. Open Services
4. Locate the PostgreSQL Server service
5. Finally, start, stop or restart the service

What are the PostgreSQL Character Types?

There are three character types in PostgreSQL: character(n), which is commonly known as char(n), character varying(n), which is commonly known as varchar(n), and text. The value of n must be a positive integer for these types. All of the PostgreSQL character types are capable of storing strings up to n characters.

If you try to store a longer string in a column of this PostgreSQL character type, you’ll get an error unless the characters that exceed the limit are composed entirely of spaces. If this is the case, the exceeding string or characters will be truncated to the allowed maximum length.

The VARCHAR data type behaves similarly to the TEXT data type when the value of n is not specified. The performance of both character types is also similar when n is omitted.

Unlike other databases, PostgreSQL character types have no difference in performance amongst them. Most of the time, we’ll be using TEXT or VARCHAR depending on our needs; we use the VARCHAR(n) when we want PostgreSQL to perform checking for length limits.

Examples Using the PostgreSQL Character Types

In this example, we’ll continue our Postgres VARCHAR vs TEXT comparison by looking more closely at the character types and how they work in a PostgreSQL database.

Before we can proceed with our examples, we’ll need to create a table:

After creating our table, we can perform an insert operation:

The statement shown above will result in an error:

This error occurs because we attempted to insert a string with more than eight characters into the my_char column that has a data type of varchar(8).

Let’s try to insert sample data again, this time with different values:

To verify that our insert operation was successful, we can use the following statement:

The result should look something like this:

In the example we looked at, we can see that the only difference between the VARCHAR(n) and TEXT columns is that a limit can be set against the VARCHAR column. In this case, we set the VARCHAR to a length of eight characters, so strings of a longer length cannot be inserted. As we mentioned before, the VARCHAR and TEXT character types have no observable differences in performance.

Conclusion

When you store character data in PostgreSQL, it’s important to choose the right data type for each column in your table. In this article, we compared the Postgres VARCHAR vs TEXT character types, discussing their basic similarities and differences. With the information provided in this article, you’ll be prepared to design your own database tables in PostgreSQL and choose the correct data types for your needs.