Logo

PostgreSQL Data Types: A Beginners Guide

Try this guide with our instant dedicated server for as low as 40 Euros

PostgreSQL Data Types

Have you ever wondered how websites remember your login details? 

It is the PostgreSQL data type.  

PostgreSQL data types are the building blocks that define how information is stored and manipulated within a database.

In this tutorial, we will discuss PostgreSQL and its various data types. 

Table Of Contents

  1. What is PostgreSQL?
  2. Key Features of PostgreSQL
  3. PostgreSQL Data Types
    1. Type #1: Numeric
    2. Type #2: Money
    3. Type #3: Character
    4. Type #4: Binary Data (bytea)
    5. Type #5: Date/Time
    6. Type #6: Boolean
    7. Type #7: Enumerated
    8. Type #8: Geometric
    9. Type #9: Network Address
    10. Type #10: Bit String
    11. Type #11: Text Search
    12. Type #12: UUID
    13. Type #13: XML
    14. Type #14: JSON
    15. Type #15: Arrays
    16. Type #16: Composite
    17. Type #17: Range
    18. Type #18: Object Identifier
    19. Type #19: Special Types
  4. Conclusion
  5. FAQs

What is PostgreSQL?

PostgreSQL is a powerful, open-source object-relational database system known for its robustness, scalability, and compliance with SQL standards. 

Developed by a global community of developers, PostgreSQL offers advanced features such as support for complex queries, foreign keys, triggers, views, transactions, and multi-version concurrency control (MVCC).

Key Features of PostgreSQL

The popularity of PostgreSQL as the DBMS powering business applications is mainly due to the following features:

Extensibility

PostgreSQL supports custom data types, operators, and functions, allowing developers to extend its functionality to meet specific needs.

SQL Compliance

PostgreSQL adheres to ANSI SQL standards, ensuring compatibility with other SQL-compliant systems.

ACID Compliance

A system using PostgreSQL ensures reliability in data transactions by strongly conforming to ACID principles – Atomicity, Consistency, Isolation, and Durability. 

This ensures data updates are always complete, consistent, and isolated from other operations, preventing errors and ensuring your information remains accurate.

Advanced-Data Types

PostgreSQL goes beyond the basics, offering a wide range of data types to store all sorts of information, from simple numbers and text to complex arrays, JSON documents, and even geometric data.

Concurrency and Performance

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to handle multiple users accessing the data simultaneously. It also provides tools to fine-tune and optimize the database for peak performance.

Now that you have a basic understanding of PostgreSQL, let us discuss various PostgreSQL data types.

PostgreSQL Data Types

PostgreSQL offers a wide range of data types to accommodate different data types. This range of data types ensures flexibility and functionality in database design and application development. 

Let us discuss the various data types available in PostgreSQL.

Type #1: Numeric

Numbers are the backbone of many applications, and PostgreSQL offers numerous data types to store them precisely and efficiently.

Integer

PostgreSQL supports three main integer data types to store whole numbers of varying ranges and sizes. Each type differs in its storage size and the range of values it can represent. 

The three main integer types are:

smallint

It is a PostgreSQL data type that stores small-range integer values. It requires 2 bytes of storage and can store values from -32,768 to 32,767. 

It is commonly used for fields that require less precision or have a limited range of possible values, such as age, count, or small identifiers.

integer (int)

integer is a numeric data type used to store medium-range integer values. It requires 4 bytes of storage and stores numbers from -2 billion to 2 billion. It is suitable for storing whole number needs, like customer IDs or social security numbers.

bigint

bigint is a numeric data type designed to store large integer values. It requires 8 bytes of storage and can represent values ranging from -9 quintillion to 9 quintillion. 

This data type is ideal for applications that handle massive numbers, such as large-scale financial calculations, and big data processing.

Serial Types (Auto-Incrementing Integer Types)

PostgreSQL offers special data types for auto-incrementing integer columns, commonly used for primary keys. These types are designed to generate unique identifiers automatically when a new row is added to the table.

The two kinds of serial types are:

serial

This is the default and most commonly used type. It stores a 4-byte integer and automatically increments by 1 for each new row insertion. 

bigserial

bigserial handles a significantly larger range of unique identifiers. It uses an 8-byte integer and starts auto-incrementing from 1.

Floating-Point Types

PostgreSQL provides two primary data types for storing floating-point numbers, each with different precision and storage requirements.

real

This is a more compact option, storing numbers in 4 bytes. It offers around 6 decimal digits of precision, making it suitable for situations where extreme accuracy isn’t critical like product ratings, or distances on a map.

double precision

double precision utilizes 8 bytes of storage and has a precision of approximately 15 decimal digits. This makes it ideal for financial calculations, engineering applications, or any scenario where precise decimal values are essential.

numeric/decimal 

PostgreSQL offers numeric/decimal types to store exact numeric values with user-defined precision, making it ideal for financial calculations or other high-precision applications. 

Unlike floating-point types that store numbers with an inherent margin of error, numeric types offer unparalleled precision for the most critical data.

The following table summarizes the PostgreSQL numeric data type.

postgresql numeric data type

Type #2: Money

While numeric data types excel at precise calculations, PostgreSQL offers an even more specialized option for financial data: the money type.

money type is a specialized data type that handles monetary values, ensuring precision and formatting suited for financial applications. 

The money type goes beyond just storing numbers. It supports a user-friendly currency format, including symbols and commas, making it easier to visualize and work with financial data within the database.

Type #3: Character

PostgreSQL provides several character data types for storing character (text) data, each with specific features and use cases. These types allow for the efficient storage and manipulation of string data. 

These character types include:

Fixed-Length Character (char(n))

char(n) stores a fixed number of characters (n), padding any empty spaces with blanks to reach the full length. 

It is ideal for short, well-defined data like product codes or to ensure consistent column widths in tables.

Variable-Length Character (varchar(n))

varchar(n) is a character data type used to store variable-length strings with a specified maximum length. Unlike fixed-length character types, varchar(n) stores text of varying lengths, up to a specified maximum (n).

Unlimited Text (text)

The text data type stores variable-length character strings without a specified maximum length. 

We recommend it for storing large blocks of text, such as descriptions, comments, articles, or any other type of content where the length is unpredictable or can be very large.

Type #4: Binary Data (bytea)

PostgreSQL provides a dedicated data type specifically for storing binary data. This type is useful for storing images, audio files, or other forms of binary-encoded information.

Type #5: Date/Time 

PostgreSQL provides a rich set of date and time data types, allowing for precise and flexible temporal data handling. These types support various formats and time zones, making them suitable for various applications. 

Some of the date/time types are:

timestamp [without time zone]

Stores both date and time, but without time zone information. This is useful for situations where time zone awareness isn’t crucial.

timestamp with time zone

This stores both date and time along with time zone information. This is essential for handling data across different time zones, such as tracking user activity or managing geographically distributed systems.

date

This data type stores the year, month, and day. It is perfect for scenarios where the time of day isn’t relevant.

time [without time zone]

This type captures the time of day (hours, minutes, seconds) without a date or time zone. It is useful for recording event durations or tracking business hours.

time with time zone

It captures the time of day but includes the time zone information. This is valuable for storing timings relevant to specific locations, like flight schedules or meeting times.

interval

This type represents a span of time between two points. It can express durations in years, months, days, hours, minutes, seconds, and even milliseconds. 

Intervals are perfect for calculating elapsed time, time differences, or representing durations.

The following table summarizes the date and time data types in PostgreSQL.

postgresql date and time data types

Type #6: Boolean

PostgreSQL includes a boolean data type for storing true/false values. This type is essential for representing binary states or conditions, making it a fundamental part of many database schemas.

Type #7: Enumerated

PostgreSQL supports an enumerated (enum) type that allows for defining a set of named values. This type represents a predefined list of possible values, ensuring data integrity and readability.

Type #8: Geometric

PostgreSQL offers a range of geometric data types to handle various geometric shapes and structures. These types are useful for spatial data applications, such as geographic information systems (GIS), computer-aided design (CAD), and other fields requiring spatial computations.

Some of the geometric types are:

point

This fundamental type represents a single location in space, defined by its coordinates (X, Y). It serves as the basis for building more complex geometric shapes.

line

A line segment connects two points, representing a linear path in space.

lseg (line segment)

It is a part of a line bounded by two distinct endpoints.

box

This type represents a rectangular area, defined by its opposite corners. It is useful for defining spatial extents or bounding boxes for other geometric objects.

path

A path is a sequence of connected straight-line segments or curves. It can be open (no connection between the first and last points) or closed (forming a complete loop). 

polygon

This type represents a closed shape formed by a connected sequence of straight-line segments. Polygons are ideal for storing and manipulating two-dimensional regions like countries, buildings, or any defined area.

circle

circle type represents a perfect circle, defined by its center point and radius. It’s useful for storing locations with a circular area of influence, like signal coverage zones or areas serviced by a facility.

The following table summarizes the geometric data types in PostgreSQL. 

postgrsql geometric data types

Type #9: Network Address 

PostgreSQL provides several network address types to handle IP addresses and subnet masks, which are useful in network management and applications dealing with network data. These types support storage, manipulation, and querying of network addresses.

Some of the network address types are:

cidr

cidr is specifically designed to store IPv4 or IPv6 network addresses. It combines the IP address with a subnet mask, allowing users to represent entire subnets within the database.

inet (Internet)

inet deals with individual IPv4 or IPv6 host addresses. It stores a single IP address, making it suitable for situations where users need to track specific devices or identify unique hosts within the network.

macaddr

macaddr stores MAC addresses, a unique identifier assigned to network interfaces (like network cards). It’s useful for tracking specific hardware devices within the network or managing access control based on MAC addresses.

Type #10: Bit String

PostgreSQL provides bit string types for storing bit masks and sequences of binary digits. These types are useful for applications that require manipulating individual bits, such as flag sets, binary data, and bitwise operations.

bit(n)

bit(n) is a fixed-length bit string. This type ensures a consistent size for the data and is useful for situations where users know the precise number of bits that need to be worked with.

bit varying(n) or varbit

bit varying(n) or varbit is more flexible than bit(n). It is ideal where the number of bits differs for each data entry.

Type #11: Text Search

PostgreSQL provides advanced text search capabilities through specific data types designed for full-text search operations. 

These types enable efficient storage, indexing, and querying of text data, making them highly suitable for applications that require powerful text search functionalities.

tsquery 

The tsquery type represents the actual search query users want to use. One can construct queries using keywords, phrases, and even boolean operators (AND, OR, NOT) to define what one looks for within the text documents.

Type #12: UUID

PostgreSQL provides a UUID (Universally Unique Identifier) type for storing 128-bit unique identifiers. 

UUIDs ensure global uniqueness for identifiers, making them suitable for distributed systems, database records, and other applications where unique identification is crucial.

Type #13: XML 

PostgreSQL provides an XML data type for storing XML data in a table. 

This type supports storing, querying, and manipulating XML documents and fragments, making it suitable for applications that manage XML content within a relational database.

Type #14: JSON 

PostgreSQL provides robust support for storing, querying, and manipulating JSON data through two data types: json and jsonb. 

These types are ideal for applications that require flexible, schema-less data storage and efficient querying capabilities.

json

This type stores JSON data in its plain text format, exactly as the user would write it. It’s familiar and easy to read for humans, making it suitable for scenarios where users want to directly inspect or edit the JSON data within the database.

jsonb

jsonb stores JSON data in a binary, decomposed format. It is not human-readable, but it offers significant advantages like faster storage and retrieval, and indexing capabilities.

Type #15: Arrays

PostgreSQL supports arrays, which allow users to store multiple values in a single field. Arrays can be of any data type, providing a convenient way to store and query sets of values within a single column. The typical statement syntax for creating array type data type is as follows:

mysql> CREATE TABLE example ( names text[] );

Type #16: Composite 

PostgreSQL supports composite types that allow users to create custom data types composed of multiple fields. Composite types can be used in tables, functions, and column type entries to store structured data in a single column.

To create a composite type, specify a name for the composite type and list the fields it will contain, along with their respective data types. The typical syntax of the statement is as follows:

mysql> CREATE TYPE complex AS ( r DOUBLE PRECISION, -- Field for a real                 

              number i DOUBLE PRECISION -- Field for another real number );

Type #17: Range

PostgreSQL supports range types, allowing users to store value ranges in a single column. This is useful for representing data that spans an interval, such as dates, times, or numbers.

int4range and int8range

These types represent ranges of integer values, ideal for storing things like product IDs within a specific range or tracking data points falling between certain integer limits.

numrange

This type is designed for numeric ranges, allowing users to store a continuous set of decimal values. It’s useful for financial applications that track price ranges or budget limitations.

tsrange and tstzrange

These types are built for storing ranges of timestamps. tsrange deals with timestamps without time zones, while tstzrange includes time zone information. They are perfect for tracking activity logs within a specific time frame or managing events that occur over a defined period.

daterange

This type focuses specifically on date ranges, allowing you to represent a span of days. It’s valuable for applications dealing with reservations, booking systems, or any scenario where date ranges play a crucial role. 

Type #18: Object Identifier

Object identifiers (OIDs) in PostgreSQL are included in several specific data types used to identify various database objects uniquely. 

These data types are primarily used in system catalogs and for referencing system objects. 

The following table summarizes the data types that include object identifiers and the storage size description.

object identifiers in postgresql

Type #19: Special Types

PostgreSQL includes several special data types designed for specific use cases. These types are optimized for particular kinds of data, enhancing performance and functionality for those applications.

The following table summarizes the PostgreSQL special data types.

postgresql special data types

Conclusion

PostgreSQL, a powerful relational database management system, offers a comprehensive suite of built-in data types that cater to various application needs. These common data types, such as integers, text, and timestamps, are essential for database development. 

Beyond these, PostgreSQL also includes additional types like geometric, network address, and JSON types, enhancing flexibility and functionality. This robust selection of data types makes PostgreSQL an adaptable and efficient choice for developers and businesses seeking a reliable database solution. 

With its extensive range of data types and ongoing community-driven enhancements, PostgreSQL remains a leading choice for managing complex and diverse data sets. 

FAQs

Q. What is the numeric data type in PostgreSQL?

The numeric data type in PostgreSQL stores exact numeric values with arbitrary precision. It is particularly useful for applications that require high-precision calculations, such as financial applications.

Q. What are pseudo-types in PostgreSQL?

Pseudo-types are special types in PostgreSQL that are not actual data types but can be used in function definitions to provide flexibility. Examples include any, anyelement, and void. They help create polymorphic functions that can handle multiple data types.

Q. How is the bigint data type used in PostgreSQL?

The bigint data type stores large integer values that exceed the range of the standard integer type. It can store values from -9223372036854775808 to 9223372036854775807, making it suitable for applications requiring large numerical values.

Q. What are character strings in PostgreSQL, and how are they used?

Character strings in PostgreSQL include data types such as char, varchar, and text. These types are used to store text data of varying lengths. char is for fixed-length strings, varchar for variable-length strings with a length limit, and text for variable-length strings without a length limit.

Q. How does PostgreSQL handle XML format data?

PostgreSQL provides the XML Data type to store XML data. This type supports various XML-related operations, such as validating, querying, and transforming XML documents, making it suitable for applications that store and manipulate XML data.

Q. What is a monetary data type in PostgreSQL?

The monetary data type in PostgreSQL is represented as money that stores currency values. It is designed to handle monetary amounts with a fixed fractional precision, simplifying financial calculations and ensuring accurate representation of currency values.

Q. What are built-in range types in PostgreSQL?

Built-in range types in PostgreSQL include int4range, int8range, numrange, tsrange, tstzrange, and daterange. These types represent continuous spans of values, such as date or numeric ranges, allowing for efficient querying and manipulation of range data.

Q. How are floating-point data types used in PostgreSQL?

Floating-point data types in PostgreSQL include real and double precision. These types are used to store approximate numeric values with varying levels of precision and are suitable for scientific and engineering applications that require floating-point arithmetic.

Q. What special values can be stored in PostgreSQL data types?

Special values in PostgreSQL data types include NULL (indicating the absence of a value), infinity (+Infinity and -Infinity for numeric types), and special date/time values such as epoch, infinity, and -infinity. These values help represent exceptional or undefined conditions in the database.

Try this guide with our instant dedicated server for as low as 40 Euros