Supabase Enum Type Migration: Add Values Without Downtime (2026)

Supabase’s enum types are not directly mutable; adding new values requires a schema recreation, which typically means downtime.

Let’s see how we can add values to an existing enum type in Supabase without taking your application offline.

Imagine we have an enum for order statuses:

CREATE TYPE public.order_status AS ENUM ('pending', 'processing', 'shipped');

And a table using it:

CREATE TABLE public.orders (
    id serial PRIMARY KEY,
    status public.order_status NOT NULL DEFAULT 'pending'
);

Now, we want to add a 'delivered' status. If we try ALTER TYPE public.order_status ADD VALUE 'delivered';, Supabase will tell us: cannot alter type "order_status" of relation "orders": cannot alter enum type "order_status" because it is used by table "orders". This is because Supabase, like PostgreSQL, doesn’t allow direct modification of enum types if they are in use.

The standard PostgreSQL way to do this is to create a new enum type with the added value, migrate existing data, and then drop the old enum. We’ll do this in a way that minimizes disruption.

Here’s the strategy:

1. Create a new enum type with all the old values and the new one.
2. Temporarily allow NULL for the status column.
3. Update all existing rows to use a valid value from the old enum (or NULL if that’s acceptable momentarily).
4. Alter the status column to use the new enum type.
5. Update any rows that might have been inserted during the process to use the new enum.
6. Remove the old enum type.
7. Re-enable NOT NULL constraint.

Let’s execute this step-by-step.

Step 1: Create the New Enum Type

First, create a new enum type with the desired values. We’ll name it distinctly, e.g., order_status_v2.

CREATE TYPE public.order_status_v2 AS ENUM ('pending', 'processing', 'shipped', 'delivered');

Step 2: Temporarily Allow NULLs

This is a crucial step to avoid blocking writes during the migration. We’ll alter the orders table to allow NULL values for the status column.

ALTER TABLE public.orders ALTER COLUMN status DROP NOT NULL;

Step 3: Migrate Existing Data (and handle concurrent writes)

This is where we handle existing data. We need to ensure all rows have a valid enum value before we switch the column type. If you have a very large table, this could take time.

-- This command updates all existing rows to 'pending' if they are NULL.
-- If you have other default logic or specific requirements, adjust this.
UPDATE public.orders SET status = 'pending' WHERE status IS NULL;

Important: During the time between dropping NOT NULL and altering the column type, your application could insert new rows. These new rows would have the default value ('pending'). If your application logic is complex, you might need to consider a more robust strategy for handling concurrent writes, perhaps by temporarily disabling writes or using a more sophisticated update mechanism. For many cases, the default value handles this sufficiently.

Step 4: Alter the Column to Use the New Enum Type

Now, switch the status column to use the new enum type.

ALTER TABLE public.orders ALTER COLUMN status TYPE public.order_status_v2 USING status::text::public.order_status_v2;

The USING status::text::public.order_status_v2 part is important. It casts the current enum value to text and then to the new enum type. This works because the values in order_status are a subset of order_status_v2.

Step 5: Handle Any Concurrent Inserts (if necessary)

If you had a long running UPDATE in Step 3, or if your application could have inserted rows with NULL status during the migration, you might need to run a quick update to ensure all rows conform to the new type and constraints.

-- This is a safety net; typically, new inserts would have used the default 'pending'.
UPDATE public.orders SET status = 'pending' WHERE status IS NULL;

Step 6: Drop the Old Enum Type

Once you are confident that all data has been migrated and the column is using the new type, you can safely drop the old enum type.

DROP TYPE public.order_status;

Step 7: Re-enable NOT NULL Constraint

Finally, re-apply the NOT NULL constraint to the status column.

ALTER TABLE public.orders ALTER COLUMN status SET NOT NULL;

Step 8: Rename the Enum (Optional but Recommended)

For tidiness, you can rename the new enum type to match the original name.

ALTER TYPE public.order_status_v2 RENAME TO order_status;

This entire process can be wrapped in a single transaction for atomicity, except for the ALTER TYPE ... USING command, which often cannot be part of a transaction block that includes other DDL that might cause locks. You’d typically run the ALTER TYPE command on its own, and then the subsequent DROP TYPE and ALTER TABLE ... SET NOT NULL within a transaction.

The key to avoiding downtime is the temporary allowance of NULL values, ensuring that writes can continue uninterrupted while the schema is being modified. This allows existing data to be safely migrated and the column type to be updated without blocking ongoing operations.

The next challenge you’ll likely face is managing migrations for enum types that have many values or are used across numerous tables, where the UPDATE step could become a performance bottleneck.