PostgreSQL – VARCHAR Data Type | A 101 Guide

PostgreSQL is one of the most robust Relational Database Management Systems. You can use SQL to create tables and store data in your databases.

The data you would want to enter in your table would be of different types, some might be numbers, alphabet, special characters, etc. Hence, there are different data types that would act as guidelines for the different kinds of data to be entered in the fields.

Character types are the data types that are applicable to the fields that can hold character data. The different character types are PostgreSQL Varchar, Char, and Text data types.

In this article, you will come to know about the different character types supported by PostgreSQL. You will also come along with examples of usage of Varchar in PostgreSQL, Char and Text data types.

What are Character Types?

A data type specifies the guidelines regarding the type of data that can be included in a table column or variable. It is a necessary and crucial phase in the design of a table. Character types are data types that can be allocated to fields that will hold character and string values.

A table with the wrong data types can cause problems such as poor query optimization, performance concerns, and data truncation.

What are the Character Types in PostgreSQL?

The different character types supported by PostgreSQL are as follows:

• Character(n)
• Char(n)
• Character Varying(n)
• Varchar(n)
• Text

Out of the 5 character types, these can primarily be categorized into 3 types based on their similarities.

PostgreSQL Character Types: Overview

For having in-depth information about the three primary character types in PostgreSQL, you can move forward.

• PostgreSQL Char Data Type
• PostgreSQL Varchar Data Type
• PostgreSQL Text Data Type

1) PostgreSQL Char Data Type

Character data, often known as CHAR in PostgreSQL, represent the character type values. In other words, the PostgreSQL character data type is used when you want the variable to store a character of limited length. This length value can be specified as a parameter of the Char or Character type.

Char datatype is a fixed-length data type i.e., so if there is any remaining space then it is padded with blanks.

The following character data types can be used interchangeably.

• char(n)
• character(n)

A) Syntax

variable_name CHAR(n)  

Or

variable_name CHARACTER(n)

Here, variable_name is the name of the variable which should be of char datatype. And n represents the maximum length of a string which can be used as the variable_name.

B) Example

You have to assign the data type to a variable while creating a table. Hence, the datatype is used along with the CREATE statement.

CREATE TABLE Character_type (  
    Id serial PRIMARY KEY,  
    X CHAR (1),  
    Y CHAR (10)  
);  

Here, you are creating a Character_type table, in which the variables are Id, X, and Y. Here, X and Y are of character types. And the length of any string assigned to X and Y can be a maximum of 1 character and 10 characters.

Now, while inserting values into the table:

INSERT INTO Character_type (X,Y)  
VALUES   
('DPS', 'They provide the best education'),    

After executing the above command, you will get the following output mentioning, “the value is too long for type character varying (1)“. Since for both the variables, the character lengths exceed the 1 and 10 length limit.

To resolve the error you can enter the following command:

INSERT INTO Character_type (X,Y)  
VALUES   
('A', 'Josephines'), 

After executing the above INSERT statement, 1 row will be inserted into the table with the values entered. And both X and Y have values within the maximum limit of the parameters mentioned.

2) PostgreSQL Varchar Data Type

The PostgreSQL Varchar data type is used to store characters of indefinite length based on the parameter n. It can store a string up to 65,535 bytes long.

• In the PostgreSQL Varchar data type i. e. Varchar(n), n is used to denote the character length limit. If n is not specified, it defaults to a character of infinite length.
• PostgreSQL checks and throws an error if you try to get a longer string in a column specified by PostgreSQL Varchar(n).
• If the extra characters are assigned to a variable that is all whitespace in the PostgreSQL varchar data type, the whitespace is truncated to the maximum length (n) and the string is stored. This is the reason why the PostgreSQL Varchar data type is called the variable-length data type.

The PostgreSQL Varchar data types are:

• Varchar(n)
• Character Varying(n)

A) Syntax

variable_name VARCHAR(n)  

Or

variable_name VARYINGING(n)

Here, variable_name is the name of the variable which should be of char datatype. And n is the parameter of the PostgreSQL Varachar datatype which represents the length of a string that can be used as the variable_name.

B) Example

You have to assign the data type i.e, PostgreSQL Varchar to a variable while creating a table. Hence, the datatype is used along with the CREATE statement.

CREATE TABLE Varchar_type (  
    Id serial PRIMARY KEY,  
    P VARCHAR(3),  
    Q VARCHAR(13)  
);  

Here, you are creating a Varchar_type table, in which the variables are Id, X, and Y. Here, X and Y are of character types. And the length of any string assigned to X and Y can be a maximum of 3 and 10 characters.

Now, while inserting values into the table:

INSERT INTO Text_type (X,Y)  
VALUES   
('DEMS', 'They provide the best education'),    

After executing the above command, you will get the following output mentioning, “the value is too long for type character varying (1)“. Since for both the variables, the character lengths exceed the 3 and 13 length limit.

To resolve the error you can enter the following command:

INSERT INTO Varchar_type (X,Y)  
VALUES   
('DPS', 'Best Education'), 

After executing the above INSERT statement, 1 row will be inserted into the table with the values entered. And Since for the Y variable, you have sent the varchar type with a maximum length of 13, so in ‘Best Education‘, it will automatically adjust, remove the space and save it.

3) PostgreSQL Text Data Type

The PostgreSQL Text 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.

It is the same as the PostgreSQL Varchar data type, rather without any argument for mentioning the length of the character ie., the PostgreSQL Varchar Data Type without n is the same as the Text data type.

A) Syntax

variable_name TEXT 

Here, variable_name is the name of the variable which should be of TEXT datatype.

B) Example

You have to assign the data type to a variable while creating a table. Hence, the datatype is used along with the CREATE statement.

CREATE TABLE Text_type (  
    Id serial PRIMARY KEY,  
    X TEXT,  
    Y TEXT  
);   

Here, you are creating a Text_type table, in which the variables are Id, X, and Y. Here, X and Y are of TEXT types. And the length of any string assigned to X and Y can be anything.

Now, while inserting values into the table:

INSERT INTO Text_type (X,Y)  
VALUES   
('DPS', 'They provide the best education'),   
('DEMS', 'They have great scope for extra-curricular activities');  

After executing the above INSERT statement, 2 rows will be inserted into the table with the values entered.

Conclusion

This article illustrated the concept of different character types supported by PostgreSQL. You had an in-depth understanding of the PostgreSQL Varchar, Char, and Text datatypes.

Now, you can move forward and assign the different character types while creating tables.

Want to explore more about different clauses and statements while writing queries and creating tables in PostgreSQL? You can go through these articles.

• Working with PostgreSQL Subquery | A 101 Guide
• PostgreSQL SELECT Statement: Syntax & Example Queries | A 101 Guide

It will take a lot of time to import and move data into your selected warehouse by using ETL for Data Analysis with PostgreSQL as your data source. When you consider how much money and resources are required to engage data engineers and analysts to make sense of this data, the issue becomes even more daunting.

However, with Hevo at your fingertips, you won’t have to worry about your PostgreSQL Data Replication demands. Loading your data into your chosen data warehouse will just take a few minutes.

Hevo’s strong integration with 150+ data sources (including 60+ free sources), such as PostgreSQL, allows you to export data from your selected data sources and load it to the destination of your choice. It also assists you with reorganizing and enhancing your data so that it is ready for analysis. Sign up for Hevo’s 14-day free trial and experience seamless data migration.

FAQ on PostgreSQL VARCHAR