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

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

Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>