This error occurs when PostgreSQL cannot find a custom type referenced in a function, procedure, or variable declaration. Common causes include missing schema qualification of custom types, incomplete migrations, enum type naming mismatches, or using non-PostgreSQL-compatible types like STRING instead of TEXT.
The "type does not exist" error (error code 42704) appears when PostgreSQL's query parser or function compiler encounters a reference to a data type that it cannot locate in the current schema or the search path. PostgreSQL allows custom types such as enums, composite types, and domain types to be defined within schemas. When you reference a custom type in a stored procedure, function declaration, or variable assignment, PostgreSQL must be able to locate that type. If the type is defined in a non-public schema, it must be explicitly qualified with the schema name, or the schema must be in the search_path. If the type name is misspelled, the type hasn't been created yet, or it was created in a different schema than you're searching, PostgreSQL will report that the type does not exist.
Use PostgreSQL's describe types command to list all custom types available in the database:
\dTThis will show all custom types. To see types in a specific schema:
\dT public.*
\dT myschema.*You can also query the information schema:
SELECT type_name, type_schema
FROM information_schema.user_defined_types
WHERE type_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY type_schema, type_name;This will show you the exact name and schema of your custom type. If the type doesn't appear, it either hasn't been created yet or it's in a different database.
When referencing a custom type in a function or procedure, use the schema-qualified name format: schema_name.type_name.
Example of the problem:
-- Type defined in 'myschema' schema
CREATE TYPE myschema.user_record AS (
id INT,
name TEXT
);
-- Function fails if type is not qualified
CREATE FUNCTION get_user() RETURNS user_record AS $$
BEGIN
-- ERROR: type "user_record" does not exist
END;Solution - use full qualification:
-- Correct - fully qualified type name
CREATE FUNCTION get_user() RETURNS myschema.user_record AS $$
DECLARE
result myschema.user_record;
BEGIN
result.id := 1;
result.name := 'John';
RETURN result;
END;
$$ LANGUAGE plpgsql;Alternatively, if the type is in the public schema, you can usually omit the schema:
-- If user_record is in public schema, this works
CREATE FUNCTION get_user() RETURNS user_record AS $$
BEGIN
-- This is fine for public schema types
END;If your custom type is defined in a non-public schema, ensure that schema is in the search_path:
-- View the current search_path
SHOW search_path;This typically shows something like: "$user", public
If your type is in a custom schema, add it to the search_path:
-- Set search_path to include your schema
SET search_path TO myschema, public;
-- Now you can reference types without full qualification
CREATE FUNCTION get_user() RETURNS user_record AS $$
BEGIN
-- Works now because myschema is in search_path
END;To make this permanent for a specific user or database:
-- For a specific user
ALTER USER myuser SET search_path = myschema, public;
-- For a specific database
ALTER DATABASE mydatabase SET search_path = myschema, public;When using enum types, verify the exact name matches. PostgreSQL doesn't enforce a specific naming convention, so the enum could be named either way:
-- Check what enum types exist and their exact names
SELECT typname, typtype FROM pg_type
WHERE typtype = 'e'
ORDER BY typname;Example of a naming mismatch:
-- If your enum is named this:
CREATE TYPE account_access_permissions_enum AS ENUM (
'read', 'write', 'admin'
);
-- And your function looks for this (wrong):
CREATE FUNCTION check_permission(p account_access_permissions)
RETURNS BOOLEAN AS $$
BEGIN
-- ERROR: type "account_access_permissions" does not exist
END;Solution - use the correct enum name:
-- Correct - matches the exact enum name
CREATE FUNCTION check_permission(p account_access_permissions_enum)
RETURNS BOOLEAN AS $$
BEGIN
-- Works now
RETURN p = 'admin';
END;
$$ LANGUAGE plpgsql;Ensure you're using type names that exist in PostgreSQL. Some database systems (like CockroachDB) have different type names:
-- Wrong - STRING type doesn't exist in PostgreSQL
CREATE TYPE user_record AS (
name STRING
);
-- ERROR: type "string" does not exist
-- Correct - use TEXT instead
CREATE TYPE user_record AS (
name TEXT
);
-- Or use VARCHAR with a length limit
CREATE TYPE user_record AS (
name VARCHAR(255)
);Common type replacements:
| Non-PostgreSQL Type | PostgreSQL Equivalent |
|---------------------|----------------------|
| STRING | TEXT or VARCHAR |
| INT64 | BIGINT or INT |
| FLOAT64 | DOUBLE PRECISION or NUMERIC |
When migrating from other databases, check the migration carefully and replace non-standard types.
In migration files, custom types must be created before functions that use them:
-- Migration 001_create_types.sql
CREATE TYPE public.user_record AS (
id INT,
name TEXT
);
-- Migration 002_create_functions.sql
CREATE FUNCTION get_user() RETURNS public.user_record AS $$
BEGIN
-- Works because type was created in previous migration
END;
$$ LANGUAGE plpgsql;If the order is wrong:
-- Wrong order - function before type
CREATE FUNCTION get_user() RETURNS public.user_record AS $$
-- ERROR: type "user_record" does not existCheck your migration file naming and ordering. If using Prisma or another ORM, verify that the migration sequence creates types before functions that use them.
Composite types in procedures: When passing composite types to stored procedures, the type must be fully qualified if it's not in the public schema:
CREATE TYPE audit.change_record AS (
table_name TEXT,
operation TEXT,
timestamp TIMESTAMP
);
CREATE PROCEDURE log_change(change audit.change_record) AS $$
BEGIN
INSERT INTO audit.log VALUES (change.table_name, change.operation, change.timestamp);
END;
$$ LANGUAGE plpgsql;Dynamic type creation: If you're creating types dynamically via application code, ensure the CREATE TYPE statement completes before any function that uses it is created or called.
Schema-qualified SELECT statements: You can also encounter this error when selecting from types in non-public schemas:
-- If user_record type is in myschema, select from it like this:
SELECT * FROM CAST('{1, "John"}' AS myschema.user_record);
-- Or use the fully qualified name in any type context
CAST(value AS myschema.custom_type)Type inheritance and composite types: Custom composite types don't support inheritance, but you can create multiple related types:
CREATE TYPE person AS (id INT, name TEXT);
CREATE TYPE employee AS (person person, salary NUMERIC);
-- Use in function
CREATE FUNCTION get_employee() RETURNS employee AS $$
DECLARE
emp employee;
BEGIN
emp.person.id := 1;
emp.person.name := 'Alice';
emp.salary := 50000;
RETURN emp;
END;
$$ LANGUAGE plpgsql;Domain types: PostgreSQL also has domain types, which are user-defined types based on existing types:
-- Create a domain
CREATE DOMAIN valid_email AS TEXT CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');
-- Use it like any other type
CREATE TABLE users (email valid_email);
-- Also reference it fully if in non-public schema
CREATE FUNCTION validate_email(e myschema.valid_email) RETURNS BOOLEAN AS $$
BEGIN
RETURN e IS NOT NULL;
END;
$$ LANGUAGE plpgsql;ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL