PostgreSQL Datatypes

PostgreSQL Datatypes

To store data in the PostgreSQL database, we need to have adequate knowledge of data types that are supported by the PostgreSQL. So, for someone to efficiently manage the data stored in the database, it’s very important to understand the data types supported by that particular system for storage of the data.

Data type specifies the type of data that is going to be stored in that column for a particular tuple. PostgreSQL supports an extensive range of data types to store data of different types in the database.

Some of the significant data types supported by PostgreSQL are numeric, character, text, varchar, integer, boolean, date, time, etc.

PostgreSQL Numeric

The numeric data type is used to store the numerical values in the different tables in the PostgreSQL, there are different types of numeric data types supported by PostgreSQL like:

  • smallint having size 2 bytes.
  • integer having size 4 bytes.
  • bigint having size 8 bytes.
  • real having size 4 bytes.

All these types have different sizes and different storage ranges.

Now let us see how we can use a particular numeric datatype for storing the data in a table. For the sake of example, we’ll take an integer as our numeric data type.

Let us create a demo table with a column having its datatype as an integer.

PostgreSQL Datatypes

We have created a table named demo having one column named age having data types as integer. Now we will add some numeric values in the table and see whether it gets stored or not.

PostgreSQL Datatypes

As we can observe in the output, the numeric values get successfully added to the demo table.

As we know we can store integer data using the integer data type, so if we try to store any other data in the column whose data type is specified as an integer it will give us an error.

PostgreSQL Datatypes

The error is displayed in the image where we tried to store the string or the text data in a column whose data type is an integer.

PostgreSQL Character

For storing the characters in our PostgreSQL database, we need to take help of the character data type. Character data type helps us to store characters up to a specified length in our column.

For a better understanding of the character data type, let us take an example and create a table with a column having a character as a datatype.

PostgreSQL Datatypes

So, we have created a table named person and there’s a column named blood_group having a character as data type having length 1 that means the character that we are going to store shouldn’t exceed that length. So now we will add data into the person table.

PostgreSQL Datatypes

As we can see in the image, the character ‘A’ gets successfully added into the blood_group column.

PostgreSQL Datatypes

But if we try to add the number of characters greater than the limit specified while creating the schema of the table, we get an error.

As shown in the image, we tried to add two characters ‘AB’ in the blood_group column having the size of 1 character, this is the reason for the error.

PostgreSQL Text

In the character data type, we need to specify the length of the characters to be stored but while using text data type, we can store an infinite length of characters. We can store a maximum length of 65,535 bytes of text in this data type.

Let us create a table to understand the usage of the text datatype.

PostgreSQL Datatypes

So we have created a person table having a person_name column and its data type is text. Now let us add data to this table.

PostgreSQL Datatypes

We have entered “Alex” as a text into the person table and it gets stored successfully also.

Now if we try to add another text having length other than the previously added text, we can easily do it, but the same isn’t allowed in the character data type. That’s the major difference between the character and the text data type. It is shown in the image below.

PostgreSQL Datatypes

PostgreSQL Timestamp

The timestamp data type is used to store the timestamp in a database. There are two types of timestamp data types:

  • timestamp: without time zone
  • timestamp: with time zone

Let us take an example to understand the timestamp datatype much better.

PostgreSQL Datatypes

We have created a table with a column having timestamp as a data type. Let us add some values to see how it works.

PostgreSQL Datatypes

The two timestamps are successfully added to the table. We have to take care of the format in which we have to enter the timestamp.

So, with the help of this article, we got a brief idea about some of the major data types of PostgreSQL.