PostgreSQL Varchar

In this section, we are going to understand the working of PostgreSQL varchar data types, which allows us to store the character of unlimited length. And we also see examples of the Varchar data type and spaces.

What is PostgreSQL Varchar datatype?

In PostgreSQL, the Varchar data type is used to keep the character of infinite length. And it can hold a string with a maximum length of 65,535 bytes.

In other words, we can say that the PostgreSQL Varchar data type uses the character data type, which is signified as VARCHAR.

• Mostly we should use the Varchar and Text datatypes. If we want PostgreSQL to check for the length, then we use the VARCHAR(n).
• In PostgreSQL, the varchar illustration as Varchar (n), where n is used to signify the limit of the character's length. If n is not described, it defaults to Varchar that has infinite length.
• If we try to get a longer string in the column specified with Varchar(n), the PostgreSQL occurs an error in the output.
• Still, we have an exception if the additional characters are all spaces, then the PostgreSQL will trim the spaces to the maximum length (n) and store the string.
• PostgreSQL will check and raise an error if we define the length specifier for the Varchar data type or if we try to insert more than n characters into the Varchar(n) column.

Note: A varchar data type is a variable-length string that can carry special characters, numbers, and letters. And n store text whose length can have a maximum of 0 to 65535 characters.

Syntax of PostgreSQL Varchar data type

The syntax of the PostgreSQL Varchar data type is as follows:

Examples of PostgreSQL Varchar data type

Let us see different examples to understand how the PostgreSQL Varchar data type works.

For this, we will create one new table name Varchar_demo table with the help of the CREATE command and inserting some values by using the INSERT command.

We are going to create Varchar_demo tables into the Organization database by using the CREATE command:

Output

After successful execution of the above command, the Varchar_demo table has been created, as shown in the below image:

Once the Varchar_demo table has been generated, we will insert some values into it using the INSERT command.

Output

After implementing the above command, we will get the following output, PostgreSQL will issue an error that "the value is too long for type character varying (1)".

That means the data type of the P column is VARCHAR (1), and where we are trying to insert a string with three characters into a particular column as we can observe in the below screenshot:

To resolve the above error, we will use the below command as follows:

Output

On executing the above command, the PostgreSQL reissues a similar error for the Q column, which means the Q column's data type is VARCHAR (10). In contrast, the number of characters entered is more than 10, as we can observe in the below screenshot:

Hence, to resolve both the above errors, we will use the below command:

Output

After executing the above command, we will get the following result: the value has been inserted successfully into the Varchar_demo table.

As we can see in the above screenshot, we successfully inserted the P and Q column's values.

After creating and inserting the Varchar_demo table's values, we will use the SELECT command to return all rows of the Varchar_demo table:

Output

After implementing the above command, we will get the following result where we can see that both the values of Column P and Column Q have been entered successfully into the Varchar_demo table.

Example 2

Let us see one more example to learn the Varchar data type in detail. We are creating one new table as Varchar_demo2 with the CREATE command's help and inserting some values by using the INSERT command.

We are going to create Varchar_demo2 into a similar database as above that is Organization by using the CREATE command:

Output

The Varchar_demo2 table has been successfully created after executing the above command, as shown in the below screenshot:

Once the Varchar_demo2 table has been generated, we can insert some values into it using the INSERT command.

Output

After implementing the above command. PostgreSQL gives the error message "the value is too long for type Character (1)". The output explains it more clearly.

Example of PostgreSQL VARCHAR and Spaces

PostgreSQL does not extend the space for varchar values when stored, and PostgreSQL also takes the trailing spaces when they stored or recovered Varchar values.

For this, we will see the below example to understand it wherein the Insert command, and we add values in the Name column in the Varchar_demo2 table, which we created in the above example:

Output

After executing the above command, we will get the following result: the value has been inserted successfully into the Varchar_demo2 table.

After creating and inserting the Varchar_demo2 table's values, we will use the SELECT command to retrieve the values from the Varchar_demo2 table:

Output

After implementing the above command, we will get the following result where PostgreSQL contains the trailing space in the counting of length since it does not increase the column length.

If we try to insert a Varchar value with trailing spaces that surpassed the column length, and the PostgreSQL will truncate the trailing spaces. Also, PostgreSQL raises a warning. As we have shown in the below example:

Output

On executing the above command, we will get the below result:

In the above command, the inserted value length is six into the Name column. And the value is still inserted into the column. However, the PostgreSQL cuts the trailing space before adding the value.

We can check it with the following command's help, where the Insert command is added successfully.

Output

After implementing the above command, we will get the below output still with a warning that is: Data truncated for column 'Name' at row 1:

Difference between PostgreSQL CHAR and PostgreSQL VARCHAR Data Type

The PostgreSQL CHAR and VARCHAR data types are both follow ASCII character. They are practically similar but different in storing and getting the data from the database.

The following table highlights the essential differences between CHAR and VARCHAR data type:

Overview

In the PostgreSQL Varchar data type section, we have learned the following topics:

• The Varchar datatype uses for varying length character types.
• If we want to check the length of the string (n) before inserting or updating it into the column, we can use the Varchar(n) data type.
• We use the PostgreSQL Varchar data type and spaces.