PostgreSQL

Overview of PostgreSQL Data Types

While working with any database or a database management system, it is considered very important to understand all the different data types that it supports. It is done to ensure that you deal with your data efficiently while working with that database or DBMS. Therefore, in this article, we will be providing you with a brief overview of the different data types of PostgreSQL followed by an example that makes use of some of these data types.

Note: Here, we are only going to discuss the most commonly used PostgreSQL data types.

An Overview of the PostgreSQL Data Types

Although PostgreSQL supports a large number of different data types, however, some of the most commonly used PostgreSQL data types along with their sub-types are discussed below:

Numeric
This particular PostgreSQL data type is utilized to store the numeric data. This type is further divided into the data types such as integer, smallint, bigint, decimal, real, serial, double precision, etc. All of these data types correspond to the different kinds of numeric data depending upon its size and the nature of the value that it can hold.

Textual
This PostgreSQL data type is utilized to store the textual data. This type is further divided into data types such as character, varchar, text, etc. Again, these data types differ depending upon the size of the data that they can hold.

Boolean
The Boolean data type corresponds to the data values that can either be true or false or “0” or “1”. This data type is mainly used to store the values of the different states of a switch or the states of a flag.

Temporal
The temporal type is related to the date and time. This includes the data types such as date, time, timestamp, interval, etc.

Monetary
This type of PostgreSQL is particularly used to store currency data. The specific data type that is used to serve this purpose in PostgreSQL is money.

JSON
As the name says, this particular PostgreSQL data type is utilized to store the JSON data.

XML
This PostgreSQL data type is utilized to store the XML data.

Array
The array data type in PostgreSQL is used to define a column of a table that can act as a multi-dimensional array of any desired data type.

UUID
This particular PostgreSQL data type is utilized to keep the collectively exclusive identifiers which are a set of 32 digits or characters each.

Special
This particular type is used to store special kinds of data. This is further divided into data types; the first one is the network address and the second one is geometric data. The network address data type contains the network addresses i.e. IPv4 and IPv6 addresses, MAC addresses, etc. whereas the geometric data refers to the data types such as point, line, box, circle, polygon, path, etc.

Example of using the Different PostgreSQL Data Types

In this example, we will be using some of the different PostgreSQL data types by creating a PostgreSQL table.

Step 1: Checking whether the PostgreSQL Server is Active or not
First, we want to check whether the PostgreSQL server is active on our system or not. For that, we will execute the subsequent command in our system’s terminal:

$ sudo systemctl status PostgreSQL

The PostgreSQL server is active on our system as you can see from the image shown below:

Step 2: Logging into the PostgreSQL Server
After confirming the active status of the PostgreSQL server, we will log into our server with the following command:

$ sudo –i –u postgres

In this command, “postgres” refers to the default PostgreSQL user. Doing this will immediately log us into the PostgreSQL server.

Step 3: Entering the PostgreSQL Console
Now, we need to access the PostgreSQL console by running the instruction presented below:

$ psql

This command will take us instantly to the PostgreSQL console where we can easily execute all the desired queries.

Step 4: Creating a New PostgreSQL Table
After entering the PostgreSQL console, we will create a new table that holds fields having some of the PostgreSQL data types. We will execute the subsequent query to do so:

# CREATE TABLE DATATYPE (number serial PRIMARY KEY, name VARCHAR (255) NOT NULL, flag BOOLEAN NOT NULL);

The response message shown below will indicate a successful table creation.

Step 5: Inserting Values into the PostgreSQL Table
Now, we will add some desired values into the PostgreSQL table by running the following query:

# INSERT INTO DATATYPE VALUES (1, ‘John’, ‘1);

The response message shown below will indicate a successful record insertion.

In the same manner, we will insert another record as follows:

# INSERT INTO DATATYPE VALUES (2, ‘Harry’, ‘0);

The response message shown below will indicate a successful record insertion.

Step 6: Displaying the Inserted Values in the PostgreSQL Table
Finally, we will simply display all the inserted records on the PostgreSQL console to view the various data types that we have used. For that, we will execute the subsequent query:

# select * from DATATYPE;

The records belonging to the different PostgreSQL data types are shown in the image below:

Additional Tip

After performing all the desired queries on the PostgreSQL console, you can exit from the console by executing the following query:

# \q

Moreover, for logging out of the PostgreSQL server after exiting from the console, you can run the command shown below:

# exit

This command will take you back to your regular system’s terminal.

Conclusion

This article provided a summary of the most frequently used PostgreSQL data types. After stating a short description of each of these data types along with their sub-types, we shared a simple example with you that makes use of a few of these discussed PostgreSQL data types so that you can understand how you can use these data types while working with this DBMS. This article was just an introductory guide on the most commonly used PostgreSQL data types, however, you can explore the other PostgreSQL data types on your own.

About the author

Saeed Raza

Hello geeks! I am here to guide you about your tech-related issues. My expertise revolves around Linux, Databases & Programming. Additionally, I am practicing law in Pakistan. Cheers to all of you.