PostgreSQL VarcharIn 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.
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 typeThe syntax of the PostgreSQL Varchar data type is as follows: Examples of PostgreSQL Varchar data typeLet 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 2Let 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 SpacesPostgreSQL 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 TypeThe 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:
OverviewIn the PostgreSQL Varchar data type section, we have learned the following topics:
Next TopicPostgreSQL Boolean
|