Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Representing Enums in PostgreSQL

making.close.com

Post reply on HN
Print view View original

31–40 of 76 posts

Re: Representing Enums in PostgreSQL

#31

Post by jpalomaki »

Native enums can be used in queries like strings, but with type checking:

  select * from cust where type = ’company’ -- ok
  select * from cust where type = ’c0mpany’ -- error
As mentioned, they take less space. Important if you use these columns in index and have millions of rows.

Re: Representing Enums in PostgreSQL

#32

Post by netcraft »

rpcope1 wrote: post #5

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, y…

In my experience, you need those descriptions and other metadata on these enum values more often than not.

Re: Representing Enums in PostgreSQL

#33

Post by wojcikstefan »

aidos wrote: post #29
wojcikstefan wrote: post #15

Earlier quoted context omitted.

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 `nat…

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.

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.

Re: Representing Enums in PostgreSQL

#34

Post by SigmundA »

rpcope1 wrote: post #5

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, y…

Yep thats what I typically do except a string "code" and string "description" that way the raw data is more readable without joining the coded value and many times you want a short code vs long description, like with US states and their abbreviations. Most codes are 1 or 2 characters up to maybe 4 so you end up with less or the same space used than a 32 bit int with UTF-8.

Metadata like sorting and even what date ranges the code is valid for and even security as in who's allowed to use the code in the app.

Re: Representing Enums in PostgreSQL

#35

Post by hn_throwaway_99 »

cryptonector wrote: post #26

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: 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_en…

Glad to see this as the top comment, completely agree. After reading the article, was thinking that the only real downside of using enum types (but there is one more issue not mentioned, more on that below) is when you need to remove values. In reality, I've found removing enum values to be a very rare occurrence in prod. Removing an enum value fundamentally breaks backwards compatibility, so usually a better option is to essentially deprecate it, which is more like what you recommend.

Note one other thing I have found is that postgres enums are less "portable" than plain text columns. For example, if you're using BigQuery to query postgres tables as an external connection, BQ doesn't support enums, so your external connection query has to cast any enum return values (e.g. some_enum_column::text) before returning them to BQ, which can be a pain if you want to do "SELECT * FROM ..." some table that returns enum columns.

Re: Representing Enums in PostgreSQL

#36

Post by dikei »

cryptonector wrote: post #26

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: 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_en…

Agree, PostgreSQL enum is really just for mapping with an enum in application code. And just like you should not remove an enum or change their ordinal in application code, don't do that to a PostgreSQL enum

Re: Representing Enums in PostgreSQL

#37

Post by cropcirclbureau »

cryptonector wrote: post #26

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: 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_en…

Good approach. And with Postgres `DOMAIN` types features to DRY up the `CHECK` the live constraint, this should be very palatable. I usually opt for enum tables and regularly find myself needing to declare `archived_at` columns for said tables. Did not know of the `RENAME` command either and what you describe renders native enums less warty.

Re: Representing Enums in PostgreSQL

#38

Post by marcosdumay »

wojcikstefan wrote: post #15
marcosdumay wrote: post #8

Earlier quoted context omitted.

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…

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 `nat…

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

Re: Representing Enums in PostgreSQL

#39

Post by Icathian »

ComputerGuru wrote: post #25
Icathian wrote: post #2

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!

It sure sounds like a bug to me, too! Is there an upstream bug id? I’d imagine dump/restore should “just work” with anything that can be done in postgres oob.

Donno about a bug Id, but there were a couple mailing list discussions. Here: https://www.postgresql.org/message-id/flat/1376149.167526827...

Disclosure: I reported this particular bug. It was a fun one to run down.

Post reply on HN