PostgreSQL offers a powerful but often overlooked feature: every table implicitly defines a data type. This type, known as a composite type, represents the structure of a row in that table. As a result, tables are not only storage objects but also reusable type definitions.
This capability allows you to store structured records inside a single column, pass rows between functions, and build more expressive SQL constructs. This blog explains how composite types work, how to use them, and where they fit in real-world systems.
Tables Automatically Define Composite Types
When a table is created, PostgreSQL automatically creates a corresponding composite type with the same name and structure. For example:
CREATE TABLE orders (
id INT,
amount INT);
In addition to creating the orders table, PostgreSQL internally defines a type equivalent to:
orders ? (id INT, amount INT)
This type is registered in system catalogs and can be used anywhere a data type is expected.
Using a Table as a Column Type
Because the table defines a type, it can be used directly in another table:
CREATE TABLE test (
data orders
);
Here, data is a single column of type orders, capable of storing a full row consisting of id and amount. This allows one table to embed the row structure of another table without using foreign keys or normalization. Instead of referencing another row by ID, the full structure can be stored directly inside a column.
Inserting Values into Composite Columns
When inserting into a composite column, values must be provided as a single structured object.
Using ROW constructor
INSERT INTO test(data)
VALUES (ROW(12, 14));
Using shorthand syntax
INSERT INTO test(data)
VALUES ((12, 14));
Using explicit casting
INSERT INTO test(data)
VALUES (ROW(12, 14)::orders);
Providing multiple scalar values without wrapping them in a composite structure will result in an error, as PostgreSQL expects a single value of type orders. If the composite definition changes later, inserts must follow the new structure. For this reason, explicit ROW(...) syntax is usually clearer and safer than shorthand forms in production systems.
Querying Composite Data
Composite values can be treated as a whole or decomposed into their individual attributes when needed. This gives flexibility between compact storage and relational-style querying.
Field access syntax such as (data).id is resolved by PostgreSQL's parser and planner, allowing composite fields to participate in filters, joins, projections, and expressions.
Selecting raw values
SELECT * FROM test;
Output:
data
-----------
(12,14)
Accessing individual fields
SELECT (data).id, (data).amount
FROM test;
Expanding all fields
SELECT (data).*
FROM test;
Filtering using fields
SELECT *
FROM test
WHERE (data).amount > 10;
Updating Composite Values
Composite fields are immutable at the field level. To modify a value, the entire composite must be reassigned:
UPDATE test
SET data = ROW((data).id, 100);
Because a composite column is stored as one structured value, PostgreSQL rewrites the entire composite during updates. Even if only one attribute changes, the new row value replaces the previous one.
For frequently updated attributes, storing separate columns may be more efficient. Composite columns are generally better suited for grouped values that change together.
Defining Composite Types Explicitly
Composite types can also be created independently of tables:
CREATE TYPE order_type AS (
id INT,
amount INT
);
They can then be used in table definitions:
CREATE TABLE test2 (
data order_type
);
Explicit composite types are useful when no physical table is needed. They allow developers to define reusable row structures purely for application logic, function interfaces, or temporary processing.
Unlike table-backed composite types, standalone types are independent schema objects. They can be versioned and reused across multiple tables or functions.
Alternative Input Formats
PostgreSQL supports text-based input for composite types:
INSERT INTO test(data)
VALUES ('(12,14)');
This format is useful in bulk operations or when constructing dynamic SQL.
Using Composite Types in Functions
Composite return types make SQL functions cleaner by returning structured results instead of multiple scalar columns. This is especially useful for stored procedures, reporting functions, and encapsulated business logic.
They are also commonly used in PL/pgSQL variables, where an entire row can be assigned to a single variable and accessed field by field.
Composite types are commonly used as function return values:
CREATE FUNCTION get_order()
RETURNS orders AS $$
SELECT ROW(1, 500)::orders;
$$ LANGUAGE SQL;
They can also be used as input parameters or intermediate structures in procedural logic.
Internal Representation
Internally, PostgreSQL maintains a strong link between tables and their row types:
- Table definitions are stored in pg_class
- Type definitions are stored in pg_type
Each table has a corresponding entry in pg_type representing its composite structure. This is why a table name can be used as a data type in SQL statements.
Practical Considerations
Suitable use cases
- Function return types
- Structured data handling within SQL
- Temporary or intermediate data modeling
- Advanced query construction
Limitations
- Limited indexing capabilities within composite fields
- Reduced compatibility with ORMs and application frameworks
- Less readable queries when overused
- Full-value replacement required for updates
PostgreSQL’s ability to treat tables as data types reflects its advanced type system and internal architecture. Composite types enable developers to work with structured data in a more expressive and modular way, particularly in database-centric logic.
While this feature is not commonly used in standard application schemas, it is highly valuable in specific contexts such as function design, query abstraction, and internal data processing.
Understanding composite types provides deeper insight into how PostgreSQL represents and manipulates data internally, and opens up additional possibilities for advanced database design.