Top 5 PostgreSQL Data Types

Quick Contact

Python Tutorial

Python Panda Tutorial

Python Selenium

Python Flask Tutorial

Python Flask tutorial Introduction

Python Django

Numpy

Tensorflow

Interview Questions & Answers

PostgreSQL Data Types

The PostgreSQL is a database that manages multiple values of data.

What is the data type?

A data type (or) only types refers to the specific type of data that the variable holds. Moreover, these include the date, timestamps, varchar, and some other formats.

Numeric Datatypes

PostgreSQL supports two types of Numeric datatypes, including integers and Floating point numbers.

Name	Storage Size	Range
Small	2 bytes	-32768 – +32767
Integer	4 bytes	-2147483648 to +2147483647
Bigint	8 bytes	-9223372036854775808 to 9223372036854775807
Real	4 bytes	It support 06 digits precision
Double precision	8 bytes	It supports 15digit decimal precision
Decimal	Variable	It enables up to 131072 before the decimal point, up to 16383 after the decimal point.
Numeric	Variable	It enables up to 131072 before the decimal point, up to 16383 after the decimal point.
Small serial	2 bytes	1-32767
Serial	4 bytes	1 – 2147483647
Double serial	8 bytes	1 to 9223372036854775807

Monetary data types

This is the detailed data that the database provides. Here in this data type, numeric values, int and bigint can be modified into money. So now many person say that we can use Float datatype. But this is not recommended to manage money due to the potential for rounding bugs.

Name	Storage	Range
Money	8 bytes	-92233720368547758.08 to +92233720368547758.07

Character data types

The following table includes the PostgreSQL character datatypes.

Name	Descriptions
Char(size)/ Character(size)	Hence size is the number of characters to save. It includes the fixed-length strings. Here the space padded on the right to equal size characters.
Varchar(size)	Hence size is the number of characters to store. It contains the variable string length.

Date/time data type

The date/time datatype is used to represent the columns using the date and time values.

Name	Size	Range	Resolution
TimeStamp With or without zone	8 bytes	4713 BC to 294276 AD	1 microsecond / 14 digits
Date	4 bytes	4713 BC to 294276 AD	One day
Time Without Time Zone	8 bytes	00:00:00 to 24:00:00	1 microseconds/14 digits
Timewith timezone	12 bytes	00:00:00 + 1459 to 24:00:00-1459	1 microseconds/14 digits
Interval	12 bytes	-178000000 to 178000000 years	I microsecond/14 digits

Geometric data types

The geometric data types represent two-dimensional data objects. Moreover, these data types help to perform various operation like rotation, scaling and translation etc.

Name	Storage	Representation	Description
Point	16 bytes	Point on a Plane	(x, y)
Line	32 bytes	Infinite Line	((x1.y1).(x2.y2))
Line Segment	32 bytes	Finite Line Segment	((x1.y1).(x2.y2))
Box	32 bytes	Rectangular Box	((x1.y1).(x2.y2))
Path	16 n + 16 n bytes	Close and Open path	((x1.y1)……)
Polygon	40 + 16 n bytes	Polygon	[(x1.y1)….]
Circle	24 bytes	Circle	<(x.y).r>

Network Address type

Today we do have many network address types like IPV4, IP V6, and MAC address. Moreover, this PostgreSQL offers different data types to store in this address.

It can store in a network address, instead of plain text, it is preferable to keep these data types.

Name	Storage Size	Description
Cidr	7 (or) 19 bytes	IPV4 and IPV6 networks
Inet	7 (or) 19 bytes	IPV4 and IPV 6 hosts and networks
Macaddr	6 bytes	MAC address

Enumerated data types

In PostgreSQL, enumerated data types are useful for representing the rarely changing information such as country code (or) branch id. So to ensure data integrity, the enumerated data type is defined with a table with a foreign key.

Range type

These represent data that uses the data range. These range types can be a discrete range (or) continuous ranges.

Name	Description
Int4range	Range of the integer
Int8range	Range of the big int
Numrange	Range of the numeric
Strange	Without timezone, time stamp range
Tstzrange	With timezone, time stamp range
Date range	It represents the range of the date.

Pseudo types

PostgreSQL contains several special-purpose entries that are collectively called as pseudotypes. Moreover, a pseudo data type cannot be used as a column data type that can be used as a column data type. Additionally, this can be used to declare a function argument (or) a result type.

Name	Description
Any	It denoted the function accepts any input data type.
Any Element	It denoted the function accepts any data type.
Any Array	It denoted the function accepts any array data type.
Anynonarray	It denoted the function accepts any non-array data type.
Anyenum	It denoted the function accepts any enum data type.
Anyrange	It denoted the function accepts any range data type.
Internal	It denoted the function accepts (or) returns an internal server data.
Record	It denoted the function of returning an unspecified row type.
Void	It denoted the function that has no value.
CString	It denoted the function accepts (or) returns the Nullterminated C string.

Apply now for Advanced Python Training Course