Introduction
You may have heard the word “Data Type” many times while studying or coding.
A data type is an attribute associated with a piece of data that tells the system how to interpret this value. While storing some data, it is necessary to know its data type to ensure its proper use.
Source: Make a Meme.org
In PostgreSQL also, we need data types. We need to specify the data type of each column while creating a table. PostgreSQL supports a large variety of data types. These are-
We will discuss all these data types one by one. So, let’s get started:
Boolean Data Type
The boolean data type is used to store three kinds of values- true, false and null. We can use the boolean or bool keyword to declare a column with the boolean data type. The boolean data type takes a 1-byte space in memory.
When we insert some data in a boolean column, PostgreSQL converts-
- True, t, yes, y, 1 values to a true value.
- False, f, no, 0 values to a false value.
- Space to a null value.
When we select some data that belongs to a boolean column, PostgreSQL converts-
- The true value to true, t, yes, y, 1 value.
- The false value to false, f, no, 0 value.
- The null value to space.
Character Data Type
Character data types are generally used to store a string of characters in a table’s column. PostgreSQL provides three types of character data types- CHAR(n), VARCHAR(n) and TEXT.
-
CHAR(n): The CHAR(n) data type stores fixed length characters in a column in PostgreSQL.
- We can use the CHAR(n) data type by using the keyword CHAR(n), where n is the length of the required string.
- If the string to be inserted is shorter in length (shorter than n), then PostgreSQL adds spaces as the rest of the characters.
- And if the string to be inserted is longer in length (longer than n), PostgreSQL throws an error.
-
VARCHAR(n): The VARCHAR(n) data type stores variable-length characters in a column in PostgreSQL.
- We can use the VARCHAR(n) data type by using the keyword VARCHAR(n), where n is the length of the required string.
- The main benefit of using VARCHAR(n) over CHAR(n) is PostgreSQL does not add spaces if the string to be inserted is shorter in length (shorter than n).
- However, if the string to be inserted is longer in length (longer than n), PostgreSQL throws an error.
-
TEXT: The TEXT data type stores variable-length characters with unlimited length in a column in PostgreSQL.
- We can use the TEXT data type by using the keyword TEXT.
- This data type is generally used to store addresses.
Numeric Data Type
In PostgreSQL, numeric data type is classified into two categories-
- Integers
- Floating-point numbers
Integers
There are several types of integer data types available in PostgreSQL. These are listed below:

Floating Point Numbers
There are several types of floating-point number data types available in PostgreSQL. These are listed below:

Temporal Data Type
PostgreSQL also supports date and time data using the temporal data types. Dates are stored according to the Gregorian calendar. There are five main types of temporal data types in PostgreSQL.
- DATE: DATE data type is used to store only the date. This data type takes up a 4-byte space in memory. The lowest date that can be stored is 4713 BC, and the highest date that can be stored is 5874897 AD.
- TIME: TIME data type is used to store only the time of day (without time zone). This data type takes up an 8-byte space in memory. The lowest time that can be stored is 00:00:00, and the highest time that can be stored is 24:00:00.
- TIMESTAMP: TIMESTAMP data type is used to store both date and time values (without time zone). This data type takes up an 8-byte space in memory. The lowest date that can be stored is 4713 BC, and the highest date that can be stored is 294276 AD.
- TIMESTAMPZ: TIMESTAMPZ data type is used to store both date and time values (with time zone). This data type takes up an 8-byte space in memory. The lowest date that can be stored is 4713 BC, and the highest date that can be stored is 294276 AD.
- INTERVAL: INTERVAL data type is used to store the period of time. This data type takes up a 12-byte space in memory. The lowest time interval it can store is -178000000 years and the highest time interval it can store is 178000000 years.
Arrays
PostgreSQL allows us to store data in a column of a table as a multidimensional array of variable length. This multidimensional array can be an array of integers, strings, etc.
We can declare a column of a multidimensional array in a table as described below-
We will create a table Employee with attributes name, payment and order. We will declare payment as a 1-dimensional integer type array and order as a 2-dimensional array of text type. Name attribute will be of text data type.
CREATE TABLE Employee (
name text,
payment integer[ ],
orders text[ ][ ]
);
Another way of declaring a 1 dimensional array is using the keyword ARRAY.
CREATE TABLE Employee (
name text,
payment integer ARRAY[5],
orders text[ ][ ]
);
The data in this table can be inserted as-
INSERT INTO Employee VALUES (
'Tanisha',
'{10000, 15000, 20800, 10500}',
'{{"Order1", "Order2"}, {"Order3", "Order4"}}'
);
The data in this table can be accessed as-
SELECT name from Employee WHERE payment[3] > payment[1];
The output of the above query will be-

JSON
PostgreSQL also supports JSON (Javascript Object Notation) data which can be stored using the JSON data type. There are two types of JSON data types available in PostgreSQL. These are-
- JSON: JSON data type is used to store simple JSON data. Each time this data is processed, it requires reparsing.
- JSONB: JSONB data type is used to store JSON data in binary format. The JSONB type of data is slow in insertion but fast in processing. Moreover, this data type supports indexing.
UUID
UUID stands for Universally Unique Identifier. This data type allows us to store universal unique identifiers. The universal unique identifier is written as a series of lower case hexadecimal digits separated by hyphens, specifically a 8-digit group, 4-digit groups, and a 12-digit group, for a total of 32 digits representing the 128 bits.
For example, − 550e8400-e29b-41d4-a716-446655440000 is an UUID.
The UUID data type is preferred over the Serial data type because UUID provides better uniqueness than serial and is used to hide sensitive data revealed to the public like id in a URL.
These are all about the data types in PostgreSQL. Besides the above-defined data types, some special data types are present in PostgreSQL, which aid us in geometric and network work.
Special Data Type
There are two types of special data types available in PostgreSQL. These are-
- Geometric data type
- Network data type
Let’s discuss each of these types.
Geometric Data Type
Geometric data type represents two-dimensional spatial objects. There are several geometric data types available in PostgreSQL.
- Box: The box data type is used to draw a rectangular box. This data type takes up 32 bytes of space in memory. For drawing a box, we just need to specify the coordinates of the opposite corners of the rectangular box as (x1, y1) and (x2, y2).
- Circle: The circle data type is used to draw a circle. This data type takes up 24 bytes of space in memory. For drawing a circle, we just need to specify the coordinates of the centre of the circle and its radius as (x, y, r).
- Line: The line data type is used to draw a line between two points. This data type takes up 32 bytes of space in memory. For drawing a line, we just need to specify the coordinates of the two points between which we want to draw a line as (x1, y1) and (x2, y2).
- Lseg: The lseg data type draws a line segment between two points. This data type takes up 32 bytes of space in memory. For drawing a line segment, we just need to specify the coordinates of the two points between which we want to draw a line segment as (x1, y1) and (x2, y2).
- Path: The path data type is used to draw a path of n lines. This path can be closed (similar to polygon) or open. This data type takes up 16 + 16n bytes space in memory. For drawing a path, we just need to specify the coordinates of the n lines in the path as [(x1, y1),....].
- Point: The point data type is used to draw a point on a plane. This data type takes up 16 bytes of space in memory. We just need to specify its coordinates for drawing a point as [(x1, y1),....].
- Polygon: The polygon data type is used to draw a closed polygon of n lines. This data type takes up 40 + 16n bytes space in memory. For drawing a polygon, we just need to specify the coordinates of the n lines in the polygon as ((x1, y1),....).
Next, we will discuss network data types.
Network Data Type
The network data type is used to store network addresses like IPv4, IPv6, and MAC addresses. This data type is preferred over text data type to store network addresses because network data type offers input error checking and specialised operators and functions.
The network data types available in PostgreSQL are-
- Cidr: The cidr data type stores IPv4 and IPv6 network addresses. This data type takes up 7 or 19 bytes of space in memory.
- Inet: The inet data type stores IPv4 and IPv6 hosts and network addresses. This data type takes up 7 or 19 bytes of space in memory.
- Macaddr: The macaddr data type stores MAC addresses. This data type takes up 6 bytes of space in memory.
FAQs
-
What is the difference between char data type and varchar data type in PostgreSQL?
Char data type is used to store fixed length characters in a column. It is represented by char(n), where n is the length of characters. If the string to be inserted is shorter than n, PostgreSQL adds spaces as the rest of the characters. If the user does not specify n, it is taken as 1.
On the other hand, the varchar data type stores variable-length characters in a column. It is represented by varchar(n), where n is the length of characters. If the string to be inserted is shorter than n, then PostgreSQL does not add spaces as the rest of the characters. If the user does not specify n, it is taken as infinity by default.
-
What is the serial data type in PostgreSQL?
The serial data type is a type of integer data type used to store a sequence of integer values. It is similar to AUTO_INCREMENT in MySQL. This data type takes up 4 bytes of space in memory and ranges from 1 to 2147483647.
-
Which data type is better - Serial or UUID?
Both serial and UUID data types have their benefits-
If we want more efficiency, then serial data type is preferred over UUID since serial is more efficient than UUID. Serial data type takes up 8 bytes, whereas UUID takes up 16 bytes.
But UUID is preferred over serial if we want a table’s data from hackers and want to keep our data safe.