PostgreSQL offers a wide range of types to deal with various types of data, such as INTEGER, TEXT, BOOLEAN, etc. Postgres offers three main data types to handle textual data, i.e., CHAR, TEXT, and VARCHAR.

This post will explain all these data types through practical examples. So, let’s start.

Postgres CHAR Data Type

In PostgreSQL, the CHAR data type is used to store fixed-length characters. The below snippet will assist you in this regard:

var_name CHAR(n);

In the above snippet, “n” determines the length of the CHAR() type. If you try to store characters more than the specified length “n”, then you will encounter an error. Let’s understand it via practical examples.

Example 1: Creating Columns With CHAR Data Type in Postgres

Let’s create a “cust_info” table with two columns: cust_name and cust_gender. Both columns are of a character data type but with different lengths:

CREATE TABLE cust_info(
cust_name CHAR(10),
cust_gender CHAR(1)
);

The output shows that the “cust_info” table has been created successfully. You can verify the table creation via the following command:

SELECT * FROM cust_info;

The output shows that two columns have been created with the character data type.

Example 2: How do I Insert CHAR Data Into a PostgreSQL Table?

To insert character data into the cust_info table, we will use the INSERT query as follows:

INSERT INTO cust_info(cust_name, cust_gender)
VALUES ('Joe', 'M'),
('Alexa', 'F');

The output proves that two records have been inserted into the cust_info table successfully. You can verify the newly inserted data via the “SELECT” command:

SELECT * FROM cust_info;

Output authenticates that the data has been inserted into the “cust_info” table successfully. Let’s insert one more record to get more clarity about the CHAR data type:

INSERT INTO cust_info(cust_name, cust_gender)
VALUES ('John', 'Male');

Output proves that an error occurred when we tried to input a value more than the specified length.

Postgres VARCHAR Data Type

Postgres offers another character type named VARCHAR or CHARACTER VARYING. It has a couple of implementations, as shown in the following snippets:

CHARACTER VARYING(n) | VARCHAR(n);

Here, “n” represents the length of the string/sequence. If you utilize one of the notations mentioned above, then the VARCHAR data type will store a variable-length string/sequence within a specified length.

VARCHAR;

The above syntax allows us to store the variable length strings with an unlimited length.

Example 1: Creating Columns With VARCHAR Data Type in Postgres

Let’s create a “std_info” table with three columns: f_name, l_name, and std_email. All columns are of a character-varying data type but with different lengths:

CREATE TABLE std_info(
f_name VARCHAR(10),
l_name CHARACTER VARYING(10),
std_email VARCHAR
);

The desired table has been created successfully. Run the below command to get the table details:

SELECT * FROM std_info;

The output shows that three columns with the specified data type and length have been created successfully. You can insert only ten characters in the “f_name” and “l_name” columns; however, in the “std_email” column, you can insert as many characters as you want.

Example 2: How do I Insert VARCHAR Data Into a PostgreSQL Table?

Let’s insert some specific records into the std_info table via the INSERT INTO command:

INSERT INTO std_info(f_name, l_name, std_email)
VALUES('Joe', 'Denly', 'joe@xyz.com'),
('Tim', 'Root', 'tim@xyz.com'),
('Mike', 'Ambrose', 'mike@xyz.com');

The above snippet proves that three records have been successfully inserted into the targeted table. Let’s verify the newly inserted data via the SELECT query:

SELECT * FROM std_info;

The output authenticates that the three records have been inserted into the std_info table.

Postgres TEXT Data Type

The TEXT data type is one of the character data types in PostgreSQL that is used to store an unlimited number of characters. It is used to create variable-length strings. In PostgreSQL, the TEXT data type and the VARCHAR data type without any argument are equivalent.

var_name TEXT;

Let’s understand the working of TEXT data type via practical examples.

Example 1: Creating a Column With TEXT Data Type in Postgres?

Suppose we want to create a table named emp_data with two columns: emp_name and emp_email. To do this, we will utilize the CREATE TABLE command as follows:

CREATE TABLE emp_data(
emp_name TEXT,
emp_email TEXT
);

The “CREATE TABLE” message in the output window shows that the desired table has been created successfully. The command mentioned below will show the table’s structure:

SELECT * FROM emp_data;

The emp_data table having two TEXT-type columns has been created successfully.

Example 2: How do I Insert TEXT Data Into a PostgreSQL Table?

To insert data into the emp_data table, we will use the INSERT query as follows:

INSERT INTO emp_data(emp_name, emp_email)
VALUES ('Tim', 'tim@xyz.com'),
('Mike', 'mike@xyz.com');

The output snippet indicates that two records have been inserted into the targeted table. Let’s execute the SELECT query one more time to display the newly inserted table’s data:

SELECT * FROM emp_data;

The output shows that all the records have been inserted into the emp_data table successfully.

That’s all from this Postgres blog post.

Conclusion

PostgreSQL provides several data types to work with the character/textual data, such as CHAR, TEXT, and VARCHAR. All these data types are used to store the character data; however, these data types differ in length. For instance, CHAR(n) and VARCHAR(n) store the characters based on the specified length, i.e., “n”. While TEXT and VARCHAR are used to store unlimited characters. This blog post explained the character types through practical examples.