How to Use Composite Data Types in PostgreSQL

Abdul Wahab Junaid

3 years ago

How to Use Composite Data Types in PostgreSQL

Anyone who’s worked with a general-purpose programming language is familiar with the idea of a struct or an object — a bundle of related fields grouped together under one name. PostgreSQL brings that exact idea into the database itself through composite types. Instead of scattering related fields across separate columns or reaching for JSON every time you need structure, you can define a real, typed structure that Postgres understands natively.

This guide covers composite types in depth: how to create them, how to use them in tables and functions, how to query and update them, and where they genuinely make your schema better versus where they can cause headaches.

What Is a Composite Type?

A composite type is a data type made up of a list of field names and their corresponding types — much like a row in a table, except it isn’t tied to a table at all. In fact, every table you create in PostgreSQL automatically gets a matching composite type behind the scenes, representing the structure of its rows. You can create standalone composite types explicitly with CREATE TYPE, and use them anywhere a type is expected: as a column type, a function parameter, a function return type, or even nested inside another composite type.

Basic Syntax

CREATE TYPE type_name AS (
    field_name1 data_type1,
    field_name2 data_type2,
    ...
);

A simple example:

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

That’s it — full_name is now a real type you can use anywhere in the database.

Using Composite Types in Tables

CREATE TABLE employees (
    employee_id serial PRIMARY KEY,
    name full_name NOT NULL,
    hire_date date NOT NULL
);

Inserting a row requires you to construct the composite value using ROW(...) or a parenthesized tuple:

INSERT INTO employees (name, hire_date)
VALUES (ROW('Sarah', 'Connor'), '2023-06-01');

-- or, equivalently, without the ROW keyword:
INSERT INTO employees (name, hire_date)
VALUES (('Sarah', 'Connor'), '2023-06-01');

Reading Fields from a Composite Column

This trips up nearly everyone the first time. You cannot write name.first_name directly in a query — Postgres will interpret name as a table name because of how the parser handles dot notation. You need to wrap the column reference in parentheses:

SELECT (name).first_name, (name).last_name
FROM employees;

If you’re referencing a table alias too, it looks like this:

SELECT (e.name).first_name
FROM employees e;

Updating a Field Inside a Composite Column

You update a single field of a composite column like this:

UPDATE employees
SET name.first_name = 'Sara'
WHERE employee_id = 1;

Note: this dotted assignment syntax on the left-hand side of SET is valid in UPDATE statements (unlike in SELECT), because Postgres parses it differently in that context.

To replace the whole composite value at once:

UPDATE employees
SET name = ROW('Sara', 'Connor-Reese')
WHERE employee_id = 1;

Composite Types as Function Parameters and Return Types

This is where composite types really start to shine — they make functions dramatically easier to read and use.

CREATE TYPE geo_point AS (
    latitude numeric(9,6),
    longitude numeric(9,6)
);

CREATE OR REPLACE FUNCTION format_coordinates(p_point geo_point)
RETURNS text AS $$
BEGIN
    RETURN 'Lat: ' || p_point.latitude || ', Lon: ' || p_point.longitude;
END;
$$ LANGUAGE plpgsql;

Interestingly, inside PL/pgSQL function bodies, you can use plain dot notation without parentheses, since the PL/pgSQL parser handles it differently from the SQL parser:

SELECT format_coordinates(ROW(30.267153, -97.743057));

A function returning a composite type is also very useful:

CREATE OR REPLACE FUNCTION get_employee_name(p_id integer)
RETURNS full_name AS $$
DECLARE
    result full_name;
BEGIN
    SELECT (first_name_col, last_name_col) INTO result
    FROM employees_raw
    WHERE employee_id = p_id;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

You can call this and immediately expand its fields into a result set:

SELECT (get_employee_name(1)).*;

Composite Types and Table Row Types

Every table’s row type is itself a composite type, and you can use it directly:

CREATE TABLE products (
    product_id serial PRIMARY KEY,
    product_name text,
    price numeric(10,2)
);

CREATE OR REPLACE FUNCTION apply_discount(p_product products, p_pct numeric)
RETURNS numeric AS $$
BEGIN
    RETURN p_product.price - (p_product.price * p_pct / 100);
END;
$$ LANGUAGE plpgsql;

You can pass an entire row into this function:

SELECT apply_discount(p, 10) FROM products p WHERE product_id = 3;

This pattern is genuinely useful in stored procedures that operate on “the whole row” rather than individual scalar arguments.

Arrays of Composite Types

Composite types combine cleanly with arrays, letting you model one-to-many structures without a join, when that makes sense:

CREATE TYPE line_item AS (
    product_name text,
    quantity integer,
    unit_price numeric(10,2)
);

CREATE TABLE quotes (
    quote_id serial PRIMARY KEY,
    customer_name text,
    items line_item[]
);

INSERT INTO quotes (customer_name, items)
VALUES (
    'Acme Corp',
    ARRAY[
        ROW('Widget', 10, 4.50)::line_item,
        ROW('Gadget', 3, 19.99)::line_item
    ]
);

Querying array elements:

SELECT customer_name, (items[1]).product_name
FROM quotes;

Expanding the array into rows using unnest:

SELECT customer_name, (unnest(items)).product_name, (unnest(items)).quantity
FROM quotes;

Comparing and Ordering Composite Values

Composite types support equality and, in many cases, ordering comparisons, based on comparing their fields in order:

SELECT * FROM employees
WHERE name = ROW('Sarah', 'Connor');

For ordering (<, >, ORDER BY), Postgres compares composite values field by field, similar to lexicographic string comparison — the first field is compared first, and only if it’s equal does the comparison move to the next field. This can be genuinely useful for multi-column sorting expressed as a single composite comparison, but it can also be surprising if you’re not expecting it, so use it deliberately.

Casting Between Composite Types and Text

Composite values have a well-defined text representation that looks like (field1,field2,field3). You can cast to and from text:

SELECT ROW('Sarah', 'Connor')::text;
-- returns: (Sarah,Connor)

SELECT '(Sarah,Connor)'::full_name;

Be careful with fields that contain commas, parentheses, or quotes — Postgres will need proper quoting in the text representation, and hand-constructing these strings is error-prone. Prefer ROW(...) construction over string literals whenever you’re building values programmatically.

Modifying Composite Types

Add a field:

ALTER TYPE full_name ADD ATTRIBUTE middle_name text;

Rename a field:

ALTER TYPE full_name RENAME ATTRIBUTE middle_name TO middle_initial;

Drop a field:

ALTER TYPE full_name DROP ATTRIBUTE middle_initial;

Important caveat: if the composite type is used as a column type in existing tables, altering it will affect every table using it. Postgres handles this reasonably gracefully for adding attributes, but dropping or changing an attribute’s type can be blocked or require careful planning, especially on large tables.

Common Use Cases

Grouping naturally related fields. Addresses, geographic coordinates, name components, and money-with-currency pairs are all classic candidates.

Function return values that need more than one piece of data. Rather than returning multiple OUT parameters or a loosely structured record, a well-named composite type documents exactly what a function hands back.

Passing whole rows into functions. As shown above, this keeps function signatures clean when logic genuinely operates on an entire row.

Intermediate representations in PL/pgSQL. Composite variables are a clean way to accumulate a structured result across multiple steps in a procedure before returning it.

When Not to Use Composite Types

Composite types aren’t a replacement for proper table design. If you find yourself wanting to query, filter, join, or index on individual fields of a composite column frequently, that’s usually a sign the “type” should actually be a normal table with a foreign key relationship instead. Composite types work best when the fields inside them are always used together and rarely need independent indexing.

They’re also not directly indexable field-by-field with a standard B-tree index the way normal columns are — you’d need to either index a computed expression on a specific field or reach for jsonb if you need flexible, path-based indexing across many independent fields.

Troubleshooting Common Issues

“Column reference is ambiguous” or parse errors on . access. Almost always means missing parentheses around the composite column: use (col).field, not col.field, in SQL statements (function bodies in PL/pgSQL are the exception).

Unexpected NULLs when a field inside a composite is NULL. A composite value itself can be non-null while individual fields inside it are null. Check field-level nullness explicitly with (col).field IS NULL if that distinction matters to your logic.

Casting errors from malformed text representations. If you’re importing composite values from CSV or external systems, validate the (field1,field2) formatting carefully — a stray comma inside a text field without quoting will break the cast.

Dependency errors on ALTER TYPE. As with any custom type, check pg_depend before altering a composite type that’s already used in production tables or functions.

Best Practices

Give composite types clear, singular names describing the “thing” they represent (geo_point, not points_data).
Keep them relatively flat — deeply nested composites become hard to query and reason about.
Use ROW(...)::type_name explicitly when inserting or constructing values, rather than relying on implicit casting, for clarity and fewer surprises.
Document which fields are expected to be non-null, since composite types themselves don’t enforce per-field constraints the way domains do — you’d need a domain or a CHECK on the containing table for that.
Prefer normal table columns and foreign keys over composite types when you expect to query, filter, or index on individual fields frequently.

Nesting Composite Types

Composite types can contain other composite types as fields, which lets you model genuinely hierarchical structures directly in the type system:

CREATE TYPE address AS (
    street text,
    city text,
    postal_code text
);

CREATE TYPE company AS (
    name text,
    hq_address address
);

CREATE TABLE vendors (
    vendor_id serial PRIMARY KEY,
    details company
);

INSERT INTO vendors (details)
VALUES (ROW('Acme Supplies', ROW('100 Main St', 'Springfield', '62701'))::company);

Reading a nested field requires chaining the parenthesized access:

SELECT ((details).hq_address).city
FROM vendors;

This works, but it’s worth being honest that nesting more than one or two levels deep starts to hurt readability fast, both in queries and in application code that has to construct these values. If you find yourself nesting three or more levels, it’s usually a sign that either a proper table relationship or a jsonb column would serve you better than continuing to model the hierarchy through composite types.

Composite Types in Aggregate Queries

Composite types combine well with aggregate functions when you want to bundle grouped results into a structured shape rather than returning flat, repeated columns:

CREATE TYPE sales_summary AS (
    total_orders bigint,
    total_revenue numeric(12,2),
    avg_order_value numeric(12,2)
);

SELECT customer_id,
       ROW(
           COUNT(*),
           SUM(order_total),
           AVG(order_total)
       )::sales_summary AS summary
FROM orders
GROUP BY customer_id;

The result is a single structured column per customer rather than three separate flat columns — genuinely useful when you’re passing this result on to a function or another layer of processing that expects a well-defined shape rather than loose scalar values.

Comparing Composite Types to JSON

It’s worth directly addressing a question that comes up constantly: why use a composite type instead of just storing a jsonb blob with the same fields?

The honest answer is that they solve overlapping but distinct problems. A composite type gives you:

A fixed, enforced structure. Every value of the type genuinely has the declared fields — the database won’t let you store a composite value missing an expected field the way it would let you store a jsonb document missing a key.
Native typing per field. A field declared numeric(10,2) behaves as an actual numeric value, not a JSON number that needs to be cast every time you use it.
Better performance for structured access patterns, since there’s no JSON parsing overhead on read.

A jsonb column gives you:

Genuine schema flexibility — different rows can have different keys, and adding a new field doesn’t require an ALTER TYPE.
Broader tooling support across application frameworks, which almost universally understand JSON natively.
Easier evolution for data whose shape changes over time without requiring a coordinated migration.

The rule of thumb: reach for a composite type when the structure is genuinely fixed and known in advance, and you want the database itself to guarantee that shape. Reach for jsonb when the structure is inherently variable, evolving, or when you need the broader ecosystem support that JSON tooling provides.

Performance Considerations

Composite types don’t carry meaningful storage overhead beyond the sum of their fields, but there are a couple of practical performance points worth knowing.

Accessing a single field from a composite column requires Postgres to deserialize the whole composite value first — this is rarely noticeable in typical usage, but if you have a very wide composite type and only ever need one specific field in a hot query path, it can be worth reconsidering whether that field should just be its own plain column instead.

Indexing individual fields inside a composite column isn’t possible directly with a simple index the way it is for a plain column. If you need to filter or join frequently on a specific field buried inside a composite type, you can create an expression index on that specific field access:

CREATE INDEX idx_vendor_city ON vendors ((((details).hq_address).city));

This works, but at that point it’s worth asking honestly whether the field would be better served as a proper standalone column with a normal index, since expression indexes on deeply nested composite access can become harder to maintain and reason about over time compared to a flat schema.

Composite Types in Table Inheritance and Polymorphic Functions

PostgreSQL’s function overloading and polymorphic type support extend to composite types in ways that can make your PL/pgSQL code considerably more reusable. A function accepting anyelement can accept a composite value and still let you access its fields dynamically:

CREATE OR REPLACE FUNCTION describe_row(r anyelement)
RETURNS text AS $$
BEGIN
    RETURN r::text;
END;
$$ LANGUAGE plpgsql;

SELECT describe_row(ROW('Sarah', 'Connor')::full_name);
-- (Sarah,Connor)

While this particular example is simple, the broader point is that composite types integrate cleanly with the rest of Postgres’s type system — they’re not a bolted-on feature, they participate in casting, polymorphism, and function overloading the same way scalar types do.

Debugging Composite Type Issues

A few diagnostic queries are worth keeping on hand when composite types start behaving unexpectedly.

Checking a type’s current field definitions:

SELECT attname, atttypid::regtype
FROM pg_attribute
WHERE attrelid = 'full_name'::regclass
  AND attnum > 0
ORDER BY attnum;

Checking what depends on a composite type before altering or dropping it:

SELECT DISTINCT dependent.relname, dependent.relkind
FROM pg_depend
JOIN pg_type t ON t.oid = pg_depend.refobjid
JOIN pg_class dependent ON dependent.oid = pg_depend.objid
WHERE t.typname = 'full_name';

Running this before any ALTER TYPE or DROP TYPE in a production environment is cheap insurance against breaking something you didn’t realize still depended on the type — especially in older codebases where a composite type may have been adopted for a one-off need years ago and quietly picked up additional dependents since.

When Composite Types Complicate More Than They Simplify

It’s worth closing with an honest caution, since composite types are genuinely easy to reach for and not always the right call. Teams sometimes adopt them for grouping fields that look related on the surface but that actually have independent lifecycles — an address that needs its own audit history, or a set of fields that different parts of the application need to update at different times and cadences. In those cases, a composite type ends up forcing “all or nothing” updates to the whole structure, which can be more awkward than just working with the individual columns directly, or than modeling the relationship as a proper foreign-keyed table. If you notice your application code is constantly reconstructing an entire composite value just to change one field, that friction is worth listening to — it usually means the grouping made sense conceptually but not operationally.

Wrapping Up

Composite types give PostgreSQL a genuinely useful middle ground between plain scalar columns and full-blown related tables. They shine as clean, self-documenting structures for function signatures, for grouping tightly related fields, and for building expressive stored procedures. Used thoughtfully — and not as a substitute for real table relationships — they’ll make your schema and your PL/pgSQL code noticeably easier to read and maintain.