Why does Postgres say the type does not exist when I clearly define it in the same file?

Postgres executes statements top to bottom. If the CREATE TABLE that uses the enum runs before the CREATE TYPE that defines it, the type genuinely does not exist yet at that point. Order matters, not file membership.

How do I add a new value to an existing enum during migration?

Use ALTER TYPE public.user_role ADD VALUE 'moderator';. Before Postgres 12 this could not run inside a transaction block; on 12+ it can, but the new value is not usable in the same transaction that adds it.

Does the schema name in the error matter?

Yes. type "public.user_role" does not exist is schema-qualified. If the type was created in another schema, or search_path does not include the schema it lives in, Postgres will not resolve it. Fully qualify the type or fix search_path.

schemaSQLSTATE 42704

type "public.user_role" does not exist

What it means

Postgres raises this when a statement references an enum or composite type that has not been created yet — usually a CREATE TABLE with an enum column that a later CREATE TYPE would have defined. The fix is to run CREATE TYPE before the CREATE TABLE that uses it. SQLSTATE is 42704 (undefined_object).

Why it happens

type "public.user_role" does not exist (SQLSTATE 42704, undefined_object) means a statement referenced a user-defined enum or composite type that Postgres cannot resolve at the moment it runs. In a migration this comes from one of three causes.

Wrong DDL order. A CREATE TABLE declares a column of type public.user_role, but the CREATE TYPE public.user_role AS ENUM (...) runs later in the script — or not at all. Postgres executes top to bottom, so the table statement fails before the type is ever defined.
The type lives in another schema, or search_path is wrong. The enum was created in a different schema, or the connection's search_path does not include the schema that holds it. An unqualified reference like user_role then resolves to nothing.
The type was never extracted. When schema is reconstructed by querying information_schema and pg_catalog rather than with pg_dump, enums and composite types have to be pulled from pg_type / pg_enum explicitly. Miss that step and every table column that uses the type fails on replay.

How to fix it

First confirm whether the type actually exists on the destination and in which schema:

-- List every user-defined enum and its values
select n.nspname   as schema,
       t.typname   as type,
       e.enumlabel as value,
       e.enumsortorder
from pg_type t
join pg_namespace n on n.oid = t.typnamespace
left join pg_enum e on e.enumtypid = t.oid
where t.typtype = 'e'
order by schema, type, e.enumsortorder;

If the type is missing, create it before the table that uses it:

create type public.user_role as enum ('admin', 'member', 'viewer');

create table public.profiles (
  id    uuid primary key references auth.users (id),
  role  public.user_role not null default 'member'
);

If the type exists but is missing a value your data needs, add it:

alter type public.user_role add value 'moderator';

Add it before a specific label if ordering matters:

alter type public.user_role add value 'moderator' before 'viewer';

Note: on Postgres 12+ ALTER TYPE ... ADD VALUE can run inside a transaction, but the newly added value cannot be used in that same transaction — commit first, then reference it.

If the type exists in a schema that is not on the search path, either fully qualify it (public.user_role, as above) or set the path for the session:

set search_path to public;

The reliable ordering for a full schema replay is: extensions -> types -> tables -> foreign keys -> indexes -> functions -> triggers -> policies. Enums and composite types come right after extensions and before any table that references them. See schema replay for the full dependency order.

How to prevent it

Emit CREATE TYPE statements first. Group all enum and composite type definitions ahead of CREATE TABLE in the generated DDL. SupaMigrate extracts types from pg_type/pg_enum and replays them immediately after extensions, so a column typed public.user_role always resolves.
Always schema-qualify types in column definitions (public.user_role, not user_role). This removes any dependence on search_path during replay.
Diagnose type vs. table errors correctly. SQLSTATE 42704 is a missing type; a missing table or view is 42P01 — see relation "public.profiles" does not exist. They read similarly but point at different objects.
Check enum values against your data before inserting. If a row carries a label the destination enum does not have, the insert fails on a check that looks unrelated. Run the diagnostic query above and reconcile labels with ALTER TYPE ... ADD VALUE first.

Frequently asked questions

Why does Postgres say the type does not exist when I clearly define it in the same file?: Postgres executes statements top to bottom. If the CREATE TABLE that uses the enum runs before the CREATE TYPE that defines it, the type genuinely does not exist yet at that point. Order matters, not file membership.
How do I add a new value to an existing enum during migration?: Use ALTER TYPE public.user_role ADD VALUE 'moderator';. Before Postgres 12 this could not run inside a transaction block; on 12+ it can, but the new value is not usable in the same transaction that adds it.
Does the schema name in the error matter?: Yes. type "public.user_role" does not exist is schema-qualified. If the type was created in another schema, or search_path does not include the schema it lives in, Postgres will not resolve it. Fully qualify the type or fix search_path.

Related errors