PostgreSQL Character Data Types

The character data types are used in the PostgreSQL database to store the different lengths of string data in the database tables. Three types of character data types are supported by PostgreSQL data types: char, varchar, and text. These data types are used for storing different types of string data. The purposes of using these data types have been explained below.

CHAR(n) or CHARACTER(n):

This data type is used to store the fixed length of string data. The length value will be provided at the time of table creation. The field of the table that contains char data will fill up the unused space with space characters. So, this data type is better to use for storing data of fixed length such as id of fixed length, phone number of a particular format, etc. The error will appear if the user tries to store string data larger than the length of the field of char data.

VARCHAR(n) or CHARACTER VARYING(n):

This data type is used to store the variable length of string data. The length will be provided at the time of table creation like the char data. The unused space of the field that contains this data type will remain unused and takes the space occupied by the string data only. The error will appear if the user tries to store string data larger than the length of the field of varchar data.

TEXT or VARCHAR:

This data type is used to store string data of unlimited length. So, the length value is not defined at the time of table creation. This is not SQL standard data type and it is removed from some relational database management systems (RDBMS) such as Oracle, MS SQL Server, etc.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-get -y install postgresql postgresql-contrib
$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root permission:

$ sudo -u postgres psql

Use of character data types in PostgreSQL:

Before creating any table with the Boolean data type you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

# CREATE DATABASE testdb;

The following output will appear after creating the database:

Create a table:

Run the following SQL statement to create a table named clients in a PostgreSQL database by using different types of character data types. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The name of the second field is name and the data type of this field is varchar which will be able to store a maximum of 15 characters. The name of the third field is address and the data type of this field is text. The name of the fourth field is email and the data type of this field is character varying that will be able to store maximum of 30 characters. The fifth field is phone and the data type is char which will store the string data of 14 characters.

# CREATE TABLE clients (
id serial PRIMARY KEY,
name varchar (15) NOT NULL,
address text NOT NULL,
email character varying (30),
phone char (14) NOT NULL );

The following output will appear if the table is created successfully:

Insert data into the table:

Run the following insert query with the valid data to insert a record into the clients table:

# INSERT INTO clients (name, address, email, phone)
VALUES ('AR Company', '12/A, Dhanmondi, Dhaka.', '[email protected]', '+8801826783423');

The following output will appear if the insert query is executed successfully:

Run the following insert query to insert a record into the clients table. Here the value of the name field exceeds the size of the field. So, an error message will appear.

# INSERT INTO clients (name, address, email, phone)
VALUES ('RFL Company Ltd.', '23, Motijeel, Dhaka.', '[email protected]', '+8801927891278');

The following output will appear after executing the above insert query. Here, the name field can store a maximum of 15 characters but 16 characters are given in the query.

Run the following insert query to insert a record into the clients table. Here the value of the phone field exceeds the size of the field. So, an error message will appear.

# INSERT INTO clients (name, address, email, phone)
VALUES ('Vision', '190/1, Mirpur, Dhaka.', '[email protected]', '+880152678342312');

The following output will appear after executing the above insert query. Here, the phone field can store 14 characters but 16 characters are given in the query.

Read data from the table:

Only one insert query has been executed successfully after executing the above three insert queries. Now, run the following select query to retrieve all records from the clients table:

# SELECT * FROM clients;

The following output will appear after executing the above query:

Conclusion:

The character data type is a very useful data type of the PostgreSQL table. Some fields of the table require to store fixed-length of string data and some fields of the table require the variable length of data. The uses of three different data types of PostgreSQL tables have been explained in this tutorial by creating a table. The new PostgreSQL users will get proper knowledge about the character data types and will be able to use them properly for creating tables after reading this tutorial.