On 31/03/16 14:14, Sándor Daku wrote: > On 31 March 2016 at 14:35, Nik Mitev <n...@mitev.eu > <mailto: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
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
signature.asc
Description: OpenPGP digital signature
