Composite Types in PostgreSQL

PostgreSQL composite types let you bundle related fields into a single named structure. Once defined, a composite type can be used as the data type of a table column, returned from a function, or passed as a function argument — just like any built-in type.

Syntax

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

Practical Example

Define a composite type for shipping addresses and use it in an orders table:

CREATE TYPE shipping_address AS (
  street      TEXT,
  city        TEXT,
  state       CHAR(2),
  postal_code VARCHAR(20),
  country     VARCHAR(50)
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  product_sku VARCHAR(50)       NOT NULL,
  quantity    INTEGER           NOT NULL CHECK (quantity > 0),
  ship_to     shipping_address,
  placed_at   TIMESTAMPTZ       NOT NULL DEFAULT now()
);

Inserting Composite Values

Construct a composite value using the ROW() expression:

INSERT INTO orders (product_sku, quantity, ship_to)
VALUES (
  'BOLT-TI-M6',
  10,
  ROW('123 Industrial Way', 'Portland', 'OR', '97201', 'USA')
);

-- Individual field notation using dot syntax
INSERT INTO orders (product_sku, quantity,
                    ship_to.street, ship_to.city, ship_to.state,
                    ship_to.postal_code, ship_to.country)
VALUES ('PANEL-CF-A4', 2, '500 Tech Park Blvd', 'Austin', 'TX', '78701', 'USA');

Accessing Composite Fields

Wrap the column name in parentheses and append the field name with a dot — the parentheses are required:

SELECT
  id,
  product_sku,
  (ship_to).city    AS ship_city,
  (ship_to).state   AS ship_state,
  (ship_to).country AS ship_country
FROM orders;

 id | product_sku |  ship_city | ship_state | ship_country
----+-------------+------------+------------+--------------
  1 | BOLT-TI-M6  | Portland   | OR         | USA
  2 | PANEL-CF-A4 | Austin     | TX         | USA

Expand all fields at once with .*:

SELECT id, product_sku, (ship_to).* FROM orders;

Updating Composite Fields

Use dot notation in the SET clause (no parentheses around the column name in SET):

UPDATE orders
SET ship_to.postal_code = '97202',
    ship_to.city = 'Portland'
WHERE id = 1
RETURNING id, product_sku, (ship_to).city, (ship_to).postal_code;

Using Composite Types as Function Return Types

CREATE TYPE order_stats AS (
  total_orders   INTEGER,
  total_quantity BIGINT,
  avg_quantity   NUMERIC(10,2)
);

CREATE OR REPLACE FUNCTION get_product_stats(p_sku VARCHAR(50))
RETURNS order_stats AS $$
  SELECT
    COUNT(*)::INTEGER,
    SUM(quantity)::BIGINT,
    AVG(quantity)::NUMERIC(10,2)
  FROM orders
  WHERE product_sku = p_sku;
$$ LANGUAGE SQL;

SELECT * FROM get_product_stats('BOLT-TI-M6');

Managing Composite Types

-- Add a field to an existing composite type
ALTER TYPE shipping_address ADD ATTRIBUTE apartment TEXT;

-- Drop a field (requires CASCADE for dependent columns)
ALTER TYPE shipping_address DROP ATTRIBUTE apartment CASCADE;

-- List user-defined types
\dT+

-- Drop the type (fails if columns reference it unless CASCADE)
DROP TYPE IF EXISTS shipping_address CASCADE;

Testing with Vela

Composite type changes are high-impact schema operations that lock every table using the type. Before running ALTER TYPE ... ADD ATTRIBUTE or DROP ATTRIBUTE on a production composite type used by a large orders table, create a database branch, apply the migration, verify that all dependent functions and queries still work correctly, and measure the lock duration — then schedule the production deployment accordingly.

Production Tips

• Composite types are best for logically grouped fields that always travel together — addresses, coordinates, price-with-currency pairs.
• Querying individual fields requires the (column_name).field syntax — forgetting the parentheses causes a parse error.
• Altering a composite type is disruptive — plan migrations carefully and prefer adding optional fields over removing required ones.
• Document composite types with COMMENT ON TYPE type_name IS 'description'; so their purpose is visible in schema tools.
• For structured column data that needs flexible queries, JSONB is often more practical than a composite type — it does not require DDL changes when the structure evolves.