Composite Type in PostgreSQL: Definition and Usage

In PostgreSQL, a composite type is a custom data type made up of multiple named fields, similar to a row or record. It allows you to group related data together as a single unit, which can be used in tables, functions, or variables.

How It Works

Think of a composite type as a container that holds several pieces of related information together, like a small box with compartments. Each compartment has a name and a type, such as a person's name, age, and email grouped in one box. This makes it easier to handle complex data as one object instead of separate values.

In PostgreSQL, you define a composite type by specifying the names and data types of its fields. Once created, you can use this type to define columns in tables or as input/output types in functions. It works like a blueprint for a structured piece of data, similar to a form with labeled fields.

Example

This example shows how to create a composite type for a person's contact info and use it in a table.

sql

CREATE TYPE contact_info AS (
  name TEXT,
  phone TEXT,
  email TEXT
);

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY,
  info contact_info
);

INSERT INTO contacts (info) VALUES (ROW('Alice', '123-4567', '[e-mail]'));

SELECT info.name, info.phone, info.email FROM contacts;

Output

 name  |  phone   |        email         
-------+----------+---------------------
 Alice | 123-4567 | [e-mail]
(1 row)

When to Use

Use composite types when you want to group related fields into one logical unit to keep your database organized and your queries simpler. For example, if you often store address details (street, city, zip) together, a composite type can bundle them.

This is helpful in functions that return multiple related values or when you want to reuse the same structure in many tables without repeating column definitions.

Key Points

• A composite type groups multiple fields into one custom data type.
• It simplifies handling complex data structures in tables and functions.
• You define it once and reuse it in multiple places.
• It behaves like a record or row with named fields.

Key Takeaways

Composite types in PostgreSQL group related fields into one structured data type.

They help organize complex data and simplify table and function design.

You can reuse composite types across tables and functions for consistency.

Composite types behave like records with named fields for easy access.