CODEWITHSUNDEEP
postgresqlenums
Arnav Mishra
Arnav Mishra

Reputation: 23

Are there restrictions on removing an enum value on Postgres

I'm trying to remove a value from an enum in Postgres and it has been failing. I've done this many times before with no issues so I'm not sure if something has changed or if I'm running into some edge case restriction.

Here is what I'm running (removing the enum FOURTH):

ALTER TYPE "my_enum" RENAME TO "my_enum_old";
CREATE TYPE "my_enum" AS ENUM('FIRST', 'SECOND', 'THIRD', 'FIFTH');
ALTER TABLE "my_Table" ALTER COLUMN "my_column" TYPE "my_eum" USING "my_column"::"text"::"my_enum";
DROP TYPE "my_enum_old";

Perhaps there is a size restriction on the length of the enum name? Our full enum name is the following length (Xs used to hide real name) XXXXXXXXXXXXX_XXXXXXX_XXXXXXXX_XXXXXXXXX_XXXXXXXX_XXXXX_XXXX and when adding old to the end that adds another _OLD.

The error being returned is

ERROR:  operator does not exist: my_enum = my_enum_old
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Upvotes: 1

Views: 438

Answers (2)

jjanes
jjanes

Reputation: 44202

The most likely problem is that you have a check constraint on the table which does something like:

 check (not(my_column = 'FOURTH'::my_enum_old)));

Once my_column changes type, that constraint can no longer be checked. Just drop the constraint.

Upvotes: 1

Schwern
Schwern

Reputation: 164859

By default Postgres identifiers have a limit of 63 bytes. You should have gotten a warning like:

NOTICE:  identifier "XXXXXXXXXXXXX_XXXXXXX_XXXXXXXX_XXXXXXXXX_XXXXXXXX_XXXXX_XXXX_OLD" will be truncated to "XXXXXXXXXXXXX_XXXXXXX_XXXXXXXX_XXXXXXXXX_XXXXXXXX_XXXXX_XXXX_OL"
ALTER TYPE

You will have to choose a shorter identifier.

Note: while XXX and xxx are the same identifier, "XXX" and XXX are different identifiers. If sometimes you are double quoting and sometimes you are not, that could lead to problems.

This is why one should avoid quoting identifiers unless necessary.

Note that in this instance instead of recreating the enum, you could simply rename the value.

alter type my_enum rename value 'FOURTH' to 'FIFTH';

If you find yourself changing enums frequently, and having so many they require large identifiers, this may not be a good use of enums. Consider a join table instead.

create table thing_names (
  id bigserial primary key,
  label not null text
);

insert into things values
  (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD'), (4, 'FOURTH');

create table stuff (
  id bigserial primary key,
  thing_id bigint not null references things
);

insert into stuff values (1, 1), (2, 4), (3, 2);

select stuff.id, things.label
from stuff
join things on stuff.thing_id = things.id;

 id | label  
----+--------
  1 | FIRST
  2 | FOURTH
  3 | SECOND

update things set label = 'FIFTH' where id = 4;

select stuff.id, things.label
from stuff
join things on stuff.thing_id = things.id;

 id | label  
----+--------
  1 | FIRST
  2 | FIFTH
  3 | SECOND

Upvotes: 0

Related Questions