intro
Let’s enter the world of the PostgreSQL string data types and clarify once and for all the differences between TEXT and VARCHAR.
One of the most important aspects when designing a database is to choose the correct data types. Text fields are among the most common, and PostgreSQL offers several character types. Specifically, TEXT and VARCHAR are the two most popular string types. What are the differences between the two? When to choose VARCHAR over TEXT and vice versa? Find it out in this Postgres TEXT vs VARCHAR guide!
Introduction to the PostgreSQL VARCHAR and TEXT Data Types
VARCHAR and TEXT are both PostgreSQL character data types. VARCHAR(N) is an alias for CHARACTER VARYING(N), a data type that accepts text data. N is a positive integer and represents the maximum number of characters the type can store in length. In other words, a VARCHAR(N) column can store a string of up to N characters. When trying to store a longer string, PostgreSQL raises the following error:
Value too long for type character varying(N)
As required by the SQL standard, if the characters in excess are all spaces, the string will be truncated to the maximum length and no error will be thrown.
Instead, when casting a value to CHARACTER VARYING(N) or VARCHAR(N), the over-length string will simply be truncated to the first N characters without raising an error. Again, this is a rule imposed by the SQL standard. The length N is optional and must be an integer greater than zero and smaller than or equal to 10485760. When N is omitted, VARCHAR is equivalent to TEXT.
TEXT is a data type to store strings of any length. In detail, it is PostgreSQL's native string data type as most built-in functions operating on strings accept or return TEXT values. Keep in mind that TEXT is not in the SQL standard data type, but most DMBS technologies provide it.
The characters that can be stored in either of these data types are determined by the database character set, which is chosen when creating the database. To avoid slowing down access to columns with short values, very long strings are stored in background tables. In any case, the longest character string that can be stored in VARCHAR or TEXT is about 1 GB.
Postgres TEXT vs VARCHAR: Which Should You Use?
The many difference between VARCHAR and TEXT lies in the number of characters they can store. VARCHAR can have a character limit, while TEXT does not. This means that choosing between one data type or another boils down to a simple rule:
Note that the TEXT data type may be better for readability reasons than VARCHAR without the length specifier.
TEXT and VARCHAR Examples
Time to dig into the Postgres TEXT vs VARCHAR comparison with some real-world scenarios. To make everything easier, we will execute our queries in DbVisualizer, a powerful database client that fully supports PostgreSQL and many other databases.
Assume you have the following products table:
As you can see, description is a TEXT field because it can be a string of indefinite length. Instead, tagline is a short string that cannot exceed 50 characters. Do not forget that CHARACTER VARYING(50) has the same meaning as VARCHAR(50).
Let's verify that the length constraint on tagline works as expected. Try to insert a new product with a very long tagline:
1
INSERT INTO products(name, tagline)
2
VALUES ('Arex GLX', 'This is the best smartphone on the market, with a top-notch screen and 3 cameras!');
The query will fail with the error below:
Instead, a product with a shorter tagline will be added without an error:
1
INSERT INTO products(name, tagline)
2
VALUES ('Arex GLX', Simply the best smartphone on the market!');
Similarly, when the characters in excess are spaces, the tagline will be trimmed out and the product added to the database:
1
INSERT INTO products(name, tagline)
2
3
VALUES ('Arex GLX Pro', 'Simply the best smartphone on the market! ');
If you look for the Arex GLX Pro product, you will see a tagline truncated to the first 50 characters:
When casting long strings to VARCHAR, the exceeding characters are simply ignored:
1
SELECT 'This is a very long description that involves a lot of details'::VARCHAR(10);
Postgres TEXT vs VARCHAR: Comparison Table
The Postgres TEXT vs VARCHAR comparison can be summarized in the table below:
| TEXT | VARCHAR(N) | |
|---|---|---|
| Data type | Invariable-length character type | Variable-length character type |
| Character length limit | None, it can store strings of any length | Specified by the N parameter, where N is the maximum character length |
| Storage | Uses as much space as required to store the string | Uses as much space as required to store the string |
| Performance | Same performance as VARCHAR | Same performance as TEXT |
| Indexing | Supported via several indexes | Supported via several indexes |
| Character encoding | Supported | Supported |
| Migration | Migration may involve text truncation | Can be easily migrated to TEXT. |
| NULL values | Can store null values. | Can store null values. |
| Use cases | Suitable for storing text of varying lengths, including long paragraphs, comments, and textual data. | Suitable for fields with specific character length constraints like usernames, product codes, and titles. |
Conclusion
In this article, you learned what TEXT and VARCHAR types are in PostgreSQL, what data they can store, and how they work. In detail, you understood that they are two popular character data types and saw their main differences. You can now easily choose between TEXT vs VARCHAR Postgres types.
Dealing with strings becomes easier when you can visually explore them. That is where DbVisualizer comes in! This database client supports all PostgreSQL features and dozens of other DBMSs. DbVisualizer also offers drag-and-drop query construction functionality, top-notch query optimization capabilities, automatic ER generation, and much more. Manage your databases like a pro. Download DbVisualizer for free now!
FAQ
Why does PostgreSQL have both VARCHAR and TEXT data types?
PostgreSQL offers both VARCHAR and TEXT for flexibility. VARCHAR(N) enforces length limits, ensuring data integrity. TEXT is instead more versatile, ideal for variable-length data. The choice between the two depends on specific use cases and data constraints.
How do TEXT and VARCHAR Postgres Types Differ in Terms of Storage?
Both VARCHAR and TEXT are dynamic character types. This means that they only consume as much space as required to store the strings. So, when storing the “Hello!” string in a TEXT or VARCHAR(50) column, only 6 characters will be written to the disk.
Which data type wins the Postgres TEXT vs VARCHAR performance comparison?
As explained in the docs, there is no performance difference between TEXT and VARCHAR, other than a few more CPU cycles required to check the string length constraints. Thus, there is no real winner in the Postgres TEXT vs VARCHAR performance comparison. It is a tie!
Are there any considerations when using PostgreSQL TEXT or VARCHAR with popular ORM libraries?
When using the TEXT or VARCHAR Postgres data types with popular ORM libraries, consider mapping your model fields correctly. In most cases, they can both be mapped to the string type at the application level. When dealing with VARCHAR(N) columns, do not forget to define a character length constraint in your model as supported by the ORM technology.
How do you enforce character length validation for TEXT and VARCHAR in Postgres?
Character length validation for TEXT and VARCHAR in PostgreSQL can be enforced through:
