Adding and Renaming a field to ENUM Type in Postgres and preserving multiple ENUM types to be used in a field

Question

I'm using Postgres and postgres-migrations to handle database migrations.

In the first migration script a TYPE based on the following ENUM labels has been defined. A single user can have multiple roles.

CREATE TYPE user_role AS ENUM('PRIMARY', 'BILLING', 'CONTENT');

This type has been used so far in two tables across the DB in an array of assigned user_roles[] - column roles (here's an example of one):

CREATE TABLE memberships (
id uuid DEFAULT uuid_generate_v1() PRIMARY KEY,
organisation_id uuid NOT NULL REFERENCES 
organisations(id),
roles user_role[] NOT NULL,
user_id uuid NOT NULL REFERENCES users(id)
);

My aim was to add a further user role of 'ADMIN' while also wanting to rename 'CONTENT' to 'EDITOR' in a later migration script.

I initially tried,

ALTER TYPE user_role ADD VALUE 'ADMIN';

ALTER TYPE user_role RENAME VALUE 'CONTENT' TO 'EDITOR';

However I ran into the following error:

ALTER TYPE ... ADD cannot run inside a transaction block at runMigrations

After many attempts at solutions found across the web I've come to this:

ALTER TYPE user_role RENAME TO user_role_old;

CREATE TYPE user_role AS ENUM('PRIMARY', 
'ADMIN', 'EDITOR', 'BILLING');

ALTER TABLE memberships ALTER COLUMN roles TYPE 
user_role USING roles::text::user_role;

ALTER TABLE organisation_invites ALTER COLUMN 
roles TYPE user_role USING 
roles::text::user_role;

DROP TYPE user_role_old;

This is giving the correct labels within the user_role type now, However this replaces the roles column type of user_roles[] with user_roles. Therefore I can now only assign a single role to a user which is no good. I believe this is something to do with casting and using text, however I'm unsure.

Answer 1

You should use arrays throughout. I am surprised that these statements don't cause an error (is the column NULL for all rows?).

ALTER TABLE memberships
   ALTER COLUMN role TYPE user_role
   USING roles::text[]::user_role[];

As you see, you are getting in trouble using enum types as soon as the values change.

My advice is to use lookup tables instead if there is a possibility that values might change or go away.