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...
