Postgres Enum Type Generator

PostgreSQL enums are easy to create and surprisingly awkward to change: there is no DROP VALUE, ADD VALUE has transaction rules that break migration tools, and every dependent column must be cast when you rebuild the type. This generator writes the correct SQL for each situation. Enter the type name and its values, choose an operation, and get the forward migration, the rollback statements and the notes that matter, such as which steps rewrite the table or must run outside a transaction. Runs entirely in your browser.

OperationSchemaType nameTableColumnDefaultNOT NULL

Current enum values (one per line, in sort order)

Generated SQL

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

ALTER TABLE public.orders
  ADD COLUMN status public.order_status NOT NULL DEFAULT 'pending';

-- Enum values compare by declaration order, not alphabetically
SELECT * FROM public.orders WHERE status < 'cancelled';

-- Inspect the values and their sort order
SELECT enumlabel, enumsortorder
FROM pg_enum
WHERE enumtypid = 'public.order_status'::regtype
ORDER BY enumsortorder;

Rollback (down migration)

ALTER TABLE public.orders DROP COLUMN status;
DROP TYPE public.order_status;

Notes and warnings

1. An enum value occupies 4 bytes on disk, and comparisons use the declaration order shown above, so ORDER BY on the column sorts by that order rather than alphabetically.

2. Labels are case-sensitive and limited to 63 bytes. 'Paid' and 'paid' are different values.

Do more than postgres enum type generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the schema, enum type name and the current values, one per line in their sort order, plus the table and column that use the type.
Pick an operation: create the type and column, add or rename a value, remove a value safely, or convert the enum to a CHECK constraint or lookup table.
Copy the generated SQL into your migration file, keep the rollback block for the down migration, and read the notes before running it in production.

Frequently asked questions

How do I add a value to an existing Postgres enum?

Run ALTER TYPE type_name ADD VALUE 'new_value', optionally with IF NOT EXISTS and BEFORE or AFTER an existing label to control the sort position. Since PostgreSQL 12 this can run inside a transaction, but the new label cannot be used by any statement in that same transaction, which fails with 'unsafe use of new value'. Migration tools such as Flyway, Prisma, Django and Alembic wrap each migration in a transaction, so put the ADD VALUE in its own migration and reference the value in a later one. On PostgreSQL 11 and earlier the statement cannot run in a transaction block at all.

How do I remove a value from a Postgres enum?

There is no ALTER TYPE ... DROP VALUE. The safe recipe, which the generator writes for you, is: update rows that still use the value, create a new enum type without it, alter the column to the new type with USING column::text::new_type, drop the old type and rename the new one to the original name. Do everything in a single transaction so a failure leaves nothing half-changed, and remember that ALTER COLUMN TYPE rewrites the table and rebuilds its indexes under an ACCESS EXCLUSIVE lock. Deleting rows from pg_enum by hand is not a shortcut: stored values would point at labels that no longer exist.

Should I use an enum, a CHECK constraint or a lookup table?

Use an enum when the set of values is small, stable and needs a defined sort order; it stores 4 bytes per row and validates on write. Use text with a CHECK constraint when values change occasionally and you want plain DDL that every ORM understands, at the cost of alphabetical rather than declared ordering. Use a lookup table with a foreign key when values change often, need metadata such as display labels or an active flag, or are shared by several tables, because adding a value becomes a simple INSERT. The generator can convert an existing enum column to either alternative. Chat2DB shows enum types, constraints and foreign keys side by side in its schema browser and can run these migrations for you: download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.