Composite types in PostgreSQL

Introduction

Composite types let you group different pieces of data together as one unit. This helps organize related information clearly.

When you want to store multiple related values in one column, like a full address with street, city, and zip.

When you want to return multiple related values from a function in a neat package.

When you want to pass a group of related values as one argument to a function.

When you want to create a reusable data structure inside the database.

When you want to simplify complex queries by grouping related fields.

Syntax

PostgreSQL

CREATE TYPE type_name AS (
    field1 data_type1,
    field2 data_type2
);

Composite types are like custom data structures made inside the database.

You can use them as column types or function return types.

Examples

This creates a composite type named full_name with two text fields.

PostgreSQL

CREATE TYPE full_name AS (
    first_name TEXT,
    last_name TEXT
);

This defines an address type with street, city, and zip code fields.

PostgreSQL

CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zip_code TEXT
);

This table uses the composite types full_name and address as column types.

PostgreSQL

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name full_name,
    home address
);

Sample Program

This example creates a composite type full_name, then a table employees using it. It inserts two employees and selects their names by accessing the composite fields.

PostgreSQL

CREATE TYPE full_name AS (
    first_name TEXT,
    last_name TEXT
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name full_name
);

INSERT INTO employees (name) VALUES (ROW('Alice', 'Smith'));
INSERT INTO employees (name) VALUES (ROW('Bob', 'Jones'));

SELECT id, (name).first_name AS first_name, (name).last_name AS last_name FROM employees ORDER BY id;

Important Notes

You access composite fields using parentheses and dot notation, like (column).field.

Composite types can be nested inside other composite types.

They help keep related data together and make queries easier to read.

Summary

Composite types group related fields into one custom type.

They can be used as column types or function return types.

Access fields with (column).field syntax.