PostgreSQL Data Types

July 29, 2021

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.

PostgreSQL data types

PostgreSQL Data Types

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:

NameDescription
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.
textVariable-length, unlimited string.

Numeric types

Numeric data types include:

  • Two-, four-, and eight-byte integers
  • Four- and eight-byte floating point numbers
  • Selectable decimals:
NameStorage SizeDescriptionRange
smallint2 bytesSmall-range integer.-32768 to +32767
integer4 bytesMedium-range integer.-2147483648 to +2147483647
bigint8 bytesLarge-range integer.-9223372036854775808 to 9223372036854775807
decimalvariableUser-specified precision decimal.Up to 131072 digits before the decimal point. Up to 16383 digits after the decimal point
numericvariableUser-specified precision decimal.up to 131072 digits before the decimal point. Up to 16383 digits after the decimal point
real4 bytesVariable precision decimal.6 decimal digits precision
double precision8 bytesVariable precision decimal.15 decimal digits precision
smallserial2 bytesSmall autoincrementing integer.1 to 32767
serial4 bytesMedium autoincrementing integer.1 to 2147483647
bigserial8 bytesLarge 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:

NameStorage SizeDescriptionRange
timestamp8 bytesDate and time, without time zone.4713 BC to 294276 AD
timestampz8 bytesDate and time, with time zone.4713 BC to 294276 AD
date4 bytesDate.4713 BC to 294276 AD
time without time zone8 bytesTime of day, without time zone.00:00:00 to 24:00:00
time with time zone12 bytesTime of day, with time zone.00:00:00 + 1459 to 24:00:00-1459
interval12 bytesTime 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:

NameDescription
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:

An example of a randomly generated UUID

Network Address

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

NameStorage SizeDescription
cidr7 or 19 bytesStores IPv4 and IPv6 networks.
inet7 or 19 bytesStores IPv4 and IPv6 hosts and networks.
macaddr6 bytesStores 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:

NameDescription
tsvectorRepresents a document optimized for text search, with a list of distinct words normalized to merge different variants of the same word (lexemes).
tsqueryStores 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:

NameStorage SizeRepresentationNumerical Description
point16 bytesPoint on a plane.(x,y)
line32 bytesInfinite line.((x1,y1),(x2,y2))
lseg32 bytesFinite line segment.((x1,y1),(x2,y2))
box32 bytesRectangular box.((x1,y1),(x2,y2))
path16+16n bytesOpen or closed path.((x1,y1),...(xn,yn))
polygon40+16n bytesPolygon.((x1,y1),...(xn,yn))
circle24 bytesCircle.((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:

NameDescription
int4rangeRange of medium-size integers.
int8rangeRange of large integers.
numrangeRange of user-specified precision decimals.
strangeRange of times and dates without a time zone.
tstzrangeRange of times and dates with a time zone.
daterangeRange 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:

NameReferencesDescription
oidanyNumeric object identifier.
regprocpg_procFunction name.
regprocedurepg_procFunction with argument types.
regoperpg_operatorOperator name.
regoperatorpg_operatorOperator with argument types.
regclasspg_classRelation name.
regtypepg_typeData type name.
regconfigpg_ts_configText search configuration.
regdictionarypg_ts_dictText search dictionary.

Pseudo Types

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

NameDescription
anyFunction accepts any input data type.
anyelementFunction accepts any data type.
anyarrayFunction accepts any array data type.
anynonarrayFunction accepts any non-array data type.
anyenumFunction accepts any enumerated data type.
anyrangeFunction accepts any range data type.
cstringFunction accepts or return a null-terminated C string.
internalFunction accepts or return server-internal data type.
language_handlerFunction returns language handler.
fdw_handlerA foreign-data wrapper handler returns fdw_handler.
recordFinds a function that returns an unspecified row type.
triggerA trigger function returns trigger.
voidFunction 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
Aleksandar Kovačević
Aleksandar Kovacevic is an aspiring Technical Writer at phoenixNAP. With a background in both design and writing, he aims to bring a fresh perspective to writing for IT, making complicated concepts easy to understand and approach.
Next you should read
How to Create a Database in PostgreSQL
May 26, 2021

This tutorial shows three easy ways to create a database in PostgreSQL. Whether you prefer using a GUI or the command line, PostgreSQL's got you covered.
Read more
How to List All Databases in PostgreSQL
June 9, 2021

Introduction One of the important tasks when managing PostgreSQL servers is listing the existing databases and their tables. There are three ways to list all databases...
Read more
How to Check Your PostgreSQL Version
April 21, 2020

Use this guide to check your PostgreSQL version with a few straightforward commands. You can retrieve the PostgreSQL version directly from your command line or use an effective SQL statement within the PostgreSQL shell.
Read more
How to Install SQL Workbench for PostgreSQL
March 13, 2020

Save time and effort by managing different database systems with a single tool. Find out how to set up SQL Workbench to connect to a PostgreSQL database with four (4) easy steps.
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.