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

This article was originally published at https://maximorlov.com/char-varchar-text-postgresql/

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.

Note: PostgreSQL allows you to use varchar (without length n), in which case it's equivalent to text.

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 specific, 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 specific 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 developers committed to advancing their careers and gain the knowledge & skills you need to succeed.

Subscribe for success!