Re: Is it possible to delete a single value from an enum type?

On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev <nik(at)mitev(dot)eu> wrote:

>
>
> On 31/03/16 14:14, Sándor Daku wrote:
>
> On 31 March 2016 at 14:35, Nik Mitev <nik(at)mitev(dot)eu> wrote:
>
>> 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,
>> Nik
>>
>
>
> That seems to me a very unusual(a.k.a. crazy) design. :)
> I'd rather use a simple old fashioned table and foreign key construction
> to store the email addresses.
>
> Regards,
> Sándor
>
>
> A rather obvious workaround which somehow wasn't obvious to me until I
> read this :)
> 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.