PostgreSQL Data Types

A datatype specifies, what kind of data you want to store in the table field. While creating table, for each column, you have to use a datatype.

There are mainly three types of datatypes in PotgreSQL. Besides this, users can also create their own custom datatypes using CREATE TYPE SQL command.

Following are the mainly three types of datatypes in PostgreSQL:

Numeric Data Type

Numeric datatype is used to specify the numeric data into the table.

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

String datatype is used to represent the string type values.

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.

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

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

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

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

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)