Column type change to enum fails: missing USING clause and existing default can't be cast

[yanpla]

Problem

Changing a table column's type to a custom enum with pgschema is not reliable if the column is currently a text or varchar type, especially when a default value is present. There are two related migration failures:

---

Issue 1: If the column contains non-enum data and pgschema generates migration SQL like:

ALTER TABLE clientstatehistory ALTER COLUMN action TYPE action_type;

PostgreSQL fails with:

ERROR: column "action" cannot be cast automatically to type action_type (SQLSTATE 42804)

This fails because PostgreSQL requires a USING clause (e. g., USING action::action_type) to specify how existing values should be cast when migrating to an enum type.

---

After fixing the issue above by hand I was introduced to a 2nd issue:

Issue 2: If the column has a default value (e.g., 'open' or 'pending' as text), even with a proper USING clause, PostgreSQL fails to cast the default automatically:

ERROR: default for column "action" cannot be cast automatically to type action_type (SQLSTATE 42804)

This requires dropping the default before the type change and re-setting it afterward with a valid enum value.

[tianzhou]

Can you please present a complete before/after schema besides the generated DDL migration

[yanpla]

Schema before:

--
-- Name: clientstatehistory; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS clientstatehistory (
    action varchar(255) DEFAULT 'Insert',
    time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    revision integer GENERATED BY DEFAULT AS IDENTITY,
    servertag varchar(31),
    clientstate varchar(64),
    data json NOT NULL,
    CONSTRAINT clientstatehistory_pkey PRIMARY KEY (revision, servertag),
    CONSTRAINT clientstatehistory_action_check CHECK (action::text IN ('Insert', 'Update', 'Delete'))
);

Schema I'm migrating to:

--
-- Name: clientstatehistory; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS clientstatehistory (
    action action_type DEFAULT 'Insert',
    time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    revision bigint GENERATED ALWAYS AS IDENTITY,
    servertag text,
    clientstate text,
    data jsonb NOT NULL,
    CONSTRAINT clientstatehistory_pkey PRIMARY KEY (revision, servertag)
);

And this enum already existed but now I'm actually using it:

--
-- Name: action_type; Type: TYPE; Schema: -; Owner: -
--

CREATE TYPE action_type AS ENUM (
    'Insert',
    'Update',
    'Delete'
);

And here's the generated migration:

ALTER TABLE clientstatehistory DROP CONSTRAINT clientstatehistory_action_check;

ALTER TABLE clientstatehistory ALTER COLUMN action TYPE action_type;

ALTER TABLE clientstatehistory ALTER COLUMN revision TYPE bigint;

ALTER TABLE clientstatehistory ALTER COLUMN servertag TYPE text;

ALTER TABLE clientstatehistory ALTER COLUMN clientstate TYPE text;

ALTER TABLE clientstatehistory ALTER COLUMN data TYPE jsonb;