[jpgvm]

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.

[cryptonector]

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

[marcosdumay]

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.

[wojcikstefan]

Can 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#sqla...

[marcosdumay]

> 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]

> 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

[mike_hock]

So, recent addition.

[aidos]

I would argue "certainly not", since it's had them for more than half its lifetime.

[aidos]

I 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]

Yep, 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.

[aidos]

Heyya! 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]

Hiya 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#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]

Happy to ping you guys over the code if you want to look. It’s probably fairly tweak-able to handle your specific cases.