PostgreSQL data types: Complete guide with examples

Understanding PostgreSQL data types is essential for anyone working with PostgreSQL databases: developers, DBAs, and data analysts. A strictly defined system of the supported data types ensures data integrity across different database instances and application compatibility. In this article, we explore PostgreSQL data types with examples that demonstrate their use and analyze the best practices for choosing the appropriate PostgreSQL data types in database management.

What are PostgreSQL data types?

PostgreSQL data types define the values that can be stored in database tables. They determine the format, accepted value range, and storage requirements for the data. In a PostgreSQL table, each column is assigned a certain data type, which defines all values that can be stored in the column.

PostgreSQL provides a number of native data types, for example, BOOLEAN, CHARACTER, NUMERIC, DATE/TIME, and others. In addition, users can create new data types as needed using the CREATE TYPE statement. Most PostgreSQL tools support the native set of data types and the creation of user-defined ones.

Why are PostgreSQL data types important?

Choosing the correct data types for PostgreSQL tables helps you:

• Maintain data integrity
• Improve query performance and index efficiency
• Leverage PostgreSQL's built-in functions
• Reduce the required storage
• Improve your database schema maintainability

For example, you can store dates as either DATE or TEXT; however, using the correct data type—DATE—enables filtering, sorting, and calculations.

Data types supported by PostgreSQL

• Numeric data types
• Character data types
• Date and time data types
• Boolean, UUID, and binary data types
• JSON data types
• Array and enum data types
• Network and full-text search data types
• Bit string data types
• XML data type
• Advanced data types
• Object Identifier types (OIDs)
• pg_lsn data type

Let's look closer at each data type to understand its specifics and use cases.

Numeric data types

Numeric types are used to store numerical data, such as numbers, quantities, prices, percentages, and other similar values. PostgreSQL supports the following numeric data types:

The SQL example below uses three numeric data types.

• INTEGER to define quantities
• NUMERIC to define prices
• REAL to define the discount

This example demonstrates how PostgreSQL numeric data types can be used in real-life situations to store different data and enable calculations in SQL.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INTEGER,
    unit_price NUMERIC(10,2),
    discount REAL
);

INSERT INTO products (product_name, quantity, unit_price, discount)
VALUES ('Wireless Mouse', 50, 29.99, 0.10);

SELECT
    product_name,
    quantity,
    unit_price,
    quantity * unit_price AS total_value
FROM products
WHERE unit_price > 20.00;

Character data types

Character data types are used to store text: names, addresses, descriptions, email addresses, and other strings of characters. They may consist of letters, numbers, symbols, and whitespace.

The following example uses all three character data types.

• CHAR(2) to store country codes consisting of two characters
• VARCHAR(50) to store first and last names, which must not exceed 50 characters
• TEXT to store notes that can be of any length

CREATE TABLE employees (
    employee_id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    country_code CHAR(2),
    notes TEXT
);

Date and time data types

Date and time data types store dates, times, timestamps, and intervals. They are commonly used to track events, schedule activities, and calculate durations. Dates are stored in the standard PostgreSQL date format.

The following SQL creates an appointments table using different PostgreSQL date and time types to define time-related values.

• DATE to store the appointment date
• TIME to store the appointment time
• TIMESTAMPTZ to store the appointment creation time as an absolute point in time
• INTERVAL to store the appointment duration

CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_name VARCHAR(100),
    appointment_date DATE,
    appointment_time TIME,
    created_at TIMESTAMPTZ,
    duration INTERVAL
);

Boolean, UUID, and binary data types

In PostgreSQL, boolean data types store logical values, UUID data types store unique identifiers, and binary data types store binary data.

For a real-life example of how these data types are used, let's look at the following SQL. It creates a table that stores documents as files and uses all three data types.

• UUID to store a document ID
• BYTEA to store data contained in the file
• BOOLEAN to define whether the document is encrypted or not

CREATE TABLE documents (
    document_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_name VARCHAR(100),
    file_data BYTEA,
    is_encrypted BOOLEAN
);

JSON data types

In PostgreSQL, JSON and JSONB data types are used to store JSON data. The difference between them is that JSON stores data as plain text, while JSONB stores JSON in a binary format, which may facilitate processing.

Another JSON-related data type is jsonpath, which stores JSON path expressions—instructions that describe how to navigate and search through JSON data.

In real life, the usage of JSON and JSONB data types is very similar. For example, the following SQL can use either JSON or JSONB.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    details JSONB
);

INSERT INTO products (details)
VALUES (
    '{
        "name": "Wireless Mouse",
        "price": 29.99,
        "brand": "Elmer578",
        "in_stock": true
    }'
);

However, internally, the data is stored differently. JSON stores data exactly as it is entered, while JSONB does not preserve whitespace, duplicate keys, or the key order. As a result, JSONB is the preferred data type for storing JSON data, as it supports efficient filtering when combined with appropriate indexes, such as GIN indexes, which can help locate matching rows without scanning the entire table.

Array and enum data types

ARRAY and ENUM data types are special data types natively supported by PostgreSQL. ARRAY data types allow storing multiple values of the same type in a single column; ENUM data types restrict a column to a predefined set of values.

The following example demonstrates the use of the ARRAY and ENUM data types. It creates a table that stores tasks with their names, statuses, and tags. The task statuses belong to a predefined list represented as an ENUM data type, while the tags are stored as an ARRAY of text values associated with each task.

CREATE TYPE task_status AS ENUM (
    'Not Started',
    'In Progress',
    'Completed'
);

CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR(100),
    status task_status,
    tags TEXT[]
);

INSERT INTO tasks (task_name, status, tags)
VALUES (
    'Update product documentation',
    'In Progress',
    ARRAY['Documentation', 'PostgreSQL', 'Release']
);

SELECT
    task_name,
    status,
    tags
FROM tasks
WHERE status = 'In Progress'
  AND 'PostgreSQL' = ANY(tags);

Network and full-text search data types

Network data types are used to store network-related information, such as IP addresses, MAC addresses, and network ranges.

The following SQL is an example of how network data types can be used to store network device information.

• INET to store the device's IP address
• CIDR to store the network to which the device belongs
• MACADDR to store the device's physical network interface address

CREATE TABLE network_devices (
    device_id SERIAL PRIMARY KEY,
    hostname VARCHAR(100),
    ip_address INET,
    subnet CIDR,
    mac_address MACADDR
);

INSERT INTO network_devices (
    hostname,
    ip_address,
    subnet,
    mac_address
)
VALUES (
    'router-01',
    '192.168.1.10',
    '192.168.1.0/24',
    '08:00:2b:01:02:03'
);

SELECT
    hostname,
    ip_address,
    subnet,
    mac_address
FROM network_devices
WHERE ip_address << subnet;

Full-text search data types store searchable text. While the common LIKE operator searches strings character by character, full-text search recognizes complete words. Besides, it removes words like "the" or "and" and can match different forms of the same word.

The following example uses the TSVECTOR data type to create a full-text search system. The article title and body are stored as PostgreSQL TEXT values and automatically normalized using the to_tsvector() function in a generated column. A GIN index is created on the TSVECTOR column to improve full-text search performance. This example requires PostgreSQL 12 or later because it uses generated columns. The to_tsquery() function creates a search query using PostgreSQL's query syntax. For raw user input, use plainto_tsquery() or websearch_to_tsquery() instead, as they handle plain text input without requiring operators.

CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        to_tsvector(
            'english',
            coalesce(title, '') || ' ' || coalesce(body, '')
        )
    ) STORED
);

CREATE INDEX idx_articles_search_vector
ON articles
USING GIN (search_vector);

INSERT INTO articles (title, body)
VALUES (
    'Introduction to PostgreSQL',
    'Learn about PostgreSQL data types and indexing.'
);

SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & indexing');

Bit string data types

Bit strings are strings of 0's and 1's used to store and manipulate data at the individual-bit level, rather than storing values as integers or text. PostgreSQL supports two bit string data types: BIT(n) and BIT VARYING(n).

As an example of BIT(n) use, let's analyze the following query that defines application user permissions. The application has five permissions that can be enabled or disabled for a particular user with corresponding boolean values:

• Can/Cannot read
• Can/Cannot write
• Can/Cannot delete
• Can/Cannot export
• Can/Cannot administer

This collection can be stored as follows.

CREATE TABLE users (
    user_id INTEGER,
    permissions BIT(5)
);

A user may have, for example, the following set of permissions: 10011, where each bit represents whether a particular permission is enabled.

For an example of how BIT VARYING(n) can be used, let's look at the following SQL that creates a table storing messages with their payload.

CREATE TABLE messages (
    id BIGSERIAL PRIMARY KEY,
    payload BIT VARYING(1024)
);

This table can store messages with payload values of 101101, 101101001011, or 10110100101111001010, which will all be valid as long as their length does not exceed 1024 bits.

XML data type

In PostgreSQL, the XML data type is used to store well-formed XML documents or XML content.

While in many cases, XML values can be stored as TEXT or JSONB data, XML is useful for applications integrated with systems that communicate data in XML format, such as external ordering or inventory systems. For example, the following SQL creates a product catalog using the XML data type.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_data XML
);

This table can then accept data in the XML format.

INSERT INTO products (product_id, product_data)
VALUES (
    1,
    '<product>
        <name>Laptop</name>
        <category>Electronics</category>
        <price>1200</price>
    </product>'
);

Advanced data types

In addition to the standard data types, PostgreSQL offers several advanced data types for modeling complex data structures and enforcing business rules.

The following SQL example models an order management system using some of the advanced data types.

• DOMAIN to ensure that prices are positive values
• COMPOSITE to store addresses consisting of several fields
• USER-DEFINED to create a custom ENUM type that stores the valid order status values
• GEOMETRIC to store a warehouse's location on the second floor plan using the POINT type
• RANGE to store the delivery window
• MULTIRANGE to store multiple maintenance windows when deliveries are not possible

-- Create a domain type
CREATE DOMAIN positive_price
AS NUMERIC(10,2)
CHECK (VALUE > 0);

-- Create a composite type
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    postal_code TEXT
);

-- Create an enum type
CREATE TYPE order_status AS ENUM (
    'Pending',
    'Processing',
    'Shipped',
    'Delivered'
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,

    -- User-defined ENUM
    status order_status,

    -- DOMAIN
    total_price positive_price,

    -- COMPOSITE
    shipping_address address,

    -- GEOMETRIC
    warehouse_location POINT,

    -- RANGE
    delivery_window TSRANGE,

    -- MULTIRANGE
    maintenance_windows TSMULTIRANGE
);

INSERT INTO orders (
    status,
    total_price,
    shipping_address,
    warehouse_location,
    delivery_window,
    maintenance_windows
)
VALUES (
    'Processing',
    149.99,
    ROW(
        '123 Main Street',
        'New York',
        '10001'
    ),
    POINT(15, 25),
    tsrange(
        '2026-04-20 09:00',
        '2026-04-20 17:00'
    ),
    tsmultirange(
        tsrange('2026-04-18 08:00', '2026-04-18 10:00'),
        tsrange('2026-04-19 14:00', '2026-04-19 16:00')
    )
);

SELECT
    order_id,
    status,
    total_price,
    (shipping_address).city AS city,
    warehouse_location,
    delivery_window
FROM orders
WHERE delivery_window @> TIMESTAMP '2026-04-20 10:30';

Object Identifier types (OIDs)

OID is a PostgreSQL-specific, unsigned 4-byte integer type used internally to identify database objects. It is a numeric identifier assigned to objects inside PostgreSQL, such as tables, types, functions, and other system objects.

As a real-world example of OID use, let's look at the following SQL. It retrieves the OID of the film table from the pg_class system catalog.

SELECT
    oid,
    relname
FROM pg_class
WHERE relname = 'film';

The result contains the OID for the requested table.

   oid | relname
-------+-----------
 33499 | film

You can use the OID to retrieve other internal details of the table.

SELECT *
FROM pg_class
WHERE oid = 33499;

pg_lsn data type

In PostgreSQL, pg_lsn is a specialized data type used to represent a Log Sequence Number (LSN). An LSN identifies a specific position in PostgreSQL's Write-Ahead Log (WAL), where changes are written before they make it to the actual database.

The pg_lsn data type is useful in cases when you need to compare LSN values, for example, in monitoring database replications. The following SQL allows you to calculate the number of WAL bytes between the primary and standby PostgreSQL instances during replication to know how far behind the standby is.

SELECT
    '16/B374D848'::pg_lsn -
    '16/B3700000'::pg_lsn;

How dbForge Studio for PostgreSQL helps you work with data types

Choosing the right data types is an essential step in building efficient, high-performance databases. However, database design begins with choosing the right tools. dbForge Studio for PostgreSQL, with its convenient user interface, a variety of features that simplify database design and development, intelligent SQL editing, and PostgreSQL formatter, represents a comprehensive PostgreSQL IDE, where you can create, modify, and manage Postgres data types, design schemas, and develop SQL code with ease.

In dbForge Studio, you can explore the table structure in a visual Data Editor with all data types clearly visible. When you open a table in the Editor, it is shown as a grid, where each column is assigned a specific data type.

When needed, you can modify data types in the Table Editor, selecting from an extensive list of supported ones or creating user-defined data types. dbForge Studio enables table editing directly in the grid without writing SQL.

Similarly, you can choose the appropriate data types for table columns when creating new tables. Just choose the required type from the drop-down list, and it will be assigned to the new column.

While dbForge Studio for PostgreSQL allows you to design and modify tables without writing code, it automatically generates the corresponding SQL as you work. Whenever you add, modify, or remove a column, change its data type, or alter other table properties, the Studio updates the SQL in real time.

When you are done editing, you can always review your changes before committing them. If everything is correct, click Apply Changes to save your updates. Otherwise, continue editing or discard your changes at any time.