Thread: Is it possible to delete a single value from an enum type?
Attachment
Attachment
On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev <nik@mitev.eu> wrote:
A rather obvious workaround which somehow wasn't obvious to me until I read this :)On 31/03/16 14:14, Sándor Daku wrote:On 31 March 2016 at 14:35, Nik Mitev <nik@mitev.eu> wrote:That seems to me a very unusual(a.k.a. crazy) design. :)Hi,
In summary, I am looking for the opposite functionality to 'ALTER TYPE
typename ADD VALUE IF NOT EXISTS new_value'
e.g. 'ALTER TYPE typename DELETE VALUE IF NOT USED unused_value'. The
[IF NOT USED] condition is optional, I can work around it and externally
check whether the value is used in the table.
In more detail, and especially if the above is not possible for a good
reason and me needing it means I'm doing something bad:
I have a set of values where 90% of the rows would contain for example a
small set of email addresses, repeated potentially ~100K times. The
remaining 10% are random email addresses which may appear just once. I
am currently using an enumerated type for this field, and the set of
values is dynamically updated as needed, before new data is inserted.
This works and so far all is good, storing this as an enumerated type
rather than say varchar(128) should be saving space and search time.
When I want to expire a set of data, simply deleting it from the table
could leave some enumerated type values unused, and they may never be
used again. Over time, the set of values for this enumerated type will
grow and will end up containing a huge amount of values which have long
since been deleted from the table and are unnecessary. So I am looking
for a way to remove them, without having to drop the type itself, as
that would mean dropping the table too.
The only workaround I can come up with now is copying the table to a new
one , reinitialising the type in the process, deleting the old table and
moving the updated one in its place. That would be disruptive though and
rather clunky, so I think I'd rather give up on using an enumerated type
for this value altogether...
I'd be grateful for any advice you may have.
Cheers,
NikI'd rather use a simple old fashioned table and foreign key construction to store the email addresses.Regards,Sándor
I guess it's (mostly) what the enumerated type functionality does behind the scenes anyway...
Thanks!
Nik
It is not easy to delete values from enums, but it can be done.
First, you need to insure that the value you want to delete is not already
stored in a column of some table(s).
So you will need to do something like:
SELECT count(*)
FROM {some_table}
WHERE {column_name} = <enum_value_to_delete>;
Then you need to get the enumtypid and sortorder for the value to delete.
The following query will provide that info.
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 = '<enum_value_to_delete>'
ORDER BY 1, enumsortorder;
Then, you can either do:
DELETE FROM pg_enum
WHERE enumtypid = {enumtypid_from_above}
AND enumsortorder = {enumsortorder_from_above};
OR
DELETE FROM pg_enum
WHERE enumtypid = {enumtypid_from_above}
AND enumlabel = '<enum_value_to_delete>';
That being said "ENUMS are EVIL"! As others have said, it is much better to just just Foriegn Keys for value integrity.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Thu, Mar 31, 2016 at 10:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Melvin Davidson <melvin6925@gmail.com> writes:
> On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev <nik@mitev.eu> wrote:
>>> In summary, I am looking for the opposite functionality to 'ALTER TYPE
>>> typename ADD VALUE IF NOT EXISTS new_value'
>>> e.g. 'ALTER TYPE typename DELETE VALUE IF NOT USED unused_value'.
> It is not easy to delete values from enums, but it can be done.
No, it is NOT SAFE TO DO THAT. At least not unless you also drop or
reindex every index on columns of the enum type. Even if you've deleted
every occurrence of the target value appearing in table rows, and vacuumed
away those rows so that their leaf index entries are gone, the target
value could still exist in upper index pages (as a page boundary value,
for example). Delete the pg_enum entry and you'll break the index,
because enum_cmp() won't know what to do when visiting that index entry.
Concerns like this are exactly why there is no ALTER TYPE DELETE VALUE,
and probably never will be. If you need a non-fixed set of key values,
you're much better off using a foreign key instead of an enum type.
regards, tom lane
AS NOTED IN MY LAST LINE > "That being said "ENUMS are EVIL"! As others have said, it is much better to just just Foreign Keys for value integrity."
I have previous advised that enums 
are a holdover from before Foreign Keys were available and should be avoided.
are a holdover from before Foreign Keys were available and should be avoided.--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.