Skip to content

Should You Use char, varchar, or text in PostgreSQL?

4 min read

When designing a database schema, you have to choose the correct data types for the data you're expecting to store.

When it comes to strings, PostgreSQL has not just one, but three data types to choose from: char(n), varchar(n), and text.

What's the difference between them, and which one should you choose?

By the end of this article, you will know exactly how the three data types differ, and which one you should use.

The 3 data types for strings in PostgreSQL

PostgreSQL gives you three data types to store strings: char(n), varchar(n), and text. These are the defining characteristics of each data type:

char(n) (or character(n)) has a fixed length of N, where N is the number of characters. When you store a string with less than N characters, the string will be space-padded on the right. Attempting to store a string with more than N characters will result in an error.

varchar(n) (or character varying(n)), like the name implies, has a varying character length not exceeding N. Unlike char(n), varchar(n) doesn't add padding to strings with less than N characters. Similar to char(n), the N character length is enforced on inserts and updates.

text can store strings of any length. There is no maximum limit on the number of characters, and padding is never applied.

When should you use char(n), varchar(n), or text?

There are two reasons why you might consider using char(n) or varchar(n) instead of text: better performance and enforcing a maximum length. Let's examine both.

Performance
PostgreSQL documentation says the following about the performance difference between the three data types:

There is no performance difference among these three types, ... . While char(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL;

Moreover, PostgresSQL Wiki says storing the same string into the three data types will use the same amount of space:

If you insert the same string into the three field types they will take up exactly the same amount of space.

Therefore, using char(n) or varchar(n) over text has no performance benefit.

In fact, char(n) will use up more storage compared to the other two for strings with less than N characters because of padding. Both char(n) and varchar(n) incur an additional CPU cost to check the length, which might be unnecessary if you don't need it.

Enforcing a maximum length
If you want to enforce a maximum length, it's better to use a check constraint instead of relying on char(n) or varchar(n).

Not only is a check constraint more powerful, but it will also save you from future trouble when you decide to change the length limit.

Changing the char(n) or varchar(n) length limit practically means changing the column's data type and therefore it impacts dependent objects (views, functions, foreign keys, etc.). It could result in an expensive query due to a potential rewrite of the table during which other queries are put on hold.

Modifying or dropping a check constraint is a quick operation that only involves reading the table's data. It's also more powerful and allows you to enforce other requirements such as a minimum (or exact) length and a limited set of characters.

/*
  Add a check constraint to the `id` column to enforce
  alphanumeric strings of exactly 5 characters long
*/
ALTER TABLE short_urls ADD CONSTRAINT id CHECK (id ~ '^[a-zA-Z0-9]{5}$');

Conclusion

Always use the text data type when storing strings in PostgreSQL. There is no performance benefit of using char(n) or varchar(n).

If you want to enforce a maximum length, use a check constraint which is more flexible and easier to change.

With this knowledge in hand, you can confidently move forward with designing and deploying your database.

Write clean code. Stay ahead of the curve.

Every other Tuesday, I share tips on how to build robust Node.js applications. Join a community of 1,537 developers committed to advancing their careers and gain the knowledge & skills you need to succeed.

No spam! 🙅🏻‍♀️ Unsubscribe at any time.

You might also like

6 Common Sequelize Queries Explained in SQL

Writing SQL queries can be daunting. We'll unveil the magic by translating 6 common Sequelize queries into raw SQL.
Read article

Node.js 15 Is Out! What Does It Mean for You?

How does this new major release affect you? Find out what the breaking changes are and how to use the new features.
Read article

ESLint Setup in Node.js: A Detailed Guide

Unlock the power of ESLint in Node.js! Dive into this beginner-friendly guide for seamless integration and smarter coding.
Read article