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

On 31/03/16 14:14, Sándor Daku wrote:
> On 31 March 2016 at 14:35, Nik Mitev <nik(at)mitev(dot)eu
> <mailto: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