Skip to main content

PostgreSQL Composite Types

Introduction​

PostgreSQL offers a rich variety of data types, going beyond the basic ones found in most database systems. One of the most powerful features is composite types, which allow you to define custom data structures that group multiple fields together as a single unit.

Think of composite types as similar to structs in C, classes in object-oriented programming, or records in other database systems. They enable you to create more complex and meaningful data models that closely match your application's domain.

In this tutorial, we'll explore how to define, use, and manipulate composite types in PostgreSQL, along with practical examples that demonstrate their benefits in real-world scenarios.

Creating Composite Types​

A composite type is defined using the CREATE TYPE command. The syntax is straightforward:

sql
CREATE TYPE type_name AS (
    attribute1 data_type1,
    attribute2 data_type2,
    ...
);

Let's create a simple example. Imagine we're building an application that needs to store address information:

sql
CREATE TYPE address AS (
    street_address varchar(100),
    city varchar(50),
    state varchar(25),
    postal_code varchar(15),
    country varchar(50)
);

Now we've created a custom type called address that encapsulates all address-related fields in a single structure.

Using Composite Types in Tables​

Once we've defined a composite type, we can use it as a column type in our tables:

sql
CREATE TABLE customers (
    customer_id serial PRIMARY KEY,
    full_name varchar(100) NOT NULL,
    billing_address address,
    shipping_address address
);

This is much cleaner than creating separate columns for each address field, especially when we need to store multiple addresses (billing and shipping in this case).

Inserting Data with Composite Types​

To insert data into a table with composite columns, you can use several approaches:

1. Using Row Constructor Syntax​

sql
INSERT INTO customers (full_name, billing_address, shipping_address)
VALUES (
    'John Doe',
    ROW('123 Main St', 'Boston', 'MA', '02108', 'USA'),
    ROW('123 Main St', 'Boston', 'MA', '02108', 'USA')
);

2. Using Composite Literal Syntax​

sql
INSERT INTO customers (full_name, billing_address, shipping_address)
VALUES (
    'Jane Smith',
    '("456 Oak Ave", "San Francisco", "CA", "94107", "USA")',
    '("789 Pine St", "San Francisco", "CA", "94109", "USA")'
);

Accessing Composite Type Data​

You can access the individual fields of a composite type using the dot notation:

sql
SELECT
    customer_id,
    full_name,
    billing_address.city AS billing_city,
    shipping_address.postal_code AS shipping_postal_code
FROM customers;

Sample output:

 customer_id | full_name  | billing_city  | shipping_postal_code
-------------+------------+---------------+---------------------
           1 | John Doe   | Boston        | 02108
           2 | Jane Smith | San Francisco | 94109

You can also retrieve the entire composite value:

sql
SELECT full_name, billing_address FROM customers;

Sample output:

 full_name  |                         billing_address
------------+----------------------------------------------------------------
 John Doe   | ("123 Main St",Boston,MA,02108,USA)
 Jane Smith | ("456 Oak Ave","San Francisco",CA,94107,USA)

Updating Composite Type Values​

You can update composite values in several ways:

1. Update the Entire Value​

sql
UPDATE customers
SET billing_address = ROW('500 Market St', 'Philadelphia', 'PA', '19102', 'USA')
WHERE customer_id = 1;

2. Update Individual Fields​

sql
UPDATE customers
SET billing_address.city = 'Chicago',
    billing_address.state = 'IL',
    billing_address.postal_code = '60601'
WHERE customer_id = 2;

Comparing Composite Types​

You can compare composite values as you would any other data type:

sql
SELECT full_name
FROM customers
WHERE billing_address = shipping_address;

This would return customers whose billing and shipping addresses are identical.

Advanced Usage: Composite Types as Function Parameters and Return Types​

Composite types are particularly useful with functions:

sql
CREATE OR REPLACE FUNCTION format_address(addr address)
RETURNS text AS $$
BEGIN
    RETURN addr.street_address || ', ' ||
           addr.city || ', ' ||
           addr.state || ' ' ||
           addr.postal_code || ', ' ||
           addr.country;
END;
$$ LANGUAGE plpgsql;

Now you can use this function to format addresses:

sql
SELECT customer_id, full_name, format_address(billing_address) AS formatted_address
FROM customers;

Sample output:

 customer_id | full_name  | formatted_address
-------------+------------+----------------------------------------------------
           1 | John Doe   | 500 Market St, Philadelphia, PA 19102, USA
           2 | Jane Smith | 456 Oak Ave, Chicago, IL 60601, USA

Nested Composite Types​

You can create more complex structures by nesting composite types:

sql
CREATE TYPE name_type AS (
    first_name varchar(50),
    middle_name varchar(50),
    last_name varchar(50)
);

CREATE TYPE contact_info AS (
    person_name name_type,
    email varchar(100),
    phone varchar(20),
    address address
);

CREATE TABLE contacts (
    contact_id serial PRIMARY KEY,
    info contact_info
);

Inserting data with nested composite types:

sql
INSERT INTO contacts (info)
VALUES (
    ROW(
        ROW('John', 'Michael', 'Doe'),
        '[email protected]',
        '555-123-4567',
        ROW('123 Main St', 'Boston', 'MA', '02108', 'USA')
    )
);

Accessing nested fields:

sql
SELECT
    contact_id,
    info.person_name.first_name || ' ' || info.person_name.last_name AS full_name,
    info.email,
    info.address.city
FROM contacts;

Real-World Example: Inventory Management System​

Let's see a practical example of using composite types in an inventory management system:

sql
-- Define composite types for product attributes
CREATE TYPE dimensions AS (
    length decimal,
    width decimal,
    height decimal,
    unit varchar(10)
);

CREATE TYPE weight_measurement AS (
    value decimal,
    unit varchar(10)
);

-- Create a products table using composite types
CREATE TABLE products (
    product_id serial PRIMARY KEY,
    name varchar(100) NOT NULL,
    size dimensions,
    weight weight_measurement,
    created_at timestamp DEFAULT current_timestamp
);

-- Insert sample data
INSERT INTO products (name, size, weight)
VALUES (
    'Laptop',
    ROW(13.3, 8.9, 0.7, 'inches'),
    ROW(3.5, 'pounds')
);

INSERT INTO products (name, size, weight)
VALUES (
    'Desktop Monitor',
    ROW(24, 21.5, 7.5, 'inches'),
    ROW(12.4, 'pounds')
);

-- Query that uses the composite types
SELECT
    product_id,
    name,
    size.length || 'x' || size.width || 'x' || size.height || ' ' || size.unit AS dimensions,
    weight.value || ' ' || weight.unit AS product_weight
FROM products;

Sample output:

 product_id | name            | dimensions           | product_weight
------------+-----------------+----------------------+---------------
          1 | Laptop          | 13.3x8.9x0.7 inches  | 3.5 pounds
          2 | Desktop Monitor | 24x21.5x7.5 inches   | 12.4 pounds

This approach lets us store and retrieve complex product information in a structured manner.

Custom Operators and Functions for Composite Types​

You can define custom operators and functions to work with your composite types:

sql
-- Calculate volume from dimensions
CREATE OR REPLACE FUNCTION calculate_volume(dim dimensions)
RETURNS decimal AS $$
BEGIN
    RETURN dim.length * dim.width * dim.height;
END;
$$ LANGUAGE plpgsql;

-- Use the function in a query
SELECT
    name,
    calculate_volume(size) AS volume,
    size.unit || '³' AS volume_unit
FROM products;

Sample output:

 name            | volume    | volume_unit
-----------------+-----------+------------
 Laptop          | 83.037    | inches³
 Desktop Monitor | 3870      | inches³

Using Composite Types with Arrays​

PostgreSQL allows combining composite types with arrays for even more complex data structures:

sql
CREATE TABLE shipping_packages (
    package_id serial PRIMARY KEY,
    tracking_number varchar(50),
    items_dimensions dimensions[],
    total_weight weight_measurement
);

INSERT INTO shipping_packages (tracking_number, items_dimensions, total_weight)
VALUES (
    'TRK123456789',
    ARRAY[
        ROW(10, 8, 2, 'inches'),
        ROW(5, 5, 5, 'inches'),
        ROW(12, 6, 3, 'inches')
    ],
    ROW(9.5, 'pounds')
);

Querying arrays of composite types:

sql
SELECT
    package_id,
    tracking_number,
    items_dimensions[1].length || 'x' ||
    items_dimensions[1].width || 'x' ||
    items_dimensions[1].height || ' ' ||
    items_dimensions[1].unit AS first_item_dimensions
FROM shipping_packages;

Advantages of Using Composite Types​

  1. Logical Grouping: Fields that belong together are grouped as a single unit
  2. Code Reusability: Define a structure once and use it in multiple tables
  3. Cleaner Schema: Avoid table explosion and reduce the number of joins
  4. Domain Modeling: More closely model real-world entities and relationships
  5. Performance: Can improve query performance by reducing joins

Limitations and Considerations​

  1. Database Portability: Composite types are specific to PostgreSQL and not part of the SQL standard
  2. ORM Support: Some ORMs might not fully support composite types
  3. Indexing: You cannot directly index individual fields within a composite type
  4. Constraints: Cannot add constraints to individual fields within a composite type

Summary​

PostgreSQL composite types provide a powerful way to model complex data structures within your database. They enable you to:

  • Group related fields together logically
  • Create reusable data structures
  • Build hierarchical data models
  • Simplify your schema design
  • Create more expressive and maintainable code

By mastering composite types, you'll be able to design database schemas that more accurately represent your business domain and reduce complexity in your application code.

Exercises​

  1. Create a composite type for representing a product_review with fields for rating, comment, and review date.
  2. Design a schema for a blog using composite types for author information, post metadata, and comment details.
  3. Write a function that takes an address composite type and returns whether it's domestic or international based on the country field.
  4. Create a composite type for representing money that includes amount and currency, then write functions to convert between different currencies.
  5. Design a recipe database using composite types for ingredients (including name, quantity, and unit) and nutritional information.

Additional Resources​


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)