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

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&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&gt;
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On 31/03/16 14:14, Sándor Daku wrote:

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

On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev <nik@mitev.eu> wrote:

On 31/03/16 14:14, Sándor Daku wrote:

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

It is not easy to delete values from enums, but it can be done.
First, you need to insure that the value you want to delete is not already
stored in a column of some table(s).

So you will need to do something like:
SELECT count(*)
FROM {some_table}
WHERE {column_name} = <enum_value_to_delete>;

Then you need to get the enumtypid and sortorder for the value to delete.
The following query will provide that info.

SELECT t.typname,
e.enumlabel,
e.enumsortorder,
e.enumtypid
FROM pg_type t
JOIN pg_enum e ON e.enumtypid = t.oid
WHERE t.typtype = 'e'
AND e.enumlabel = '<enum_value_to_delete>'
ORDER BY 1, enumsortorder;

Then, you can either do:
DELETE FROM pg_enum
WHERE enumtypid = {enumtypid_from_above}
AND enumsortorder = {enumsortorder_from_above};

OR

DELETE FROM pg_enum
WHERE enumtypid = {enumtypid_from_above}
AND enumlabel = '<enum_value_to_delete>';

That being said "ENUMS are EVIL"! As others have said, it is much better to
just just Foriegn Keys for value integrity.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson <melvin6925@gmail.com> writes:

On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev <nik@mitev.eu> wrote:

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

It is not easy to delete values from enums, but it can be done.

No, it is NOT SAFE TO DO THAT. At least not unless you also drop or
reindex every index on columns of the enum type. Even if you've deleted
every occurrence of the target value appearing in table rows, and vacuumed
away those rows so that their leaf index entries are gone, the target
value could still exist in upper index pages (as a page boundary value,
for example). Delete the pg_enum entry and you'll break the index,
because enum_cmp() won't know what to do when visiting that index entry.

Concerns like this are exactly why there is no ALTER TYPE DELETE VALUE,
and probably never will be. If you need a non-fixed set of key values,
you're much better off using a foreign key instead of an enum type.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Mar 31, 2016 at 10:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Melvin Davidson <melvin6925@gmail.com> writes:

On Thu, Mar 31, 2016 at 9:24 AM, Nik Mitev <nik@mitev.eu> wrote:

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

It is not easy to delete values from enums, but it can be done.

No, it is NOT SAFE TO DO THAT. At least not unless you also drop or
reindex every index on columns of the enum type. Even if you've deleted
every occurrence of the target value appearing in table rows, and vacuumed
away those rows so that their leaf index entries are gone, the target
value could still exist in upper index pages (as a page boundary value,
for example). Delete the pg_enum entry and you'll break the index,
because enum_cmp() won't know what to do when visiting that index entry.

Concerns like this are exactly why there is no ALTER TYPE DELETE VALUE,
and probably never will be. If you need a non-fixed set of key values,
you're much better off using a foreign key instead of an enum type.

regards, tom lane

AS NOTED IN MY LAST LINE > "That being said "ENUMS are EVIL"! As others
have said, it is much better to just just Foreign Keys for value
integrity."

I have previous advised that enums are a holdover from before Foreign Keys
were available and should be avoided.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.