PostgreSQL Data Types: Complete Guide with Examples

When working with any relational database management system (RDBMS), understanding data types is crucial. It ensures the correct representation of data and optimizes the system’s performance. PostgreSQL, a powerful, open-source RDBMS, provides a comprehensive suite of data types. This article will deep dive into PostgreSQL’s data types, explain their usage, compare similar ones, and demonstrate SQL examples for each. When creating or updating your schema, we recommend using pgAdmin for a streamlined experience.

Numeric Data Types

Integer Types

PostgreSQL offers three distinct integer types: Smallint, Integer, and Bigint. These differ in their storage size and value range.

Numeric data types are often used when you need to store numerical data and perform mathematical operations on that data.

For instance, imagine you’re developing an e-commerce application and need to create a products table in your PostgreSQL database. Each product in your store has a price, and you’ll likely perform calculations such as totaling the cost of items in a user’s shopping cart or applying discounts. This is a clear scenario where you would use a numeric data type.

You might use the DECIMAL or NUMERIC type to store the product prices because these types can handle exact precision, which is essential when dealing with monetary values to avoid rounding errors. Let’s dig into the various numeric data types…

• Smallint: With 2 bytes of storage, Smallint accommodates values from -32768 to +32767. Use this type when dealing with a small range of numbers.

CREATE TABLE test_smallint (id SMALLINT);

• Integer: The default choice for integer values. Integer uses 4 bytes of storage and can handle values from -2147483648 to +2147483647.

CREATE TABLE test_integer (id INTEGER);

• Bigint: Bigint, using 8 bytes, stores values from -9223372036854775808 to +9223372036854775807. Use this when your data exceeds the Integer range.

CREATE TABLE test_bigint (id BIGINT);

Remember, choose the right integer type based on the expected range of values, balancing between storage efficiency and data integrity.

Floating-Point Numbers

PostgreSQL offers two floating-point number data types: Float and Double Precision.

• Float: Also known as “single precision,” Float occupies 4 bytes of storage. It is suitable for most numerical data.

CREATE TABLE test_float (value FLOAT);

• Double Precision: This “double precision” type uses 8 bytes and offers more precision compared to Float. Use this when high precision is required.

CREATE TABLE test_double (value DOUBLE PRECISION);

Numeric or Decimal

Numeric or Decimal types provide exact precision, ideal for calculations demanding high precision, like financial data. The storage size for this data type is variable.

CREATE TABLE test_numeric (price NUMERIC(5, 2));

Text Data Types

PostgreSQL offers several text data types, each suitable for different scenarios.

Char(n), Varchar(n), and Text

• Char(n): A fixed-length character string padded with spaces as needed. Ideal for storing fixed-length strings like identifiers.

CREATE TABLE test_char (identifier CHAR(10));

• Varchar(n): Represents a variable-length string. Unlike Char(n), Varchar(n) isn’t padded with spaces.

CREATE TABLE test_varchar (username VARCHAR(50));

• Text: When you don’t want to limit the string length, Text is the perfect choice. There’s no notable performance difference between Varchar(n) and Text.

CREATE TABLE test_text (description TEXT);

Boolean Data Types

In PostgreSQL, the Boolean data type is used to represent logical values of either true or false. It is commonly used to store binary or Boolean information in database columns.

For example, let’s consider a table named “Tasks” that tracks the completion status of various tasks. The table has columns such as “task_id” (integer) and “is_completed” (boolean).

To insert a new task into the table, we can use the following SQL query:

INSERT INTO Tasks (task_id, is_completed)
VALUES (1, true);

This query adds a task with an ID of 1 and sets its completion status to true.

To retrieve all completed tasks, we can use a SELECT statement:

SELECT *
FROM Tasks
WHERE is_completed = true;

This query will return all tasks that have been marked as completed.

Boolean data types in PostgreSQL allow for efficient storage and querying of binary information, making it straightforward to represent and work with true/false values in a database. They are often used in scenarios where there is a need to track states, flags, or conditions that have binary outcomes.

Array Data Types

In PostgreSQL, array data types provide a powerful way to store and manipulate collections of values within a single column. Arrays can hold values of any data type, including built-in types, user-defined types, and even other arrays. They offer flexibility and convenience when dealing with structured data sets.

For example, let’s consider a table called “Students” with a column named “grades” that stores an array of numeric values representing the grades of each student:

CREATE TABLE Students (
    student_id serial PRIMARY KEY,
    name text,
    grades integer[]
);

In this example, the “grades” column is defined as an array of integers using the integer[] data type.

To insert a new student record into the table with their respective grades, we can use the following SQL query:

INSERT INTO Students (name, grades)
VALUES ('John Doe', ARRAY[85, 92, 78]);

This query adds a student named “John Doe” with three grades: 85, 92, and 78.

We can also perform various operations on array data, such as querying, updating, and aggregating elements within an array column. For instance, we can retrieve all students with a grade of 90 or above:

SELECT *
FROM Students
WHERE 90 = ANY(grades);

This query returns all students who have at least one grade equal to or greater than 90.

By utilizing array data types in PostgreSQL, we can efficiently store and work with collections of related values within a single column, simplifying data management and enabling powerful array-specific operations and functions.

JSON Data Types

PostgreSQL offers robust support for JSON, enabling you to store JSON data and perform complex queries on it. It provides two JSON data types: JSON and JSONB.

The JSON type stores JSON data as strings in their original format, preserving whitespace and the order of object keys. It’s useful when you need to store JSON data exactly as it was inputted, but it lacks indexing capabilities.

On the other hand, the JSONB type stores JSON data in a decomposed binary format, enabling advanced processing capabilities, such as indexing and more complex queries. While JSONB does not preserve whitespace or the order of object keys, its features generally make it the more powerful option for most applications.

Here’s an example of a PostgreSQL command creating a table with a JSONB column:

CREATE TABLE customer_data (id INTEGER, data JSONB);

In this command, data is a column that can store JSONB data type, allowing for complex, structured data associated with a customer to be stored and queried.

Date and Time Data Types

In PostgreSQL, the date and time data types are powerful tools for handling temporal data. Let’s consider an example to better understand their usage.

Suppose we have a table called “Orders” that tracks customer orders in an e-commerce system. The table has columns such as “order_id” (integer), “order_date” (date), and “order_time” (time).

To insert a new order into the table, we can use the following SQL query:

INSERT INTO Orders (order_id, order_date, order_time)
VALUES (1, '2023-05-28', '15:45:00');

This query adds an order with an ID of 1, placed on May 28, 2023, at 3:45 PM.

To retrieve all orders placed on a specific date, we can use a SELECT statement:

SELECT *
FROM Orders
WHERE order_date = '2023-05-28';

This query will return all orders placed on May 28, 2023.

Furthermore, we can perform various operations on date and time values, such as calculating the time difference between orders or finding the maximum or minimum date in the table.

By leveraging the date and time data types in PostgreSQL, developers can effectively manage and manipulate temporal information in their databases, enabling accurate tracking and analysis of time-related data.

Enum Data Type

PostgreSQL provides the Enum type as a useful feature for defining enumerated (or “enum”) data types. Enum types allow you to define a set of named values that a column can take, providing a clear and constrained set of options.

For example, let’s consider a scenario where we have a table named “Employees” with a column called “employment_status” that represents the status of an employee’s employment. We can define an enum type called “EmploymentStatus” with possible values such as ‘Full-time’, ‘Part-time’, and ‘Contract’.

To create an enum type in PostgreSQL, you can use the CREATE TYPE statement:

CREATE TYPE EmploymentStatus AS ENUM ('Full-time', 'Part-time', 'Contract');

Once the enum type is defined, you can use it in a table definition:

CREATE TABLE Employees (
    employee_id serial PRIMARY KEY,
    name text,
    employment_status EmploymentStatus
);

Now, the “employment_status” column can only store one of the predefined enum values, ensuring data integrity and making it easier to query and filter by employment status.

Using enum types in PostgreSQL provides a structured and efficient way to handle a limited set of possible values, enhancing data consistency and readability in your database schema.

Binary Data Type

In PostgreSQL, binary data types provide a means to store and manipulate binary data, such as images, audio files, or serialized objects. These data types, namely bytea and varbinary, allow for efficient storage and retrieval of binary data within the database.

For example, let’s say we have a table called “Images” that stores images uploaded by users. The table has columns like “image_id” (integer) and “image_data” (bytea) to hold the binary image data.

To insert an image into the table, we can use the following SQL query:

INSERT INTO Images (image_id, image_data)
VALUES (1, E'\\x89504E470D0A1A0A0000000D49484452...');

In this example, we use hexadecimal notation (prefixed with \\x) to represent the binary image data.

To retrieve an image from the table, we can use a SELECT statement:

SELECT image_data
FROM Images
WHERE image_id = 1;

This query fetches the binary image data associated with the image ID 1.

By utilizing binary data types, PostgreSQL enables efficient storage and retrieval of binary content, making it suitable for a wide range of applications that require handling binary data alongside structured information.

Network Address Data Type

In PostgreSQL, network address data types are designed to handle IP addresses and network ranges, allowing for efficient storage and manipulation of network-related information. The primary data types for network addresses in PostgreSQL are inet and cidr.

For example, let’s consider a scenario where we have a table named “Devices” that stores information about network devices in an organization. The table has columns such as “device_id” (integer) and “ip_address” (inet) to store the IP addresses of the devices.

To insert a device record into the table, we can use the following SQL query:

INSERT INTO Devices (device_id, ip_address)
VALUES (1, '192.168.0.100');

This query adds a device with an ID of 1 and an IP address of ‘192.168.0.100’.

To query devices within a specific IP address range, we can use a SELECT statement with the inet data type:

SELECT *
FROM Devices
WHERE ip_address BETWEEN '192.168.0.0' AND '192.168.0.255';

This query retrieves all devices with IP addresses falling within the range of ‘192.168.0.0’ to ‘192.168.0.255’.

By utilizing the network address data types, PostgreSQL enables efficient storage, querying, and manipulation of IP addresses and network ranges, making it well-suited for network management and related applications.

UUID Data Type

In PostgreSQL, the UUID (Universally Unique Identifier) data type allows for the storage of unique identifiers that are generated using various algorithms. The UUID data type is commonly used when there is a need for globally unique identifiers that can be generated independently of a centralized authority.

For example, let’s consider a table named “Users” that stores user information, including a UUID as the primary key for each user:

CREATE TABLE Users (
    user_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

In this example, the user_id column is defined as a UUID data type, and the uuid_generate_v4() function is used to generate a random UUID as the default value.

To insert a new user into the table, we don’t need to specify the UUID explicitly:

INSERT INTO Users (name, email)
VALUES ('John Doe', 'john.doe@example.com');

PostgreSQL automatically generates a UUID for the user_id column.

UUIDs offer the advantage of being globally unique, even across different systems, making them suitable for distributed and replicated environments. They provide a reliable way to identify entities without requiring a centralized authority for issuing unique identifiers.

Data Type Conversion Using CAST()

In PostgreSQL, the CAST function allows for data type conversions, enabling the transformation of values from one data type to another. It provides a flexible way to handle data type conversions within SQL queries.

For example, let’s assume we have a table called “Products” with a column named “price” defined as a numeric data type. We want to retrieve the prices of the products as floating-point numbers for further calculations.

We can use the CAST function to convert the numeric values to float:

SELECT CAST(price AS float) AS price_float
FROM Products;

This query converts the “price” column values from numeric to float and aliases the converted values as “price_float” in the result set.

Similarly, CAST can be used to convert values between other compatible data types, such as converting a timestamp to a date, or a varchar to an integer.

By utilizing the CAST function, PostgreSQL allows for seamless data type conversions within SQL queries, enabling developers to manipulate and transform data as needed for their specific requirements.

User-Defined Data Types

In PostgreSQL, user-defined data types offer the ability to create custom data types based on existing data types or composite types. This feature allows developers to define their own domain-specific data structures and enforce data integrity and consistency.

For example, let’s say we want to create a user-defined data type called “PhoneNumber” to represent phone numbers in a specific format. We can define this custom data type as follows:

CREATE TYPE PhoneNumber AS (
    area_code text,
    number text,
    extension text
);

In this example, the “PhoneNumber” data type consists of three fields: “area_code,” “number,” and “extension,” each defined as text.

We can then use the custom data type in a table definition, like this:

CREATE TABLE Customers (
    customer_id serial PRIMARY KEY,
    name text,
    phone PhoneNumber
);

Now, the “phone” column in the “Customers” table will only accept values that conform to the structure defined by the “PhoneNumber” data type.

Using user-defined data types in PostgreSQL allows for more expressive and self-descriptive schemas. It enhances data consistency and simplifies data management by encapsulating domain-specific rules within the custom types.

Choosing the Right PostgreSQL Data Type

When working with PostgreSQL, choosing the appropriate data type for each column is crucial for efficient data storage, retrieval, and query performance. Consider the following factors when selecting the right PostgreSQL data type:

1. Data Integrity: Use data types that enforce data integrity and validity. For example, use integer for whole numbers, date for dates, and boolean for true/false values. This ensures that the stored data adheres to the expected format and constraints.
2. Storage Efficiency: Optimize storage by selecting the most space-efficient data type that satisfies your data requirements. For instance, consider using smallint instead of integer for smaller ranges, or varchar with an appropriate length for variable-length strings.
3. Query and Index Performance: Choose data types that align with the types of operations and queries performed on the data. For example, using the jsonb type for structured JSON data allows for efficient querying and indexing.
4. Compatibility and Interoperability: Consider compatibility with other systems or programming languages. PostgreSQL offers compatibility with various data types, such as uuid, which is widely supported across different platforms.
5. Future Flexibility: Anticipate future data needs and choose data types that provide room for growth. Selecting broader types like text or numeric instead of fixed-length types allows for accommodating varied data sizes and precision.
6. Domain-Specific Requirements: Consider domain-specific data types or create user-defined types to represent complex concepts. This can improve data consistency, readability, and maintainability.

Understanding the characteristics of PostgreSQL data types and their suitability for the specific data being stored will help ensure efficient storage, retrieval, and processing of data within your PostgreSQL database.

I hope this article was helpful, please provide your comments below!

Frequently Asked Questions (FAQ)