PostgreSQL TEXT VS VARCHAR Data Types

PostgreSQL provides various character data types to store character data, such as TEXT, VARCHAR, etc. But the question is, why is it so? If we have a TEXT data type, then what is the need for the VARCHAR data type, and vice versa? If you are stuck in the same debate, then nothing to worry about. This write-up is going to resolve all your queries regarding TEXT vs. VARCHAR.

This write-up will consider several examples to demonstrate the difference between TEXT and VARCHAR data types. So, let’s start!

PostgreSQL TEXT VS VARCHAR Data Types

The below-listed points will assist the users in understanding the difference between TEXT and VARCHAR data types:

  • In PostgreSQL, TEXT and VARCHAR data types store the character data. As per PostgreSQL's official documentation, both TEXT and VARCHAR data types are the same regarding performance parameters.
  • However, the major difference between these data types is the character’s “limit”. For instance, using the VARCHAR data type, you can specify the limit/length of the VARCHAR column.
  • For example, VARCHAR(100) will store only a hundred characters. A column created with the VARCHAR(100) will throw an error if you try to enter more than a hundred characters. While using the TEXT data type, you can not specify the maximum limit/length; instead, it is used to create the strings/sequence of unlimited length.
  • Another notable difference between these two data types is padding and spaces. While working with the VARCHAR data type, the padding and spaces get truncated during execution. On the other hand, if you are working with the TEXT data type, then padding and spaces wouldn’t be truncated even during the execution/storing.

Let’s understand the difference between TEXT and VARCHAR data types via practical examples.

Example 1: Understanding TEXT and VARCHAR Data Types?

Let’s create a table named “example_greetings” with two columns: “var_txt” and “var_varchar”:

CREATE TABLE example_greetings(
var_txt TEXT,
var_varchar VARCHAR(10)
);
img

The table named “example_greetings” has been created successfully.

Example 2: How Do I Insert TEXT and VARCHAR Data Into a Table in Postgres?

Let’s insert the following data into the example_greetings table via the INSERT INTO command:

INSERT INTO example_greetings(var_text, var_varchar)
VALUES
('Welcome to commmandprompt.com',
'The Best Platform For Educational Tutorials'
);
img

The “var_varchar” column exceeded the character limit; therefore, Postgres throws an error. Let’s execute the INSERT INTO command one more time to see how it works if the user enters a value within the specified limit/length:

INSERT INTO example_greetings(var_txt, var_varchar)
VALUES
('Welcome to commmandprompt.com',
'PostgreSQL'
);
img

This time, one record has been successfully inserted into the example_greetings table. You can verify the inserted data via the SELECT query:

SELECT * FROM example_greetings;
img

If you didn’t specify the length parameter “n” within the VARCHAR data type, then the VARCHAR data type can accept the unlimited number of characters(same as the TEXT data type).

That’s it from this Postgres blog.

Conclusion

In PostgreSQL, TEXT and VARCHAR data types store the character data. As per PostgreSQL's official documentation, both TEXT and VARCHAR data types are the same regarding performance parameters. However, the major difference between these data types is the character’s “limit”. For instance, using the VARCHAR data type, you can specify the limit/length of the VARCHAR column. While the TEXT data type doesn’t accept any length parameter, it can store unlimited characters. This blog post presented a comparative analysis of TEXT and VARCHAR data types through practical examples.