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

On 31 March 2016 at 14:35, Nik Mitev <nik@mitev.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