Data Types in PostgreSQL

There are number of data types in PostgreSQL and user can create their own data types as PostgreSQL is identified with object oriented database.

Data types in PostgreSQL has been differentiated on the basis of its use and its type, below is the list of data types available in PostgreSQL

• Numeric types
• Monetary types
• Character types
• Binary types
• Date/Time types
• Boolean types
• Enumerated types
• Geometric types
• Network Address types
• Bit string types
• Text search types
• UUID types
• XML types
• JSON types
• Arrays
• Composite types
• Range types
• Domain types
• Object oriented types
• pg_lsn types
• pseudo types

Now we will discussion each one in details

Numeric types

Below table represents all number data types available

Name	Storage Size	Description	Range
smallint	2 bytes	Small range integer	-32768 to +32767
integer	4 bytes	integer	-2147483648 to +2147483647
bigint	8 byes	Large range integer	-922337203685475808 to +922337203685475807
decimal	variable	User specified precision, exact	Upto 131072 digits before decimal, upto 16383 digits after decimal
numeric	Variable	User specified precision	Upto 131072 digits before decimal, upto 16383 digits after decimal
real	4 bytes	Variable precision inexact	6 decimal digit precision
double precision	8 bytes	Variable precision inexact	15 decimal digits precision
smallserial	2 bytes	Small auto increment integer	1 to 32767
serial	4 bytes	auto increment integer	1 to 2147483647
bigserial	8 bytes	large auto increment integer	1 to 922337203685475807

Monetary types

Below is the monetary data type available to use in PostgreSQL

Name	Storage Size	Description	Range
Money	8 bytes	Currency amount	-922337203685475808.08 to +922337203685475808.07

Character types

Below are described character types in PostgreSQL

Name	Description
Character varying(n), varchar(n)	Variable length with limit
Character(n) , char(n)	Fixed length, blank padded
Text	Variable unlimited length

Binary types

Name	Storage	Description
bytea	1 or 4 bytes plus actual binary string	Variable length binary string

bytea is binary data types allows to store binary strings

bytea Hex format:

Select '\xDEADBEEF';

bytea Escape format:

SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;

     bytea

----------------

 abc klm *\251T

Date/Time types

Below are the available data types related to Date/time in PostgreSQL

Name	Storage Size	Description	Low value	High value	resolution
Timestamp[(p)][without timezone]	8 bytes	Both date and time no timezone	4713BC	294276 AD	1 microsec
timestamp [ (p) ] with time zone	8 bytes	both date and time, with time zone	4713BC	294276 AD	1 microsec
date	4 bytes	date (no time of day)	4713BC	5874897 AD	1 day
time [ (p) ] [ without time zone ]	8 bytes	time of day (no date)	00:00:00	24:00:00	1 microsec
time [ (p) ] with time zone	12 bytes	time of day (no date), with time zone	00:00:00+1459	24:00:00-1459	1 microsec
interval [ fields ] [ (p) ]	16 bytes	time interval	178000000 years	178000000 years	1 microsec

Boolean type

Boolean data type is command and same is  every database system

Name	Storage Size	Description
Boolean	1 byte	State of true or false

Enumerated types

Enumerated data types are comprised static ordered set of  values. They are equivalent to enum types supported in number of programming languages.

example-days of the week, set of status value for piece of data

this kind of types are created as below

CREATE TYPE order AS enum ('Yes', 'No','NA');

Geometric type

Geometric data types represents two dimensional spatial objects, below are available data types in PostgreSQL

Name	Storage Size	Description	Representation
Point	16 bytes	Point of plane	(x,y)
Line	32 bytes	Infinite line	{A,B,C}
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	[(x1,y1)…..]
path	16+16n bytes	Open path	((x1,y1)…..)
Polygon	40+16 bytes	Polygon	((x1,y1)…..)
circle	20 bytes	circle	<(x,y), r> (central point and radius

Network types

PostgreSQL offers data types to store IPv4, IPv6 and MAC addresses

Name	Storage Size	Description
cidr	7 or 19 bytes	IPv4 and IPv6 networks
inet	7 or 19 byes	IPv4 and IPv6 hosts and networks
macaddr	6 bytes	MAC addresses
macaddr8	8 bytes	MAC address (EUI -64 format)

Bit string types

Bit string types are strings of 1's and 0's. They can be used to store or visualize bit masks

bit(n)-- > exact match

bit varying(n) -- > variable length

Text Search types

there are two data types to support full text search 

tsvector

a tsvector value is stored list of distinct lexemes, which are words that have been normalized to merge different variables of the same word.

example

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;

                      tsvector

----------------------------------------------------

 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat' 

tsquery

A tsquery value stores lexemes, that are to be searched for  and combine them using Boolean operator &(AND), | (OR) and !(NOT) as well as the phrase search operator <->

SELECT 'fat & rat'::tsquery;

    tsquery    

---------------

 'fat' & 'rat' 

UUID type

• This data stores universally unique identifier(UUID) as per standards defined.
• This identifier is 128 bit quantity that is generated by an algorithm chosen to make it very unlikely that the same identifier will be generated by any one else in the known universe  using same algorithm
• UUID is written as sequence of lower case hexadecimal digits , in several groups separated by hyphens specially group of 8 digits followed by three groups of 4 digits followed by group of 12 digits, total 32 digits representing 128 bits
• for example

           a0eebc99-9cob-4ef8-bb6d-6bb9bd380011

XML types

XML data types can be used to store XML data. To use data type, installation should be done using below option

configure --with-libxml

This data type can store well formed "documents", as defined by xml standards as well as "content" fragments which are defined by production XMLDecl? content in XML standards.

JSON types

JSON data type are for storing JSON (java script object notation) data

There are two types

1. json
2. jsonb

The major difference is one of efficiency 

• json- stores an exact copy of input text
• jsonb- 
• Data stored in decomposed binary format that makes it slightly slower to input due to added conversion overhead.
• It also support indexing

Json primitive type	PostgreSQL type	Notes
String	Text	\u0000 is allowed, as are non-ASCII unicode escape if db encoding is UTF-8
Number	Numeric	NaN and infinity values are disallowed
Boolean	Boolean	Lower case true and false
Null	(none)	SQL null is different concept

For example

select '5' ::json;

select '[1,2,"foo",null]'::json;

Composite types

A composite type represents the structure of row or record; it is essentially just a list of field names and their data types

Declaration

CREATE TYPE complex AS (

r double precision,

i double precision

);

CREATE TYPE inventory_item AS (

name text,

supplied_id integer,

price numeric

);

syntax is quite similar to create table command without any constraints

Use of composite types

CREATE TABLE on_hand (

item inventory_item,

count integer

);

Inserting data 

insert into on_hand values (ROW('Fuzzydice',45,1.99),1000)

Accessing data

select (item).name from on_hand where (item).price>9.99;

or

select (on_hand.item).name from on_hand where (on_hand.item).price >9.99;

Range types

There are following range types in PostgreSQL

Name	Description
int4range	Range of integer
Int8range	Range of big integer
numrange	Range of numeric
tsrange	Range of timestamp without timezone
tstzrange	Range of timestamp with timezone
daterange	Range of date

These are the data types available in PostgreSQL, this includes 11 version as well.

Read More