Create an enum column in Supabase – 2026 guide

TL;DR#

If you're seeing the need to store a limited set of string values in Supabase and wonder how to model it, the cause is simply that PostgreSQL requires an enum type before you can attach it to a column. Fix it by creating the enum type with CREATE TYPE, then adding or altering the column to use that type.

If that doesn't work, scroll to verify the fix — there are two common variants this guide also covers.

What you'll see#

Developers often land on this page after typing a query like the one below into Google:

How can I create an enum column in Supabase

The underlying problem appears when you try to run a migration that adds an enum column and PostgreSQL throws:

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

It happens when you are building a new table for an e‑commerce order flow, or when you need to retrofit an existing orders table with a status field that should only accept "pending", "shipped" or "delivered". The error reproduces on the Supabase dashboard SQL editor, locally with psql, and in CI pipelines that run supabase db push.

Root cause#

Supabase is a thin wrapper around PostgreSQL. Unlike some NoSQL services, 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.

A second subtlety is that Supabase’s migration system (supabase db push or supabase migration new) treats each SQL file as a separate transaction. If you try to create the enum and the column in the same file without a COMMIT between them, the second statement may run before the type is fully registered, especially when using the dashboard’s “Run” button which splits statements.

The relevant code path in Supabase’s CLI is the pg_dump/psql wrapper that streams each statement sequentially. When the wrapper encounters a CREATE TYPE it sends it to the server, but the server only acknowledges the type after the transaction commits. Therefore, a subsequent ALTER TABLE in the same transaction can fail.

The fix#

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 deliberately split into two separate files to avoid the transaction ordering issue.

1️⃣ Create the enum type#

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

-- Expected output (psql)
CREATE TYPE

2️⃣ Add the column to a new table (or alter an existing one)#

-- 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

3️⃣ Alter an existing column to use the enum (variant)#

If the orders table already has a status column of type text, you can migrate it safely:

-- 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

That single change addresses the cause because the enum type now exists before PostgreSQL tries to bind it to the column, and the USING clause safely casts existing rows.

Step by step#

1. Open the Supabase dashboard, navigate to SQL editor, and click New migration.
2. Paste the CREATE TYPE snippet into the first migration file, give it a descriptive name, and click Run.
3. Create a second migration file for the ALTER TABLE (or combined file if you’re starting fresh) and run it.
4. Verify the column appears in the Table editor and that the enum values are selectable.

Verify the fix#

Run the following query to confirm the enum type and column are registered:

supabase db remote status

Or, using psql directly:

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 two common variants are covered below.

Variant A — Enum already exists but with a different name#

If another developer created order_status instead of status_enum, rename the column to use the existing type:

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

Variant B — Adding enum to a table with existing rows that contain invalid values#

If some rows contain a status outside the allowed set, the cast will fail. Clean the data first:

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

Then run the ALTER COLUMN … TYPE statement.

Why this happens (and how to avoid it next time)#

PostgreSQL treats enums as first‑class types that must be declared before use. Supabase’s migration tooling runs each SQL file in its own transaction, so mixing CREATE TYPE and ALTER TABLE in the same file can lead to a race condition where the type isn’t visible yet. To avoid the regression, always separate enum creation into its own migration file or wrap the statements in a DO block that checks for existence, as shown in the variant above.

Adding a lint rule to your CI pipeline that scans migration files for CREATE TYPE without a preceding comment can catch accidental omissions early. You can also enable Supabase’s Database > Settings > Enable RLS and write a simple audit trigger that logs any attempt to insert a value not in the enum—this 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 Audit Checklist: Problem -> Fix -> Guide.

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