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