I have a table with array column of enums like this:
CREATE TYPE "model AS ENUM('b2b', 'b2c', 'b2g');
CREATE TABLE IF NOT EXISTS "comp" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"models" model[] DEFAULT array[]::model[],
);
Now I need to remove b2g
from enum. Unfortunately it is impossible in Postgres with a simple way, so I followed approach “rename-create new-change type-drop old”:
ALTER TYPE mode RENAME TO model_old; -- Here is ok
CREATE TYPE model AS ENUM ('b2b', 'b2c'); -- Here is ok
ALTER TABLE comp
ALTER COLUMN models TYPE model[]
USING models::VARCHAR::model[]; -- Here fails
DROP TYPE model_old;
I cannot get how to correctly instruct postgres to make conversion or skip it (no new entries appears in enum and actually removed one already removed from models
column by previous migration.
Is it possible to skip this check or convert somehow to varchar[] and then to models[]? Any ways to achieve desired?