11–20 of 76 posts
I 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.
Native 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 en…
Does this approach ensure data consistency? Or could you accidentally insert a number value into your table which is not represented by any of the enum values?
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.
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 t…
> 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.
Just to share how we at Close got into this discussion (which I personally don't find as "outrageous" as you), SQLAlchemy – which we use in our Python code – uses `native_enum=True` as the default value for its `Enum` type [0], which then translates to using types in PostgreSQL:
> native_enum – Use the database’s native ENUM type when available. Defaults to True. When False, uses VARCHAR + check constraint for all backends.
[0]: https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqla...
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
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?
Little 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!
Native 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 en…
> I usually use C# Enums translated by EF Core now (...) Does this approach ensure data consistency? Or could you accidentally insert a number value into your table which is not represented by any of the enum values?
Native 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 en…
I am going down this path. There are a lot of downstream use cases (e.g. support, reporting, etc) that can benefit from having the canonical names & human-friendly labels in the database.
Keeping database in sync with code like this requires some discipline and manual scripting, but I think it's worth it.