In this section, we are going to understand the working of PostgreSQL Character data types, which allows us to select the right character types for our tables. And we also see examples of the character data type.
What is PostgreSQL Character Data Type?
In PostgreSQL, the character data represent the character type values and also known as CHAR. In other words, we can say that the PostgreSQL character data type is used to store a character of limited length.
We have the following table, which contains all Character data types that are supported by PostgreSQL:
In the above table, both CHAR(n) and VARCHAR(n) can store up to n characters where n signifies the limit of the length of the characters. If n is not defined, it defaults to char (1) or character (1).
If we try to get a longer string in the column, which is specified with char(n), the PostgreSQL shows an error in the output.
Still, we have exception if the additional characters are all spaces, then the PostgreSQL will trim the spaces to the maximum length (n) and store the string.
The PostgreSQL will shorten the string to n characters before inserting it into the table if a string cast to a CHAR(n) or VARCHAR(n).
Syntax of PostgreSQL Character data type
The syntax of the PostgreSQL Character data type is as follows:
Examples of PostgreSQL Character data type
Let us see different examples to understand how the PostgreSQL Character data type works.
For this, we will create one new table name Char_demo table with the help of the CREATE command and insert some values by using the INSERT command.
We are going to create Char_demo tables by using the CREATE command into the Organization database:
The Char_demo table has been successfully created after executing the above commands, as shown in the below screenshot:
Once the Char_demo table has been generated, we can insert some values into it by using the INSERT command.
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 (1)".
That means the data type of the A column is char (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:
On executing the above command, we will get the following output, and again PostgreSQL will issue an error that "the value is too long for type Character (10)".
That means the data type of the B column is char (10), and whereas the number of characters entered is greater than 10, as we can observe in the below screenshot:
Hence, to resolve both the above errors, we will use the below command:
After executing the above command, we will get the following result, which displays that the value has been inserted successfully into the Char_demo table.
As we can see in the above screenshot, we can insert the values to the character data type successfully for both the columns A and B.
After creating and inserting the Char_demo table's values, we will use the SELECT command that returns all rows of the Char_demo table:
After successfully implementing the above command, we will get the following result where we can see that the both the values of Column A and Column B have been entered successfully.
In the PostgreSQL Character data type section, we have learned that the character data type can be used for the fixed-length character.