CHARACTER VARYING vs VARCHAR in PostgreSQL

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

Is CHARACTER VARYING and VARCHAR Same?

Yes, CHARACTER VARYING and VARCHAR are the same data type in PostgreSQL.
VARCHAR is just an alias for CHARACTER VARYING, used interchangeably to define a variable–length character string column.
Both data types store variable-length character strings with a specified maximum length.
Using either CHARACTER VARYING or VARCHAR in table definitions has the same effect.

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:

column_a VARCHAR(10)

It shows that ‘column_a‘ can only store a maximum of 10 characters. Exceeding the maximum number of characters will result us 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 of 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:

Table – geeksforgeeks

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:

Exceeding length error

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

Table courses

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:

changing data type to character varying

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.

Now the question arises which is best 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

Overall, CHARACTER VARYING and VARCHAR are indeed the same in PostgreSQL, with VARCHAR being an alias for CHARACTER VARYING. Both data types are used to store variable-length character strings with a specified maximum length. They are ideal for storing text data that varies in length, like names or descriptions, without wasting storage space. Understanding these data types can help developers make informed decisions when designing database schemas in PostgreSQL

Article Tags :

Databases

PostgreSQL