MySQL ENUM → PostgreSQL Migration

Three real ways to migrate MySQL's inline ENUM column to PostgreSQL — native ENUM type, CHECK constraint, or a lookup table — and how to choose between them.

MEDIUM

Quick answer

PostgreSQL has no inline ENUM syntax on a column definition. Choose between a native PostgreSQL ENUM type (CREATE TYPE ... AS ENUM), a CHECK constraint, or a lookup table with a foreign key — the right choice depends on how often the allowed values change.

Why it breaks

MySQL's ENUM('a','b','c') is a value list declared directly on the column. PostgreSQL requires you to CREATE TYPE a named enum type first, then use it as a column type — there is no anonymous, inline enum syntax at all.

Real examples

Native PostgreSQL ENUM type

CREATE TABLE products (
  status ENUM('active','archived') NOT NULL DEFAULT 'active'
);

CREATE TYPE product_status AS ENUM ('active', 'archived');
CREATE TABLE products (
  status product_status NOT NULL DEFAULT 'active'
);

The enum type is created once and reused; PostgreSQL enforces the value list the same way MySQL did, but as a separate named type rather than an inline list.

CHECK constraint alternative

CREATE TABLE products (
  status ENUM('active','archived') NOT NULL DEFAULT 'active'
);

CREATE TABLE products (
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived'))
);

Adding a new allowed value later is a plain ALTER TABLE ... DROP CONSTRAINT / ADD CONSTRAINT — no ALTER TYPE transaction restrictions to worry about.

Safe migration options

Native ENUM type: fast comparisons and storage, but adding a new value later requires ALTER TYPE ... ADD VALUE, which cannot run inside the same transaction as other DDL in older PostgreSQL versions — best when the value set rarely changes.
CHECK (col IN (...)) constraint: no separate type to manage, and changing the allowed values is a simple ALTER TABLE ... DROP CONSTRAINT / ADD CONSTRAINT — best when the value set changes occasionally.
Lookup table with a foreign key: most flexible, and lets you attach metadata (display order, descriptions) to each value — best when the values need more than just a name, or change frequently.

PostgreSQL solution

For most cases: CREATE TYPE status_enum AS ENUM ('active', 'archived'); then use status_enum as the column type.

Validation

Before cutover

After conversion, run SELECT DISTINCT col FROM table; against the source MySQL data and confirm every distinct value that actually exists is included in whichever PostgreSQL option you chose — MySQL silently accepts values outside the declared list under some SQL modes, so real data can contain surprises.

Scan your own schema

This page documents detector rule ENUM in the DevEquiv analyzer — paste or upload your real MySQL/MariaDB schema to find every occurrence of this (and 19 other documented issues) automatically, with a prioritized readiness report.

Analyze a schema free

References

MySQL Manual — The ENUM Type (v8.0) — checked 2026-07-30
PostgreSQL Manual — Enumerated Types (v17) — checked 2026-07-30

Last verified 2026-07-30.