Introduction to PostgreSQL VARCHAR
The PostgreSQL supports various data types, the VARCHAR data type is used to store the characters. We can specify the number with VARCHAR to decide the length of the characters. The number defined with the VARCHAR data type is a positive integer number. The PostgreSQL throws an error if the length of the text is greater than the length of VARCHAR data type defined. If we define the VARCHAR data type without the number as a limit then it will store the text with unlimited length, or the text string with any size. The VARCHAR with number defined to limit the number of characters like VARCHAR(n) is an acronym for the varying characters with length n.
Syntax:
Consider the following syntax in order to understand VARCHAR data type
VARCHAR(n)
Or
VARCHAR
Explanation:
n: This defines the length of the text or number of characters. It is a positive integer number. It defines the varying character with length n.
If the ‘n’ is not defined then the column for which we have defined the VARCHAR data type then it will store the text string with unlimited length, or the text string with any size.
How Does VARCHAR Data Type Work in PostgreSQL?
1. The number within the parentheses should be defined after the VARCHAR datatype if we want to limit the character size to the store.
2. If we define the number with VARCHAR data type then it will store the characters with the limit to the number.
3. If we define the VARCHAR data type without the number as a limit then it will store the text with unlimited length, or the text string with any size.
4. If the number is defined with VARCHAR data type then PostgreSQL will check the length of the characters and if it exceeds it will throw an exception.
5. If we try to insert a string with all spaces in the end the PostgreSQL will truncate the spaces and allow it to store.
Examples to Implement PostgreSQL VARCHAR
Below are the examples of PostgreSQL VARCHAR:
Generally, for using the data type for characters the VARCHAR is used, as it has the capability to store the values with variable length. Consider a table named TEXTS in order to understand the examples of the PostgreSQL VARCHAR data type.
Let’s create a table named TEXTS by using CREATE TABLE statement as follows,
Query:
CREATE table TEXTS
(
text_id serial PRIMARY KEY,
text VARCHAR (11) NOT NULL
);
Now insert some data in the TEXTS table by using the INSERT INTO statement as follows,
Query:
INSERT INTO TEXTS (text)
VALUES
('text 1'),
('Its text2'),
('t3');
Illustrate the content of the TEXTS table with the following SQL statement and snapshots.
Query:
select * from TEXTS;
Output:
In the above example, we can see that we have a text column with a VARCHAR data type. We have inserted the value of the text with different length values.
Now, we will try to insert the text with length greater than 11 in the text column of the TEXTS table as follows.
Query:
INSERT INTO TEXTS (text)
VALUES
('It is long text');
Output:
PostgreSQL throws an error if the length of the text is greater than the length of VARCHAR data type defined.
Now, we will try to insert the text with length greater than 11 and having spaces at the end in the text column of the TEXTS table as follows,
Query:
INSERT INTO TEXTS (text)
VALUES
('space ');
Illustrate the result of the above statement after execution by using the following SQL statement and a snapshot.
Query:
select * from TEXTS;
Output:
Now we will add a column with data type VARCHAR which is without n and will insert the values with different lengths.
Consider the following ALTER TABLE statement for adding a column with a VARCHAR data type.
ALTER TABLE TEXTS ADD COLUMN text_1 VARCHAR;
Illustrate the result of the above statement by using the following snapshot.
Now, we will insert the text string with variable lengths in the text_1 column by using INSERT INTO statement as follows,
Query:
INSERT INTO TEXTS (text,text_1)
VALUES
('t1','This is text_1 first'),
('t1','This is text_1 long text' ),
('t1','This is text_1 long long long text string');
select * from TEXTS;
Output:
In the above example, we can see that the column text_1 is having the text strings with the variable-length size or any size. It will almost accept the string with unlimited size. Whenever we want to store the free text data then we generally prefer to use the data type VARCHAR without the number defined which specifies the limit.
Conclusion
We hope from the above article you have understood the PostgreSQL VARCHAR data type. Also, we have explained the working of the VARCHAR data type., Also with the help of examples and syntax explained we hope you have understood the VARCHAR data type in detail.
Recommended Articles
This is a guide to PostgreSQL VARCHAR. Here we discuss the Introduction to PostgreSQL VARCHAR and its working along with practical examples and different subquery expressions. You can also go through our suggested articles to learn more –
