Representing Enums in PostgreSQL
making.close.com
Representing Enums in PostgreSQL
1–10 of 76 posts
Re: Representing Enums in PostgreSQL
#2Little known bug, but enums also choke dump/restore if you use them as a hash partition key.
That said, they can be really nice for all the same reasons static types are nice. Good article!
Re: Representing Enums in PostgreSQL
#3I agree with this, I don't use enums they are always more trouble than they are worth.
They break FDW unless they are pre-created on the importing side. Super inconvenient.
Re: Representing Enums in PostgreSQL
#4PostgreSQL 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.
Re: Representing Enums in PostgreSQL
#5After 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.
I think the "right" choice for enums probably looks a little more like:
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),
...
);
Re: Representing Enums in PostgreSQL
#6smallint 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
Re: Representing Enums in PostgreSQL
#7After 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, y…
This is exactly what I do, save with natural keys.
Re: Representing Enums in PostgreSQL
#8PostgreSQL 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.
PostgreSQL enums are there for you to use on your code API. They can go into tables because sometimes it's important to put code data into tables.
What they are absolutely not for is to replace the one standard way to define data enums that everybody use since relational algebra was created. It's such an outrageously naive idea that I'm sure most people here were attracted to the title thinking it's about algebraic types or some other similar misunderstanding.
Re: Representing Enums in PostgreSQL
#9smallint 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
Just to make sure I understand your suggestion fully, you're saying:
1. Use `smallint` instead of `text` for the column type.
2. Otherwise follow our "`CHECK` constraint" approach (without it we're jeopardizing data consistency because we can store anything between -32768 and 32767 in that column).
3. Translate the int to the enum in your application.
Right?
Re: Representing Enums in PostgreSQL
#10Native enums in Postgres just seem to have many more drawbacks than advantages. I never really used them, it didn't seem worth it.
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.