Thread: How to drop a value from an ENUM?
Hi,
I am absolutely sure a certain value of one of my ENUM types is not used in the entire database. Now I am asked to drop that value. Unfortunately, there is no ALTER TYPE DROP VALUE.
On my development box I tried
delete from pg_enum
where enumtypid='my_type_name'::regtype
and enumlabel='my_label'
It worked and I could not find any adverse effects.
Given the value is not used anywhere, is this a save command?
Thanks,
Torsten
On Mon, May 28, 2018 at 11:08 PM, Torsten Förtsch <tfoertsch123@gmail.com> wrote:
Hi,I am absolutely sure a certain value of one of my ENUM types is not used in the entire database. Now I am asked to drop that value. Unfortunately, there is no ALTER TYPE DROP VALUE.On my development box I trieddelete from pg_enumwhere enumtypid='my_type_name'::regtype and enumlabel='my_label'It worked and I could not find any adverse effects.Given the value is not used anywhere, is this a save command?Thanks,Torsten
Well, imho, you should avoid enums at all cost. As you have discovered, enums are hard to
maintain and have long been replaced by Foreign Keys.
With that being said, apparently your command was safe. However, the best way is to do the following.to drop/delete an enum.
1. Determine that the particular enum value is NOT referenced by any column of any table in the database.
2. As a superuser, use the following queries:
SELECT t.typname,
e.enumlabel,
e.enumsortorder,
e.enumtypid
FROM pg_type t
JOIN pg_enum e ON e.enumtypid = t.oid
WHERE t.typtype = 'e'
AND e.enumlabel = 'your_enum_value'
ORDER BY 1, e.enumsortorder;
DELETE FROM pg_enum
WHERE enumtypid = <e.enumtypid from previous query>
AND enumlabel = 'your_enum_value';
--
2. As a superuser, use the following queries:
SELECT t.typname,
e.enumlabel,
e.enumsortorder,
e.enumtypid
FROM pg_type t
JOIN pg_enum e ON e.enumtypid = t.oid
WHERE t.typtype = 'e'
AND e.enumlabel = 'your_enum_value'
ORDER BY 1, e.enumsortorder;
DELETE FROM pg_enum
WHERE enumtypid = <e.enumtypid from previous query>
AND enumlabel = 'your_enum_value';
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:
> I am absolutely sure a certain value of one of my ENUM types is not used in
> the entire database. Now I am asked to drop that value. Unfortunately,
> there is no ALTER TYPE DROP VALUE.
Yup.
> On my development box I tried
> delete from pg_enum
> where enumtypid='my_type_name'::regtype
> and enumlabel='my_label'
> It worked and I could not find any adverse effects.
> Given the value is not used anywhere, is this a save command?
No. If it were, we'd have an ALTER DROP VALUE command.
The key problem that is hard to fix here is that, even if today you have
no live rows containing that value, it may still be present in indexes.
In a btree, for example, the value might've migrated up into upper index
pages as a page boundary value. Once that's happened, it's likely to
persist indefinitely, even if the live occurrences in the underlying table
get deleted and vacuumed away.
Now, even if that's happened, you might be okay, because of the
optimizations that typically allow enum value comparisons to be done
without consulting pg_enum. But if you're in the habit of altering
enums, it's that much more likely that you would have done an ALTER TYPE
that defeats those optimizations; so I wouldn't rely on this. Sooner
or later you're going to get burnt by complaints about an invalid enum
value (not sure of the exact wording) when you access certain parts
of the index.
You could maybe get around all of that by reindexing any indexes
containing the altered enum type after you're certain that all
entries of the unwanted enum value are dead and vacuumed away.
But it's not terribly safe.
regards, tom lane