CHARACTER VARYING vs VARCHAR in PostgreSQL

In PostgreSQL, the terms CHARACTER VARYING and VARCHAR are often used interchangeably, but are they truly the same? We will understand these data types in this article to clarify their similarities and differences. We'll explore how they work, their syntax, and examples of their usage in PostgreSQL.

Are CHARACTER VARYING and VARCHAR the Same in PostgreSQL?

Yes, 'CHARACTER VARYING' and 'VARCHAR' are indeed the same data type in PostgreSQL. VARCHAR is simply a shorter alias for 'CHARACTER VARYING'. Both are used to define columns that store variable-length character strings, meaning the number of characters can vary up to a specified maximum length.

• Interchangeable Usage: You can use either 'CHARACTER VARYING' or 'VARCHAR' in your table definitions without any difference in functionality or performance.
• Maximum Length Specification: Both data types require you to specify a maximum length ('n'), which limits the number of characters that can be stored in the column.
• Memory Efficiency: PostgreSQL dynamically adjusts the storage space based on the actual length of the stored data, making both 'CHARACTER VARYING' and 'VARCHAR' efficient choices for variable-length strings.

Definition of PostgreSQL CHARACTER VARYING

PostgreSQL Character Varying, also known as VARCHAR, is a data type used to store variable-length character strings in a table column. The 'n' in VARCHAR(n) represents the maximum number of characters the column can store. Character Varying adjusts the storage space based on the actual length of the text, saving memory. It is commonly used for storing text data like names or descriptions where the length may vary.

For Example:

user_name VARCHAR(10)

Here, 'user_name' can store up to 10 characters. Any attempt to store a string longer than 10 characters will result in an error.

How does CHARACTER VARYING work in PostgreSQL?

PostgreSQL's CHARACTER VARYING or VARCHAR data type stores variable-length character strings. It has a maximum length limit of 'n' but adjusts storage based on the actual text length, saving memory. This data type is ideal for storing text data like names or descriptions that vary in length without wasting storage space

Syntax

column_name CHARACTER VARYING(n)
or 
column_name VARCHAR(n)

Where 'n' is the number of maximum characters that can be stored in a column.

Example of Define Character Varying Data Type at the Time of Table Creation

In this, we are going to create a table in our database. We will use VARCHAR in order to create our table.

Create Table:

CREATE TABLE geeksforgeeks(
    user_id INT PRIMARY KEY,
    user_name VARCHAR(10),
    score INT
);

Explanation : In the above query, we have created a table named 'geeksforgeeks' in our database. We have create it with 'user_id' , 'user_name' and 'score' as Columns. We have specified 'user_name' with data type as VARCHAR with 10 as the limit. You can also use CHARACTER VARYING if we want to be more expressive with your query. If the character length goes out of bound or exceeds the the given length ( i.e. 10), then this will us throw an error.

Example: Insert Value Into Character Varying Data Type Column

In this we are going to insert values in our table 'geeksforgeeks'. We will explore how we can add data to our column with a VARCHAR data type. We will also see the consequences of exceeding the maximum character limit of a column with VARCHAR data type.

Insert Values:

--inserting values
INSERT INTO geeksforgeeks (user_id, user_name, score)
VALUES (101, 'Vishu', 500);
INSERT INTO geeksforgeeks (user_id, user_name, score)
VALUES (102, 'Ayush', 525);
INSERT INTO geeksforgeeks (user_id, user_name, score)
VALUES (103, 'Neeraj', 450);
INSERT INTO geeksforgeeks (user_id, user_name, score)
VALUES (104, 'Sumit', 425);

--displaying the table data
SELECT * FROM geeksforgeeks

Output:

Explanation: In the above image, we can clearly see a table has been created with different fields. In the above table 'user_name' has been created with VARCHAR(10). Moving forward, we will see how exceeding this length will throw us an error.

Lets try to Insert A Value In Our Table With User Name Greater Than 10 Character In A Column With Varchar Data Type

In this we are going to add a value in our user name column with length greater than 10, which is our maximum length. Lets say, we are going to add a user name with length 17 which is grater than 10.

Query

INSERT INTO geeksforgeeks (user_id, user_name, score)
VALUES (105, 'Vivek kumar singh', 400);

Output:

Explanation: In the above image, we can clearly see that we are trying to add a user name ('Vivek kumar singh') with length 17 (including space). It is throwing us an error which say ' value too long for type character varying(10)'. As 17 is greater than 10, our table can not accept this value. This violate the maximum exceeding values, which is 10. Therefore, it is throwing us an error.

Example: Changing the Data Type of the Column as a Character Varying after Table Creation

In this, we will explore how we can add 'CHARACTER VARYING' data type to a column with another data type in a previously created table.

Lets create a table first.

CREATE TABLE courses(
    user_id INT,
    course char(10)
);

Output:

After executing the above query, we can clearly notice a new table named courses has been created in our database.

Changing the Data Types of Column 'course' from Char to Character Varying

ALTER TABLE courses ALTER COLUMN course TYPE CHARACTER VARYING(10);

Output:

After executing the above query, we can clearly see that data type of course column has been changed from char to CHARACTER VARYING.

PostgreSQL TEXT vs VARCHAR: Which Should We Use?

In PostgreSQL, TEXT and VARCHAR are data types which are used to store characters in a column. Both are used to store varying length of characters in column. But there is a slight difference in them, with TEXT you can store unlimited characters in a column whereas with VARCHAR(n) you can only store up to 'n' characters of in a column.

Which One to Use?

It depends on the particular use cases or the requirements of a specific application.

• If you are uncertain about the character data which is going to get stored in a column, then you should go with TEXT.
• If you are certain and want only characters with certain limit to get stored in a specified column ,then you should definitely go with VARACHAR(n).

Conclusion

In PostgreSQL, 'CHARACTER VARYING' and 'VARCHAR' are essentially the same, with 'VARCHAR' being an alias. They are both used to store variable-length strings efficiently, making them suitable for text data like names or descriptions. By understanding how these data types work, you can better design your database schemas and optimize performance.