29

I found how to add value to the TYPE. But how can I remove value from it?
For example I have TYPE with enum values ('A','B','C'). How to remove 'C'?

Alex
  • 11,451
  • 6
  • 37
  • 52
  • If you need to do that, an `enum` is most probably the wrong choice in the first place. Use a second table and a foreign key between the two tables –  Sep 17 '14 at 12:26

1 Answers1

60

To remove value ('val1') from enum ('enum_test') you can use:

DELETE FROM pg_enum
WHERE enumlabel = 'val1'
AND enumtypid = (
  SELECT oid FROM pg_type WHERE typname = 'enum_test'
)
marzapower
  • 5,531
  • 7
  • 38
  • 76
snaiffer
  • 700
  • 1
  • 5
  • 7
  • Very neat and short, therefore much less error prone. Thanks a lot ! – cgte Jun 14 '17 at 14:09
  • 3
    one has to be aware that it can be dangerous, because if the deleted enum value exists somewhere in some tables in the database, the delete still works without complaining but all the records with the deleted value become corrupt. E.g. any SELECT query that has any such record in its result set, crashes. – leszy77 Apr 10 '18 at 12:37