Search code examples
postgresql

Drop value from enum with following column type update in Postgres


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;

Executing those commands resulted in an exception:

ERROR:  default for column "models" cannot be cast automatically to type model[] 

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?


Solution

  • The exception occurs because there is a default for the models column. The following sequence produces the intended final result:

    ALTER TYPE model RENAME TO model_old;
    
    CREATE TYPE model AS ENUM('b2b', 'b2c');
    
    ALTER TABLE comp ALTER COLUMN models DROP DEFAULT,
                     ALTER COLUMN models TYPE model[] USING models::VARCHAR::model[],
                     ALTER COLUMN models SET DEFAULT ARRAY[]::model[];
    
    DROP TYPE model_old;
    

    I assume the reason for having a default for models is to avoid having to account for NULL; if so, then it is imperative that the column be declared NOT NULL.