PostgreSQL offers various data types to deal with textual data such as CHARACTER/CHAR, TEXT, and VARCHAR. The VARCHAR(n) data type stores the character type data in a limited range. The maximum range of VARCHAR is 65,535 bytes and a string that exceeds this range can’t be stored in the VARCHAR data type.
With the help of examples, this post will explain how to use the VARCHAR data type in PostgreSQL.
What is VARCHAR and How to Use it in PostgreSQL?
VARCHAR(n) or CHARACTER VARYING(n) is a data type in PostgreSQL that stores textual data. It can store ‘n’ number of characters. The syntax of the VARCHAR data type will be as follows:
Where n represents the size of the string(number of characters to be stored). ‘n’ must be a positive integer. If you didn’t specify the size (n) in the VARCHAR data type then it will act as the TEXT data type.
Note:If you try to insert a string that exceeds the specified length/size then PostgreSQL will throw an error.
Now let’s have a look at the examples given below to get a profound understanding of the VARCHAR data type.
Example #1: Create a Table With VARCHAR data Type
Let’s create a table named employee_profile using the CREATE TABLE command.
CREATE TABLE employee_profile( emp_name VARCHAR(30) NOT NULL, emp_designation VARCHAR(50) NOT NULL, emp_address VARCHAR(100) NOT NULL );
We created a table named employee_profile that consists of three columns: emp_name, emp_address, emp_designation. All three columns are of VARCHAR data type:
- The emp_name column can store thirty characters, emp_designation can store fifty characters, while the emp_address column can store hundred characters:
The table “employee_profile” has been created. Let’s verify the table formation using the SELECT statement:
SELECT * FROM employee_profile;
The output verifies that the employee_profile table contains three columns of VARCHAR data type.
Example #2: Insert VARCHAR Data Into employee_profile Table
Let’s insert some data to the employee_profile table using the INSERT INTO command:
INSERT INTO employee_profile(emp_name, emp_designation, emp_address) VALUES ('Ambrose', 'Web Designer', 'email@example.com'), ('Paul', 'Web Designer', 'firstname.lastname@example.org'), ('Seth', 'Web Developer', 'email@example.com'), ('MIKE', 'Web Developer', 'firstname.lastname@example.org'), ('Stephanie', 'HR Manager', 'email@example.com');
Five records have been inserted into the employee_profile table. Let’s check the newly inserted table’s data using the SELECT query:
SELECT * FROM employee_profile;
This is how the VARCHAR data type works in PostgreSQL.
Example #3: Value Too Long Error
If you try to insert a string that is greater than the specified length then you will face an error:
INSERT INTO employee_profile(emp_name, emp_designation, emp_address) VALUES ('Natalia', 'Human Resources Information Systems (HRIS) Professional', 'firstname.lastname@example.org');
The range for the emp_designation column is 50 characters; however, in the above query, we tried to insert a value that is greater than the specified range. Consequently, we will encounter the following error:
The output proved that you can’t insert a value that exceeds the specified range.
That was all the necessary information regarding the Postgres VARCHAR data type.
VARCHAR(n) or CHARACTER VARYING(n) is a data type in PostgreSQL that stores textual data in a limited range. It can store n number of characters. A string that exceeds the specified range cannot be stored in the VARCHAR data type. This write-up explained what VARCHAR data type is and how to use it in PostgreSQL with the help of examples.