PostgreSQL Character

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:

DatatypeExplanation
char(n)Here n represents the number of characters to store fixed-length strings. Space padded on the right is equal to the size of characters.
character(n)Here n is the number of characters to store fixed-length strings. Space padded on the right is equal to the size of characters.
varchar(n)Here n is the number of characters to store. Variable-length string.
character varying(n)Here n is the number of characters to store. Variable-length string.
textVariable-length string.

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:

Output

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

PostgreSQL Character

Once the Char_demo table has been generated, we can insert some values into it by 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 (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:

PostgreSQL Character

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

Output

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:

PostgreSQL Character

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, which displays that the value has been inserted successfully into the Char_demo table.

PostgreSQL Character

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:

Output

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.

PostgreSQL Character

Overview

In the PostgreSQL Character data type section, we have learned that the character data type can be used for the fixed-length character.


Next TopicPostgreSQL Text




Latest Courses