TFA is all about how to make changes where you drop elements of an enum, and how hard that is. The obvious thing though is not covered: don't do that! Instead you should:<p><pre><code> a. Add CHECK() constraints on columns of
the relevant ENUM type checking that
the value is one of the "live" values.
b. RENAME "dead" ENUM values to indicate
that they're dead.
c. UPDATE ... SET column_of_that_enum_type = ...
WHERE column_of_that_enum_type IN (dead_values_here) ...
</code></pre>
(c) can be done slowly, say, with a LIMIT clause to keep load and disruptions down. Yeah, you might have "broken" data for a while, but you can make your queries report better values for columns of that ENUM type in the same way that the UPDATE would fix them.
After having suffered through the consequences of "type" enums on MySQL, and see some things go through a long life that used "enums" in the database (in multiple different databases, include Postgres), I'm not convinced that either of these are the right choice for representing enumerations. The string with check constraint seems dumb if for no other reason than if the table that uses it winds up having many rows, you're basically burning up lots of extra space for arguably no reason, and if you ever have to alter the name of an enum (or something similar), that update is going to be really expensive.<p>I think the "right" choice for enums probably looks a little more like:<p><pre><code> CREATE TABLE myEnum(
enumID SERIAL NOT NULL PRIMARY KEY,
enumName TEXT UNIQUE NOT NULL,
-- enum description or other metadata columns here.
);
CREATE TABLE foo(
...
associatedEnumID INTEGER NOT NULL REFERENCES myEnum(enumID),
...
);
</code></pre>
I think this has the benefit of being space efficient like the native typed enum, while being relatively flexible (easy to change names, add new enum values, add data about the enum itself, etc.)
PostgreSQL enums feel like a bit of a hack in general. I end up using an "enum table" approach in many cases as joining against a very small table has negligible performance impact in all but the most performance sensitive databases and foreign key constraints are a well understood quantity.
Is there a reason ALTER TYPE name ADD VALUE new_enum_value wouldn't Just Work for the first example for 99% of use cases? Seems like the only drawback highlighted for native enums is that you have to lock the whole table if you completely swap out the type of one of the columns, which... Yes, that's true, but also very rare? I guess TFA mentions removing a value from an enum, but you shouldn't do that imo - leave the data in place and ignore it at the app layer, like how you'd treat a deleted flag or similar. Or migrate it and leave the old value hanging around in the type.
Native enums can be used in queries like strings, but with type checking:<p><pre><code> select * from cust where type = ’company’ -- ok
select * from cust where type = ’c0mpany’ -- error
</code></pre>
As mentioned, they take less space. Important if you use these columns in index and have millions of rows.
Little known bug, but enums also choke dump/restore if you use them as a hash partition key.<p>That said, they can be really nice for all the same reasons static types are nice. Good article!
Native enums in Postgres just seem to have many more drawbacks than advantages. I never really used them, it didn't seem worth it.<p>I usually use C# Enums translated by EF Core now, which works perfectly fine on the C# side. The only missing part would be to give the DB the information about the enum names, so it could show those to me instead of just the raw numbers. But I assume there is no way to do that. Adding entire tables just doesn't seem worth it for this use case alone.
When would one use this approach versus a domain type [1]? How does it differ?<p>[1]: <a href="https://www.postgresql.org/docs/current/domains.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/current/domains.html</a>
I am probably missing something obvious(space/time optimization for very large/busy tables?) but isn't this a perfect example of why foreign keys exist. In fact both case studies presented by the article(enums and fixed string check constraints) are clunky enough that I would say both are wrong and they should just go with a foreign key.
I agree with this, I don't use enums they are always more trouble than they are worth.<p>They break FDW unless they are pre-created on the importing side. Super inconvenient.
smallint looks like a good alternative, with dictionary in the app or separate table. So far i've only seen storing dictionary in app source code approach