PostgreSQL Data Types

Last update on November 13 2023 10:00:12 (UTC/GMT +8 hours)

Data Types

This document discusses PostgreSQL Data Types. While creating table, for each column, you specify a data type, i.e. what kind of data you want to store.

This enables several benefits:

Consistency: A column can can store a single type of value. So, when you select a column with integer type, you are sure that the result will have integer only.

Validation : A column can store different types of values. So, if a column's type is integer, you can not store string there.

Compactness: Since a column can store a single type of value, it is stored in a compact way.

Performance: Since type is uniform in a column, the values stored can be processed quickly, which enhances performance

A wide set of Data Types are available in PostgreSQL. Besides, users can create their own custom data type using "CREATE TYPE" command.

In the rest of the document, we have discussed each of the PostgreSQL Data Types based on PostgreSQL 9.1 Version.

Category - Numeric Types

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

Category - Monetary Types

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

Category - Character Types

Name	Description
character varying(n), varchar(n)	variable-length with limit
character(n), char(n)	fixed-length, blank padded
text	variable unlimited length

Category - Binary Data Types

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

Category - Date/Time Types

Name	Description	Storage Size	Low Value	High 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) ]	12 bytes	time interval	-178000000 years	178000000 years	1 microsecond / 14 digits

Category - Boolean Type

Name	Description	Storage Size
boolean	state of true or false	1 byte

Category - Enumerated Type

Unlike other types, Enumerated Types need to be created using CREATE TYPE command. This type is used to store a static, ordered set of values, for example compass directions,i.e. NORTH, SOUTH, EAST, and WEST or days of the week.

Code:

CREATE TYPE mood AS ENUM ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

Once creates, they can be used like any other types.

Category - Geometric Type

It represents two-dimensional objects pertaining to space.

Name	Description	Storage Size	Representation
point	Point on a plane	16 bytes	(x,y)
line	Infinite line (not fully implemented)	32 bytes	((x1,y1),(x2,y2))
lseg	Finite line segment	32 bytes	((x1,y1),(x2,y2))
box	Rectangular box	32 bytes	((x1,y1),(x2,y2))
path	Closed path (similar to polygon)	16+16n bytes	((x1,y1),...)
path	Open path	16+16n bytes	[(x1,y1),...]
polygon	Polygon (similar to closed path)	40+16n byte	((x1,y1),...)
circle	circle	24 bytes	<(x,y),r> (center point and radius)

Category - Network Address Type

These Data Type stores store IPv4, IPv6, and MAC addresses.

Name	Description	Storage Size
cidr	IPv4 and IPv6 networks	7 or 19 bytes
inet	IPv4 and IPv6 hosts and networks	7 or 19 bytes
macaddr	MAC addresses	6 bytes

Category - Bit String Type

Bit String Types are used to store bit masks. They are either 0 or 1. There are two bit types - bit(n) and bit varying(n), where n is a positive integer.

Category - Text Search Type

Used for Full Text Search. There are two Data Types for this -

tsvector: Which is a sorted list of distinct words that have been normalized to merge different variants of the same word, called as "lexemes".

tsquery: A tsquery value stores lexemes that are to be searched for, and combines them honoring the Boolean operators & (AND), | (OR), and ! (NOT). Parentheses can be used to enforce grouping of the operators.

Category - UUID Type

A UUID (Universally Unique Identifiers) is written as a sequence of lower-case hexadecimal digits, in several groups separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits. An example of a UUID is

550e8400-e29b-41d4-a716-446655440000

Category - XML Type

The xml data type can be used to store XML data. For storing XML data, first you create XML values using function xmlparse.

Code:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><title>PostgreSQL Tutorial </title><topics>...</topics></tutorial>')
XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Category - Array Type

In PostgreSQL, it is possible to define a column of a table as a variable length multidimensional array. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. But Arrays of domains are not yet supported.

Declaration of Arrays

Code:

CREATE TABLE monhly_savings (
name text,
saving_per_quarter  integer[],
scheme text[][]);

Inserting values

Code:

INSERT INTO monhly_savings
VALUES ('Bidhan',
'{20000, 14600, 23500, 13250}',
'{{"FD", "MF"}, {"FD", "Property"}}');

Accessing Arrays

Code:

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

So, the above command will select persons whose savings are more in second quarter that fourth quarter.

Category - Composite Types

This type represents a list of field names and their data types, i.e. structure of a row or record of a table.

Category - Object Identifier Types

Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.

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
regconfig	text search configuration	pg_ts_config	english
regdictionary	text search dictionary	pg_ts_dict	simple

Category - Pseudo Types

Name	Description
any	Denotes that a function accepts any input data type.
anyarray	Denotes that a function accepts any array data type
anyelement	Denotes that a function accepts any data type
anyenum	Denotes that a function accepts any enum data type
anynonarray	Denotes that a function accepts any non-array data type
cstring	Denotes that a function accepts or returns a null-terminated C string
internal	Denotes that a function accepts or returns a server-internal data type.
language_handler	A procedural language call handler is declared to return language_handler.
fdw_handler	A foreign-data wrapper handler is declared to return fdw_handler.
record	Denotes a function returning an unspecified row type.
trigger	A trigger function is declared to return trigger.
void	Denotes that a function returns no value.
opaque	An obsolete type name that formerly served all the above purposes.

Previous: PostgreSQL Connector and APIs
Next: Create Database