5 ms·
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 negligibl
by jpgvm 3y ago
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.
- marcosdumay 3y agoPostgreSQL 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.
- wojcikstefan 3y agoCan you elaborate on what it means to use PostgreSQL enums "on your code API"? > 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#sqlalchemy.types.Enum.params.native_enum https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqla...
- aidos 3y agoHeyya! Close customer here. > When False, uses VARCHAR + check constraint for all backends Is this behaviour new? Or is it that alembic still doesn't pick up changes to the enum to recreate the constraint? We implemented our own alembic hooks to automate the migrations for us. We've found the check constraint model to be fairly effective.
- wojcikstefan 3y agoHiya Aidan! :D > Is this behaviour new? Don't think so. Even the docs for SQLA v1.3 ([0]) mention it: > native_enum – Use the database’s native ENUM type when available. Defaults to True. When False, uses VARCHAR + check constraint for all backends. The VARCHAR length can be controlled with Enum.length [0]: https://docs.sqlalchemy.org/en/13/core/type_basics.html#sqlalchemy.types.Enum.params.native_enum https://docs.sqlalchemy.org/en/13/core/type_basics.html#sqla... -- > Or is it that alembic still doesn't pick up changes to the enum to recreate the constraint? Off the top of my head, I believe Alembic doesn't pick it up automatically. We make a lot of manual tweaks to our auto-generated Alembic migrations and I believe adding / modifying the CHECK constrains is part of it.
- aidos 3y agoHappy to ping you guys over the code if you want to look. It’s probably fairly tweak-able to handle your specific cases.
- marcosdumay 3y ago> Can you elaborate on what it means to use PostgreSQL enums "on your code API"? If you export a procedure for creating a socket into pgPlSQL, you shouldn't use magical numbers for setting the socket flags. You should use enums. As for sqlalchmey, well that design is not good. It should support more mappings than just to string. But well, personally, I would ignore the feature and go without enum types (notice that it's a recent addition). You can always declare your own enum and convert the column if you want.
- faangsticle 3y ago> But well, personally, I would ignore the feature and go without enum types (notice that it's a recent addition). Postgres has had enums since 2008
- aidos 3y agoI still don't totally understand that first point. Are you talking about using enums for controlling the connection itself? Can you give an example? Just to clarify, sqlalchemy supports any arbitrary mapping you want — it's entirely flexible in every direction. EDIT ah, do you mean something like this? CREATE FUNCTION do_thing(flags custom_enum)
- marcosdumay 3y agoYep, you use it as CREATE FUNCTION do_something(some_option custom_enum). The main use-case is not putting them on tables. Relational algebra has the concept of enums builtin (as long as they all have the same structure). You create them in databases by using foreign keys.
- cryptonector 3y agoPG enums are "enum tables" under the hood. With native enum support the JOINs with the enum tables happen at query compilation time, which is a performance benefit you should not ignore.