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:
sudo service postgresql start
  • You can see if the service is running with the command shown below:
service postgresql status

The output of this command will look like the following:

â— postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)

Aug 01 14:51:20 user-UX330UAK systemd[1]: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd[1]: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
  • 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

Restarting PostgreSQL server in a Windows Machine

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:

CREATE TABLE pgsql_char_sample (
id serial PRIMARY KEY,
my_char VARCHAR (8),
my_text TEXT
);

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

INSERT INTO pgsql_char_sample (my_char, my_text)
VALUES
(
'This sample string is for the column varchar',
'This sample string is for the text column to be inserted in the PostgreSQL table'
);

The statement shown above will result in an error:

ERROR: VALUE too long FOR TYPE CHARACTER VARYING(8)

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:

INSERT INTO pgsql_char_sample (my_char, my_text)
VALUES
(
'String',
'This sample string is for the text column to be inserted in the PostgreSQL table'
);

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

SELECT * FROM pgsql_char_sample;

The result should look something like this:

id | my_char | my_text
----+---------+----------------------------------------------------------------------------------
1 | String | This sample string IS FOR the text COLUMN TO be inserted IN the PostgreSQL TABLE
(1 ROW)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.