CODEWITHSUNDEEP
databasepostgresqlenums
Adam Robinson
Adam Robinson

Reputation: 125

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

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.

Upvotes: 4

Views: 4051

Answers (3)

Carrie
Carrie

Reputation: 21

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[];

Upvotes: 2

Adam Robinson
Adam Robinson

Reputation: 125

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.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246688

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.

Upvotes: 2

Related Questions