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.

One real restriction to know about: 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. This does not affect the initial creation workflow — a CREATE TYPE followed immediately by ALTER TABLE … ADD COLUMN in the same transaction works perfectly fine; the type is visible within the same session as soon as it is created.

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 shown split into two files — this is a common style preference for keeping type definitions separate from table changes, though both statements can also live in a single migration file.

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 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 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. CREATE TYPE and ALTER TABLE can safely coexist in the same migration file — the type is immediately visible within the session once created. The one genuine restriction: if you later ALTER TYPE … ADD VALUE to extend an existing enum, you cannot reference that new value in the same transaction; commit first. To avoid any ordering confusion, a common practice is to wrap CREATE TYPE 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 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