How to fix relation does not have a composite type in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 119 views

This error occurs when you attempt to use a relation (table or view) as a composite type in a context where PostgreSQL expects a stand-alone composite type. It commonly happens when using table rows in function arguments, array constructors, or type conversions without proper syntax.

Quick fix

Fixes relation does not have a composite type

CREATE TYPE person_type AS (
  id INT,
  name TEXT,
  age INT
);
  1. Create a stand-alone composite type instead of using table rows
  2. Use explicit type casting with ROW constructors
  3. Fix functions that accept composite parameters
  4. Use typed tables correctly if referencing table structure
postgresql — error
CREATE TYPE person_type AS (
  id INT,
  name TEXT,
  age INT
);
relation does not have a composite type

What this error means

The "relation does not have a composite type" error indicates a mismatch between what PostgreSQL expects (a stand-alone composite type created with CREATE TYPE) and what you have provided (a table or view row type). In PostgreSQL, there are two kinds of composite types: 1. Stand-alone composite types: Created explicitly with CREATE TYPE ... AS (...) 2. Table row types: Automatically created when you CREATE TABLE, representing the structure of a table row While PostgreSQL automatically creates a composite type for each table, these table row types cannot be used in all the same contexts as stand-alone types. Certain operations require true stand-alone composite types. The error manifests when using a table row type as a function parameter where a stand-alone type is required, attempting to create arrays of anonymous ROW constructors without type specification, using array_agg() or array_append() with ROW() values without proper typing, or passing table row types to functions expecting stand-alone composite types. The key distinction is that stand-alone composite types have specific properties that table row types lack, which are needed for certain PostgreSQL operations.

Typical symptoms

  • Functions reject table row types as arguments
  • array_agg() fails with composite type errors
  • Anonymous ROW() constructors fail in queries
  • Type casting from a table row fails
  • CREATE TYPE AS TABLE statements fail
  • Queries using table.* in function arguments fail
  • Data type conversion errors with composite values

Common causes

  1. Using a table row type where a stand-alone composite type is required
  2. Attempting to create arrays of anonymous ROW constructors without type specification
  3. Using array_agg() or array_append() with ROW() values without proper typing
  4. Passing table row types to functions expecting stand-alone composite types
  5. Attempting to use typed tables incorrectly in function signatures
  6. Using relations that do not have explicit composite type definitions
  7. Passing CURRENT_ROW or implicit row types without explicit casting

How to fix "relation does not have a composite type"

1Create a stand-alone composite type instead of using table rows

Create an explicit composite type using CREATE TYPE rather than relying on table row types.

Step 1: Create a stand-alone composite type

sql
CREATE TYPE person_type AS (
  id INT,
  name TEXT,
  age INT
);

Step 2: Create a function that accepts the type

sql
CREATE FUNCTION process_person(p person_type) RETURNS TEXT AS $$
BEGIN
  RETURN p.name || ' is ' || p.age || ' years old';
END;
$$ LANGUAGE plpgsql;

Step 3: Convert table rows to composite type when calling the function

sql
SELECT process_person(ROW(id, name, age)::person_type) FROM person;

-- Or use CAST syntax
SELECT process_person(CAST(ROW(id, name, age) AS person_type)) FROM person;

Key points:
- CREATE TYPE defines a stand-alone type explicitly
- Functions must match their parameter types exactly
- Use ROW() constructor with :: casting to convert values

2Use explicit type casting with ROW constructors

When constructing composite values on the fly, always specify the type explicitly.

Anonymous ROW values without type specification cannot be used with array_agg and other functions that require explicit types.

Step 1: Define the composite type first

sql
CREATE TYPE user_record AS (
  id INT,
  name TEXT
);

Step 2: Now use it with explicit casting

sql
SELECT array_agg(ROW(id, name)::user_record) FROM users;

-- Or in a function call
SELECT process_records(array_agg(ROW(id, name)::user_record))
FROM users;

For multiple field types:

sql
CREATE TYPE order_item AS (
  order_id BIGINT,
  product_name VARCHAR(255),
  quantity INT,
  price NUMERIC(10,2),
  ordered_at TIMESTAMP
);

-- Use with casting
SELECT array_agg(
  ROW(
    o.id,
    p.name,
    oi.quantity,
    oi.unit_price,
    o.created_at
  )::order_item
)
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id;

Important: The field order and types in the ROW() constructor must exactly match the order and types in the composite type definition.

3Fix functions that accept composite parameters

Ensure functions are defined with explicit stand-alone composite types.

Functions must have matching parameter types. Never mix table row types with function parameters that expect stand-alone types.

Incorrect function definition:

sql
-- This will not work properly with table rows
CREATE FUNCTION get_person_info(p person) RETURNS TEXT AS $$
BEGIN
  -- This references the table row type, not a stand-alone type
  RETURN p.name;
END;
$$ LANGUAGE plpgsql;

Correct function definition:

sql
-- Step 1: Define the composite type first
CREATE TYPE person_type AS (
  id INT,
  name TEXT,
  email TEXT,
  age INT
);

-- Step 2: Define function using the composite type
CREATE FUNCTION get_person_info(p person_type)
RETURNS TEXT AS $$
BEGIN
  RETURN p.name || ' (' || p.email || ')';
END;
$$ LANGUAGE plpgsql;

-- Step 3: Call with proper casting
SELECT get_person_info(ROW(1, 'John', 'john@example.com', 30)::person_type);

For multiple composite type parameters:

sql
CREATE TYPE address_type AS (
  street VARCHAR(255),
  city VARCHAR(100),
  country VARCHAR(100),
  postal_code VARCHAR(20)
);

CREATE TYPE contact_type AS (
  email VARCHAR(255),
  phone VARCHAR(20)
);

CREATE FUNCTION create_customer(
  p person_type,
  addr address_type,
  contact contact_type
) RETURNS TABLE(customer_id INT, created_date TIMESTAMP) AS $$
BEGIN
  INSERT INTO customers (name, email, street, city, country, postal_code, phone)
  VALUES (p.name, contact.email, addr.street, addr.city, addr.country,
          addr.postal_code, contact.phone)
  RETURNING id, created_at;
END;
$$ LANGUAGE plpgsql;
4Use typed tables correctly if referencing table structure

If you want a table based on a composite type, use CREATE TABLE OF.

This approach ensures the table and type stay in sync, and allows functions to accept table rows naturally.

Step 1: Create the composite type

sql
CREATE TYPE employee_type AS (
  employee_id INT,
  name TEXT,
  department TEXT,
  salary NUMERIC(10,2),
  hire_date DATE
);

Step 2: Create a table based on that type

sql
CREATE TABLE employees OF employee_type (
  PRIMARY KEY (employee_id),
  CHECK (salary > 0)
);

Step 3: Insert data

sql
INSERT INTO employees VALUES
  (1, 'Alice', 'Engineering', 100000.00, '2020-01-15'),
  (2, 'Bob', 'Sales', 80000.00, '2021-06-01');

Step 4: Reference the type properly in functions

sql
CREATE FUNCTION process_employee(e employee_type)
RETURNS VARCHAR AS $$
BEGIN
  RETURN e.name || ' works in ' || e.department;
END;
$$ LANGUAGE plpgsql;

-- This works with typed tables
SELECT process_employee(employees.*) FROM employees;

Key differences:
- Tables created with CREATE TABLE automatically get a row type (but it is not standalone)
- Tables created with CREATE TABLE OF explicitly use a standalone composite type
- Use CREATE TABLE OF when you need functions to accept table rows as parameters

5Check PostgreSQL version and use appropriate casting syntax

Different PostgreSQL versions have slightly different support for composite types.

PostgreSQL 13+ (recommended approach):

sql
-- Simple and clear casting
CREATE TYPE payment AS (
  amount NUMERIC,
  currency VARCHAR(3),
  method VARCHAR(50)
);

SELECT array_agg(ROW(amount, 'USD', 'card')::payment)
FROM orders;

PostgreSQL 11-12 (explicit type conversion):

sql
-- May need more verbose casting
SELECT array_agg(CAST(ROW(amount, 'USD', 'card') AS payment))
FROM orders;

Check your PostgreSQL version:

bash
psql -c "SELECT version();"

Or in SQL:

sql
SHOW server_version;
-- Returns something like: 15.1

Compatibility note: If upgrading PostgreSQL, verify composite type definitions and function signatures still match.

6Debug composite type mismatches with system catalogs

Use PostgreSQL system tables to verify composite type definitions and function signatures.

List all composite types in your schema:

sql
SELECT
  t.typname as type_name,
  t.typtype as type_type,
  a.attname as field_name,
  a.atttypid::regtype as field_type,
  a.attnum as field_number
FROM pg_type t
JOIN pg_attribute a ON a.attrelid = t.typrelid
WHERE t.typtype = 'c'
ORDER BY t.typname, a.attnum;

Verify function parameter types:

sql
-- Check what types a function expects
SELECT
  p.proname as function_name,
  t.typname as parameter_type,
  p.proargtypes
FROM pg_proc p
JOIN pg_type t ON t.oid = ANY(p.proargtypes)
WHERE p.proname = 'process_person'
ORDER BY p.oid;

Find which functions use a composite type:

sql
-- Find all functions that accept a specific composite type
SELECT DISTINCT
  p.proname,
  t.typname,
  pg_get_functiondef(p.oid) as definition
FROM pg_proc p,
  pg_type t
WHERE t.typname = 'person_type'
AND p.proargtypes::text LIKE '%' || t.oid::text || '%';

Check for mismatched definitions:

sql
-- If you get the error, compare what exists vs. what is expected
-- First, find the relation
SELECT
  c.relname as table_or_view_name,
  c.relkind as relation_type
FROM pg_class c
WHERE c.relname = 'person';

-- Check if it has an associated composite type
SELECT
  c.relname,
  t.typname,
  t.oid
FROM pg_class c
JOIN pg_type t ON t.typrelid = c.oid
WHERE c.relname = 'person';

Advanced notes

Stand-alone vs table row types: PostgreSQL has a peculiar design where creating a TABLE automatically creates a composite type for that table row structure. However, this automatic composite type is NOT a "stand-alone" composite type. Some operations especially in functions require true stand-alone types created with CREATE TYPE. This is an implementation detail that trips up many PostgreSQL users coming from other databases.

Parallel query execution and composite types: With parallel query execution (introduced in PostgreSQL 9.6), certain restrictions around composite types are enforced more strictly. Anonymous composite types without explicit type casts may fail in parallel execution. If you see "relation does not have a composite type" specifically with parallel execution, make type casts explicit.

Typed tables and inheritance: Typed tables created with CREATE TABLE OF inherit from their composite type. Functions expecting a specific composite type can accept rows from typed tables naturally, which is a major advantage of this approach.

Migration from table row types: If you have existing code using table row types that now fails, you should migrate to stand-alone types. Old code that worked in earlier PostgreSQL versions may need updating for newer versions where type checking is stricter.

Frequently asked questions

What does "relation does not have a composite type" mean?
The "relation does not have a composite type" error indicates a mismatch between what PostgreSQL expects (a stand-alone composite type created with CREATE TYPE) and what you have provided (a table or view row type). In PostgreSQL, there are two kinds of composite types: 1. Stand-alone composite types: Created explicitly with CREATE TYPE ...
How do I fix "relation does not have a composite type"?
This error occurs when you attempt to use a relation (table or view) as a composite type in a context where PostgreSQL expects a stand-alone composite type. It commonly happens when using table rows in function arguments, array constructors, or type conversions without proper syntax.
What causes "relation does not have a composite type"?
Using a table row type where a stand-alone composite type is required Attempting to create arrays of anonymous ROW constructors without type specification Using array_agg() or array_append() with ROW() values without proper typing
Is the "relation does not have a composite type" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading