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.

Laurenz Albe · Accepted Answer · 2019-02-25 16:01:51Z

2

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.

answered Feb 25, 2019 at 16:01

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adam Robinson Over a year ago

Thanks for your reply Laurenz, I've actually already tried your recommendation this afternoon with no luck. I get the following error result of USING clause for column "roles" cannot be cast automatically to type user_role this is an example of the casting issue that I mentioned

Adam Robinson Over a year ago

Which version of Postgres are you running, Laurenz?

Laurenz Albe Over a year ago

Version 11.2, but I don't think that changed recently.

Jeremy Caney · Accepted Answer · 2020-10-26 22:33:16Z

2

I had this exact same issue after using Laurenz Albe's suggestion, which yielded the following error:

Result of USING clause for column "roles" cannot be cast automatically to type user_role

What fixed this for me was to also update the TYPE user_role (enum) in ALTER COLUMN to user_role[] (an array of enums):

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

edited Oct 26, 2020 at 22:33

Jeremy Caney

7,798115 gold badges58 silver badges87 bronze badges

answered Oct 26, 2020 at 21:44

Carrie

214 bronze badges

Comments

Adam Robinson · Accepted Answer · 2019-02-27 12:53:01Z

0

So after checking which version of Postgres we were running I found we we're on

PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit

which explained why this worked

-- postgres-migrations disable-transaction
ALTER TYPE user_role ADD VALUE 'ADMIN';

but not,

ALTER TYPE user_role RENAME VALUE CONTENT TO EDITOR

RENAME VALUE is only supported by PG 10 or above.

Looked on Compose, they don't seem to support PG 10 yet.

Changed database provider to AWS running V.10 and it worked.

Still unsure why I could't get Laurenz's solution to work because it also seems correct.

answered Feb 27, 2019 at 12:53

Adam Robinson

1352 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related