Supabase Enums: ALTER TYPE ADD VALUE in Migrations

You're building an e-commerce order flow — or retrofitting an existing orders table — and the status field should only ever hold "pending", "shipped" or "delivered". You write the migration, run it, and PostgreSQL answers:

ERROR:  type "status_enum" does not exist
LINE 1: ALTER TABLE orders ADD COLUMN status status_enum;

The error reproduces identically in the Supabase dashboard SQL editor, locally with psql, and in CI pipelines that run supabase db push.

ERROR: type "status_enum" does not exist — what PostgreSQL is telling you

Supabase is a thin wrapper around PostgreSQL, and PostgreSQL does not allow you to declare a column as an enum without first defining the enum type itself. The CREATE TYPE … AS ENUM statement registers a new type in the database catalog. If that type does not exist at the moment you run ALTER TABLE … ADD COLUMN …, PostgreSQL raises the "type does not exist" error shown above. Supabase's UI only wraps this raw SQL — there is no dashboard shortcut that creates the type for you.

So the workflow is always two ordered operations: register the type, then attach it to a column.

The migration SQL: type first, column second

Below is the minimal, production-ready set of SQL statements you can paste into the Supabase SQL editor or add to a migration file. The steps are shown split into two files — a common style preference for keeping type definitions separate from table changes, though both statements can also live in a single migration file.

Register the type:

-- migrations/20240602_create_status_enum.sql
CREATE TYPE public.status_enum AS ENUM ('pending', 'shipped', 'delivered');

-- Expected output (psql)
CREATE TYPE

Then attach the column:

-- migrations/20240602_add_status_to_orders.sql
ALTER TABLE public.orders
  ADD COLUMN status status_enum NOT NULL DEFAULT 'pending';

-- Expected output (psql)
ALTER TABLE

If you need to create the table from scratch, you can combine the enum creation and table definition in a single file, but keep the CREATE TYPE statement first:

-- migrations/20240602_create_orders_with_enum.sql
CREATE TYPE public.status_enum AS ENUM ('pending', 'shipped', 'delivered');

CREATE TABLE public.orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id),
  status status_enum NOT NULL DEFAULT 'pending',
  created_at timestamp with time zone DEFAULT now()
);

-- Expected output (psql)
CREATE TYPE
CREATE TABLE

In the dashboard: open SQL editor, click New migration, run the CREATE TYPE file first, then the ALTER TABLE (or the combined file if you're starting fresh), and confirm the column appears in the Table editor with the enum values selectable.

Retrofitting an existing text column

If the orders table already has a status column of type text, you can migrate it safely — the USING clause casts existing rows:

-- migrations/20240602_migrate_status_to_enum.sql
-- 1. Create the enum type (if not already present)
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'status_enum') THEN
    CREATE TYPE public.status_enum AS ENUM ('pending', 'shipped', 'delivered');
  END IF;
END$$;

-- 2. Convert existing text values to the enum
ALTER TABLE public.orders
  ALTER COLUMN status TYPE status_enum
  USING status::status_enum;

-- 3. Add a default if needed
ALTER TABLE public.orders
  ALTER COLUMN status SET DEFAULT 'pending';

-- Expected output (psql)
DO
ALTER TABLE
ALTER TABLE

Two situations complicate the retrofit:

Rows contain values outside the allowed set. The cast will fail. Clean the data first, then run the ALTER COLUMN … TYPE statement:

UPDATE public.orders
SET status = 'pending'
WHERE status NOT IN ('pending', 'shipped', 'delivered');

The enum already exists under a different name. If another developer created order_status instead of status_enum, point the column at the existing type instead of creating a duplicate:

ALTER TABLE public.orders
  ALTER COLUMN status TYPE order_status
  USING status::order_status;

Checking the catalog: information_schema and pg_enum

Confirm the migrations ran:

supabase migration list --linked

Or check directly with SQL:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'orders';

-- Expected output
 column_name | data_type
-------------+-----------
 id          | uuid
 user_id     | uuid
 status      | USER-DEFINED
 created_at  | timestamp with time zone

The data_type for status should be USER-DEFINED, which indicates it is using a custom enum type. To see the actual enum values:

SELECT enumlabel
FROM pg_enum
WHERE enumtypid = 'public.status_enum'::regtype
ORDER BY enumsortorder;

-- Expected output
 enumlabel
-----------
 pending
 shipped
 delivered

If you still see the original error, double-check that the enum type name matches exactly (status_enum) and that you ran the migrations in the correct order.

The one real transaction restriction: ALTER TYPE … ADD VALUE

CREATE TYPE and ALTER TABLE can safely coexist in the same migration file and even the same transaction — the type is immediately visible within the session once created. The one genuine restriction sits elsewhere: ALTER TYPE … ADD VALUE (adding a new value to an existing enum) cannot use that new value in the same transaction. The value becomes visible only after the transaction commits — extend the enum, commit, and only then write rows that use the new label.

This does not affect the initial creation workflow at all; it only bites when you later grow the enum. To avoid any ordering confusion in shared codebases, a common practice is to wrap CREATE TYPE in a DO block that checks for existence, as shown in the retrofit migration above.

Two further guardrails worth wiring in: a lint rule in your CI pipeline that scans migration files for CREATE TYPE without a preceding comment can catch accidental omissions early, and enabling Supabase's Database > Settings > Enable RLS with a simple audit trigger that logs any attempt to insert a value not in the enum gives you runtime safety while you're still iterating on the schema.

For a broader view on managing schema changes without downtime, see my guide on Zero‑Downtime Supabase Migrations and the related checklist in Supabase RLS Policy Design Patterns Beyond the Basics.

Related

• Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)
• Supabase RLS Policy Design Patterns
• Zero‑Downtime Supabase Migrations
• Insert into multiple tables with one Supabase API call 2026
• Supabase client permission denied for schema public – fix
• How to get COUNT(*) in Supabase
• Return inserted row ID in Supabase JS
• How to query using join in Supabase
• PostgreSQL DESCRIBE TABLE: The psql \d Equivalent
• Fix: Peer Authentication Failed for User "postgres"
• How to Change a PostgreSQL User Password (Supabase)
• Supabase "Database Error Saving New User" Trigger Fix

---

Originally published at https://www.iloveblogs.blog