POSTGRESQL
TEXT
VARCHAR

Postgres TEXT vs VARCHAR: Comparing String Data Types

intro

Let’s enter the world of the PostgreSQL string data types and clarify once and for all the differences between TEXT and VARCHAR.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

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:

  • If you need to ensure that the length of the string to store in a column does not exceed N characters, go for VARCHAR(N)
  • In all other cases, prefer VARCHAR without the length specifier or TEXT.

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:

The DDL definition of “products” automatically produced by DbVisualizer
The DDL definition of “products” automatically produced by DbVisualizer

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:

Copy
        
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:

Note the error
Note the error

Instead, a product with a shorter tagline will be added without an error:

Copy
        
1 INSERT INTO products(name, tagline) 2 VALUES ('Arex GLX', Simply the best smartphone on the market!');
This time, the product was added succesfully
This time, the product was added succesfully

Similarly, when the characters in excess are spaces, the tagline will be trimmed out and the product added to the database:

Copy
        
1 INSERT INTO products(name, tagline) 2 3 VALUES ('Arex GLX Pro', 'Simply the best smartphone on the market!                                   ');
Again, the product was added successfully
Again, the product was added successfully

If you look for the Arex GLX Pro product, you will see a tagline truncated to the first 50 characters:

Note the truncated tagline
Note the truncated tagline

When casting long strings to VARCHAR, the exceeding characters are simply ignored:

Copy
        
1 SELECT 'This is a very long description that involves a lot of details'::VARCHAR(10);
The string was truncated with no error
The string was truncated with no error

Postgres TEXT vs VARCHAR: Comparison Table

The Postgres TEXT vs VARCHAR comparison can be summarized in the table below:

TEXTVARCHAR(N)
Data typeInvariable-length character typeVariable-length character type
Character length limitNone, it can store strings of any lengthSpecified by the N parameter, where N is the maximum character length
StorageUses as much space as required to store the stringUses as much space as required to store the string
PerformanceSame performance as VARCHARSame performance as TEXT
IndexingSupported via several indexesSupported via several indexes
Character encodingSupportedSupported
MigrationMigration may involve text truncationCan be easily migrated to TEXT.
NULL valuesCan store null values.Can store null values.
Use casesSuitable 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:

  • Constraints: For VARCHAR, define a column with a specified length limit using VARCHAR(n). PostgreSQL will enforce the limit, rejecting input that exceeds it.
  • CHECK Constraint: Create a CHECK constraint for TEXT or VARCHAR columns to restrict input length. For instance, CHECK (LENGTH(my_column) <= 100) ensures the maximum number of characters allowed is 100.
Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

PostgreSQL Upsert: INSERT ON CONFLICT Guide

author Antonello Zanini tags POSTGRESQL UPSERT 7 min 2024-03-25
title

Schemas in PostgreSQL

author Leslie S. Gyamfi tags POSTGRESQL SCHEMA 6 min 2024-03-11
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

A Complete Guide to pg_dump With Examples, Tips, and Tricks

author Antonello Zanini tags pg_dump POSTGRESQL 8 min 2024-01-25

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗