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.

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:

Character

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

Numeric types

Numeric data types include:

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

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.

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:

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:

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:

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:

Geometric

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

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.

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:

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:

Pseudo Types

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

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.