Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
1.1.
Boolean Data Type
1.2.
Character Data Type
1.3.
Numeric Data Type
1.3.1.
Integers
1.3.2.
Floating Point Numbers
1.4.
Temporal Data Type
1.5.
Arrays
1.6.
JSON
1.7.
UUID
1.8.
Special Data Type
1.8.1.
Geometric Data Type
1.8.2.
Network Data Type
2.
FAQs
3.
Key Takeaways
Last Updated: Mar 27, 2024

PostgreSQL - Data types

Author Pakhi Garg
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

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- truefalse 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-

  1. Integers
  2. 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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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- 

  1. JSON: JSON data type is used to store simple JSON data. Each time this data is processed, it requires reparsing.
  2. 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-

  1. Geometric data type
  2. 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.

  1. 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).
  2. 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).
  3. 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).
  4. 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).
  5. 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),....].
  6. 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),....].
  7. 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-

  1. Cidr: The cidr data type stores IPv4 and IPv6 network addresses. This data type takes up 7 or 19 bytes of space in memory.
  2. 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.
  3. Macaddr: The macaddr data type stores MAC addresses. This data type takes up 6 bytes of space in memory.

FAQs

  1. 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.
     
  2. 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.
     
  3. 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. 
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Key Takeaways

In this article, we talked about data types in PostgreSQL. PostgreSQL offers several data types like boolean, character, numeric, temporal, arrays, JSON, UUID and some special data types (geometric and network). We discussed all these data types thoroughly. 

To study more about data types, refer to Abstract Data Types in C++ and Data Types in C++.

Attention Reader!!!!! Don’t stop here. Start your DBMS journey with the DBMS course on Coding Ninjas. To master SQL, solve our Top 100 SQL Problems asked in various company interviews.

Happy Learning!

Next article
PostgreSQL Create Database
Live masterclass