PostgreSQL Data Types

Introduction

PostgreSQL is an open-source relational database management system known for its robustness and extensibility. This also means PostgreSQL offers a variety of data types for users.

In this tutorial, we will cover the different built-in data types available in PostgreSQL.

When creating a table in PostgreSQL, you can specify a data type for each column. PostgreSQL supports a wide range of built-in data types:

Note: You can create custom data types in PostgreSQL using the CREATE TYPE SQL command.

Character

PostgreSQL uses character data types for storing text values. There are three character data types in PostgreSQL:

Name	Description
character(n), char(n)	Fixed-length strings, where n is the number of characters. Blank space padded on the right to equal n.
character varying(n), varchar(n)	Variable-length string with a character limit, where n is the number of characters.
text	Variable-length, unlimited string.

Numeric types

Numeric data types include:

Two-, four-, and eight-byte integers
Four- and eight-byte floating point numbers
Selectable decimals:

Name	Storage Size	Description	Range
smallint	2 bytes	Small-range integer.	-32768 to +32767
integer	4 bytes	Medium-range integer.	-2147483648 to +2147483647
bigint	8 bytes	Large-range integer.	-9223372036854775808 to 9223372036854775807
decimal	variable	User-specified precision decimal.	Up to 131072 digits before the decimal point. Up to 16383 digits after the decimal point
numeric	variable	User-specified precision decimal.	up to 131072 digits before the decimal point. Up to 16383 digits after the decimal point
real	4 bytes	Variable precision decimal.	6 decimal digits precision
double precision	8 bytes	Variable precision decimal.	15 decimal digits precision
smallserial	2 bytes	Small autoincrementing integer.	1 to 32767
serial	4 bytes	Medium autoincrementing integer.	1 to 2147483647
bigserial	8 bytes	Large autoincrementing integer.	1 to 9223372036854775807

Monetary

The monetary data type stores a numeric amount of money with fixed fractional precision. This type stores up to 8 bytes of data with a range of -92233720368547758.08 to +92233720368547758.07 and use numeric, integer, and bigint data types as values.

Note: Using floating-point numbers with the monetary data type is not recommended due to the potential for rounding errors.

Date/Time

PostgreSQL Supports all the standard SQL date and time data types, with a resolution of 1 microsecond or 14 digits. Date is the only exception, with a resolution of one day, counted according to the Gregorian calendar:

Name	Storage Size	Description	Range
timestamp	8 bytes	Date and time, without time zone.	4713 BC to 294276 AD
timestampz	8 bytes	Date and time, with time zone.	4713 BC to 294276 AD
date	4 bytes	Date.	4713 BC to 294276 AD
time without time zone	8 bytes	Time of day, without time zone.	00:00:00 to 24:00:00
time with time zone	12 bytes	Time of day, with time zone.	00:00:00 + 1459 to 24:00:00-1459
interval	12 bytes	Time interval.	-178000000 to 178000000 years

Binary

PostgreSQL can save variable-length binary strings as the bytea data type, taking 1 or 4 bytes plus the size of the actual binary string.

Boolean

A Boolean data type is declared using bool or boolean keywords. It can hold true (1), false (0), or unknown (null) values.

Enumerated

Enumerated data types consist of a static, ordered set of values, such as numbers from 1 to 10 or months in the year. Unlike other data types, you can create enumerated types using the create type command:

CREATE TYPE year AS ENUM ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

Bit String

Bit string type stores strings of 1's and 0's, used to store or visualize bit masks:

Name	Description
bit(n)	Stores a bit string with a fixed length of n characters.
varying(n)	Stores a bit string of a varying length, up to n characters.

UUID

A UUID (Universally Unique Identifiers) is a set of 32 digits created by an algorithm. It consists of several groups of four, eight, and twelve digits, separated by hyphens:

Network Address

PostgreSQL uses network address data types to store IPv4, IPv6, and MAC addresses:

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

Using network address data types has several advantages over using plain text. This includes saving storage space, specialized functions and commands, and easier error checking.

Text Search

Text search data type allows you to search for the best match in a collection of natural-language documents:

Name	Description
tsvector	Represents a document optimized for text search, with a list of distinct words normalized to merge different variants of the same word (lexemes).
tsquery	Stores the keywords that need to be searched and combines them using Boolean operators (AND, OR, and NOT).

Geometric

Geometric data types represent spatial objects rendered in two dimensions, such as points, lines, and polygons:

Name	Storage Size	Representation	Numerical Description
point	16 bytes	Point on a plane.	(x,y)
line	32 bytes	Infinite line.	((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	Open or closed path.	((x1,y1),...(xn,yn))
polygon	40+16n bytes	Polygon.	((x1,y1),...(xn,yn))
circle	24 bytes	Circle.	((x,y),r) (center point and radius)

XML

PostgreSQL allows you to save XML data as an XML data type using the XMLPARSE function:

XMLPARSE (DOCUMENT [document name] WELLFORMED)

or:

XMLPARSE (CONTENT [XML content] WELLFORMED)

Where:

[document name]: A singly-rooted XML document.
[XML content]: Valid XML value
WELLFORMED: This option guarantees that [document name] or [XML content] resolve to a well-formed XML document. Use it only when you don't want the database to check if the input is well-formed.

JSON

PostgreSQL offers two JSON data types:

json: An extension of the text data type with JSON validation. This data type saves data exactly the way it is (including whitespace). You can quickly insert it into the database, but it is relatively slow to retrieve due to reprocessing.
jsonb: Represents JSON data in a binary format. Slower to insert into the database, but indexing support and lack of reprocessing make retrieval significantly faster.

Differences between JSON and JSONB data formats

Array

The array data type lets you define a column of a table as a multidimensional array that can use any base, enumerated, or composite data type. You can declare, modify, and search arrays as you would any other column in the database.

Composite

Composite data types allow you to use a row or record of a table as a data element. Similar to array data types, you can also declare, search, and modify composite values.

Range

Range data types use discreet or continuous ranges of other data types. Built-in range data types include:

Name	Description
int4range	Range of medium-size integers.
int8range	Range of large integers.
numrange	Range of user-specified precision decimals.
strange	Range of times and dates without a time zone.
tstzrange	Range of times and dates with a time zone.
daterange	Range of dates.

You can also create custom range types by using other data types as a basis.

Object Identifier

PostgreSQL uses object identifiers as primary key systems when performing specialized input and output operations:

Name	References	Description
oid	any	Numeric object identifier.
regproc	pg_proc	Function name.
regprocedure	pg_proc	Function with argument types.
regoper	pg_operator	Operator name.
regoperator	pg_operator	Operator with argument types.
regclass	pg_class	Relation name.
regtype	pg_type	Data type name.
regconfig	pg_ts_config	Text search configuration.
regdictionary	pg_ts_dict	Text search dictionary.

Pseudo Types

Pseudo types are a collection of special entries for declaring a function's argument or result type:

Name	Description
any	Function accepts any input data type.
anyelement	Function accepts any data type.
anyarray	Function accepts any array data type.
anynonarray	Function accepts any non-array data type.
anyenum	Function accepts any enumerated data type.
anyrange	Function accepts any range data type.
cstring	Function accepts or return a null-terminated C string.
internal	Function accepts or return server-internal data type.
language_handler	Function returns language handler.
fdw_handler	A foreign-data wrapper handler returns fdw_handler.
record	Finds a function that returns an unspecified row type.
trigger	A trigger function returns trigger.
void	Function returns no value.

Conclusion

After reading this article, you should have a basic working knowledge of data types available in PostgreSQL.

Get started with PostgreSQL by checking out our guides to installing PostgreSQL on Windows and installing PostgreSQL on Ubuntu.

To learn how to create a database in PostgreSQL using different methods, make sure to read our article how to create a database in PostgreSQL.

Was this article helpful?

YesNo