PostgreSQL Data Types

A data type specifies what kind of data you want to store in the table field. While creating a table, for each column, you have to use a datatype. It identifies a particular type of data, like integer, Boolean, floating points, and so on.

In this section, we are going to discuss the various data types used in PostgreSQL.

In PostgreSQL, each database table has many columns and has precise data types for each column. It supports an extensive range of data types.

Also, users can create their custom data type using the CREATE TYPE SQL command. And these data types have the following advantages:

Performance:It enhances our performance if we are using these data types correctly and efficiently to store the data values.
Validation: The correct use of data types involves the validation of data and dismissal of data outside the scope of the data type.
Compactness:It stores efficiently, as a column can store a single type of value.
Consistency: The activities in contradiction of columns of the same data type provide reliable results and are usually the fastest.

In PostgreSQL, we have many sets of data types; let us understand them one by one:

Numeric datatype
Character datatype
Date/time datatype
Monetary data type
Binary data type
Boolean data type
Enumerated data type
Geometric data type
Text search data type
UUID data type
Network address type
JSON data type
Bit string type
XML data type
Range data type
Arrays
Composite data type
Object identifiers type
Pseudo data type
pg-Isn data type

Numeric Data Type

Numeric datatype is used to specify the numeric data into the table. It contains the following:

four- and eight-byte floating-point numbers
two-, four-, and eight-byte integers
Selectable-precision decimals.

The below table contains all numeric data types that support in PostgreSQL:

name	description	storage size	range
smallint	stores whole numbers, small range.	2 bytes	-32768 to +32767
integer	stores whole numbers.use this when you want to store typical integers.	4 bytes	-2147483648 to +2147483647
bigint	stores whole numbers, large range.	8 bytes	-9223372036854775808 to 9223372036854775807
decimal	user-specified precision, exact	variable	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
numeric	user-specified precision, exact	variable	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
real	variable-precision, inexact	4 bytes	6 decimal digits precision.
double precision	variable-precision, inexact	8 bytes	15 decimal digits precision
serial	auto incrementing integer	4 bytes	1 to 2147483647
bigserial	large auto incrementing integer	8 bytes	1 to 9223372036854775807

Character Data Type

In PostgreSQL, we have various general purpose character data types, and these data types are used to represent the character type values.

The below table contains all Character data types that are supported in PostgreSQL:

Datatype	Explanation
char(size)	Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
character(size)	Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
varchar(size)	Here size is the number of characters to store. Variable-length string.
character varying(size)	Here size is the number of characters to store. Variable-length string.
text	Variable-length string.

Date/Time Data Type

The PostgreSQL supports the complete set of SQL date and times data types. The date/time datatypes are used to represent the columns using date and time values. Based on the Gregorian calendar, the dates are counted.

The below table contains all date/ time data types that are supported in PostgreSQL:

Name	Description	Storage size	Minimum value	Maximum value	Resolution
timestamp [ (p) ] [ without time zone ]	both date and time (no time zone)	8 bytes	4713 bc	294276 ad	1 microsecond / 14 digits
timestamp [ (p) ] with time zone	both date and time, with time zone	8 bytes	4713 bc	294276 ad	1 microsecond / 14 digits
date	date (no time of day)	4 bytes	4713 bc	5874897 ad	1 day
time [ (p) ] [ without time zone ]	time of day (no date)	8 bytes	00:00:00	24:00:00	1 microsecond / 14 digits
time [ (p) ] with time zone	times of day only, with time zone	12 bytes	00:00:00+1459	24:00:00-1459	1 microsecond / 14 digits
interval [ fields ] [ (p) ]	time interval	12 bytes	-178000000 years	178000000 years	1 microsecond / 14 digits

Monetary type:

Name	Description	Storage size	Range
money	currency amount	8 bytes	-92233720368547758.08 to +92233720368547758.07

Binary Data Types

The bytea data type allows storage of binary strings as we can see in the below table:

Name	Storage size	Description
bytea	1 or 4 bytes plus the actual binary string	variable-length binary string

Boolean type

PostgreSQL contains the standard SQL type boolean; The Boolean type has many states such as true, false, and a third state, unknown, represented by the SQL null value.

Name	Description	Storage size
boolean	it specifies the state of true or false.	1 byte

The Boolean datatype input function receives these string demonstrations for the true and false state:

Enumerated Types

In PostgreSQL, the Enumerated data types include a static, ordered set of values.

It is similar to enum types which are compatible with a various programming language. The Enumerated data type is represented in a table with foreign keys to ensure data integrity.

For Example:

CREATE TYPE fruits_name AS ENUM
('Mango','Apple','Orange','Strawberry')

Geometric Data Type

Geometric data types represent two-dimensional spatial objects. The most fundamental type, the point, forms the basis for all of the other types.

Name	Storage size	Representation	Description
point	16 bytes	point on a plane	(x,y)
line	32 bytes	infinite line (not fully implemented)	((x1,y1),(x2,y2))
lseg	32 bytes	finite line segment	((x1,y1),(x2,y2))
box	32 bytes	rectangular box	((x1,y1),(x2,y2))
path	16+16n bytes	closed path (similar to polygon)	((x1,y1),...)
path	16+16n bytes	open path	[(x1,y1),...]
polygon	40+16n	polygon (similar to closed path)	((x1,y1),...)
circle	24 bytes	circle	<(x,y),r> (center point and radius)

Text Search data type

In PostgreSQL, the full-text search data type is used to search over a collection of natural language documents. We have two categories of data types that are compatible with full-text search.

Data types	Description
tsvector	It is used to display a document in a form, which enhance text search.
tsquery	It is used to represent a text query.

UUID data types

The UUID stands for Universally Unique identifiers is a 128-bit quantity which is created by an algorithm. It is the best-suited data type for the primary keys. The UUID is written as a group of lower-case hexadecimal digits through multiple sets separated by hyphens.

For example:

Note: PostgreSQL also agrees to take alternative forms of UUID inputs such as no hyphens, all capital case, braces, and so on.

Network Address Data type

PostgreSQL provides data types to store Mac Address, IPv4, and IPv6, as we can see in the below table. It enhances these types other than plain text types to contain network address as these offer input error checking and particular function and operators.

The below table contains all network address data types that are supported in PostgreSQL:

Data type	Description	Storage Size
inet	It stores the IPv4 and IPv6 hosts and networks.	7 or 19 bytes
cidr	It is used to store the IPv4 and IPv6 networks.	7 or 19 bytes
macaddr	It stores the MAC addresses.	6 bytes

JSON Data type

PostgreSQL provides two kinds of data types for storing the JSON (JavaScript Object Notation) data.

JSON
JSONB

Json

It is an extension of a text data type with JSON validation. In this, we can insert the data quickly, but the data retrieval is comparatively slow. It saves inputted data just the way it contains the whitespace. It is also reprocessing on the data retrieval.

Jsonb

It is a binary representation of the JSON data. It is also compatible with indexing and also improves the whitespace to make the retrieval quicker. In this, the insertion is slow, but the data retrieval is more rapid, and reprocessing is required on the data retrieval.

Bit string types

The bit strings data type contains two categories of strings that are 1's and 0's. The bitmasks can be stored with the help of these strings. In this, we have two kinds of SQL bit, such as:

bit varying(n)
bit (n)

Here, n is a positive integer.

XML type

In PostgreSQL, the XML data type is used to store the XML data. The function of the XML data type is to check whether that the input XML is well-formed, and also there are support functions to perform type-safe operations on it.

For Example:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>PostgreSQL</title><chapter>...</chapter></book>')

Range Types

These data types are used to display a range of values of some element types, known as the range's subtype. It also signifies several elements of values in a single range value. In this, we can also create our range types.

In PostgreSQL, we have following built-in range types:

Built-in range types	Description
tsrange	Range of timestamp without time zone
tstzrange	Range of timestamp with time zone
daterange	Range of date
int4range	Range of integer
int8range	Range of bigint
numrange	Range of numeric

Array Type

In this, the PostgreSQL provides a column of tables as a variable-length and the multidimensional array. We can create any user-defined base type, built-in, composite, and enumerated type arrays.

Here, we can perform various operations on arrays such as declare, insert, accessing, modifying, and searching.

Composite Types

In PostgreSQL, the composite data type is used to signify the structure of a row or record as a list of file names and data types.

Pseudo data type

In PostgreSQL, the data types are pseudo types, which are used to contain many special-purpose entries. And it is used to declare a result type or the function's argument, but it is not compatible to use as a column data type.

The below table contains some of the commonly used pseudo data types that support in PostgreSQL:

Pseudo data types	Description
any	It shows that a function accepts any input data type.
anyelement	It accepts any data type.
anyarray	It shows a function that accepts any array data type
anyenum	It accepts any enum data type
anyrange	It accepts any range of data type
cstring	It is used to specify that a function accepts or returns a null-terminated C string.
language_handler	A procedural language call handler is declared to return language_handler.
fdw_handler	The fdw (foreign-data wrapper) handler is declared to return fdw_handler.
record	It is used to specify a function which is taking or returning an unspecified row type.
trigger	It is declared to return the trigger.
pg_ddl_command	It is used to represent the DDL commands that are available to event triggers.
void	It is used to specify that a function returns no value.

Object identifier (OIDs) type

These types of data types are used as primary keys for several system tables. The oid type represents an object identifier and currently implemented as an unsigned four-byte integer. In huge databases or even in large individual tables, it is not big enough to offer database-wide individuality.

Object identifiers are used for references to system tables. Beyond comparison, the oid type itself has few operations that can be cast to integer, and can be manipulated using the standard integer operators.

The below table contains all the object identifier data types that are supported in PostgreSQL:

Name	Description	References	Value Example
oid	numeric object identifier	Any	564182
regproc	function name	pg_proc	Sum
regprocedure	function with argument types	pg_proc	sum(int4)
regoper	operator name	pg_operator	+
regoperator	operator with argument types	pg_operator	*(integer,integer) or -(NONE,integer)
regclass	relation name	pg_class	pg_type
regtype	data type name	pg_type	Integer
regnamespace	namespace name	pg_namespace	pg_catalog
regconfig	text search configuration	pg_ts_config	English
regdictionary	text search dictionary	pg_ts_dict	Simple

pg_lsn Type

The pg_lsn data type can be used to store Log Sequence Number (LSN) data, a pointer to a location in the XLOG. It is used to signify the XLogRecPtr and an internal system type of PostgreSQL. The pg_lsn type is compatible with the standard comparison operators, such as > and =.

Note:

While using the data types, we can refer to the following points:

If we have an IEEE 754 data source, we can use the float data type
For integer data type, we can use int.
Never use char.
If we want to limit the input, we can apply a text data type.
When we have vast numbers, we can use bigint only.

Overview

The character data types are used to store text values.
The Network address type is used to optimize the storage of network data.
A binary string is a classification of bytes or octets.
PostgreSQL provides two different types of numbers, such as Floating-point numbers and integers.
The range data types are used to display a range of values of some element types.
The Boolean data type has three values, such as True, False, and Null.
The UUID data type is a 128-bit quantity, which is made by an algorithm.
The composite data type is used to signify the structure of a row or record.
PostgreSQL, the enumerated Data types, are rarely used to demonstrate the modified information like branch id or country code.
To define date and time information in several formats, PostgreSQL provides Date and Time data type.
The object identifiers data type represents an object identifier.
PostgreSQL designed the text search data to support full-text search.
The Geometric data type is used to signify two-dimensional spatial objects.
In PostgreSQL, the pseudo type is used for many special-purpose entries.
The pg_lsn data type is used to store Log Sequence Number (LSN) data.