[hn_throwaway_99]

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.

[thom]

You can create an implicit cast to and from text with CREATE CAST and it becomes transparent.

[hn_throwaway_99]

Can you explain a little more what you mean here? I'm not sure this helps my use case. E.g. if I run a BQ external query like the following

    SELECT * FROM EXTERNAL_QUERY("my-pg-connection-string", """
        SELECT enum_column FROM foo;
    """);

that always fails for me, even if I define "CREATE CAST (my_enum_type AS text) WITH INOUT AS IMPLICIT;"

The only way I can get it to work is if I use "SELECT enum_column::text FROM foo;" and I don't need a custom cast definition to do that.

[thom]

Apologies, I’m hallucinating features here. In our codebase we also have lots of JDBC extensions to map PG objects to language types and back, and this is the easiest way to handle queries over enums. But obviously some type info has to trigger the coercion, so SELECT * FROM might work in the context of an INSERT INTO but elsewhere you’d have to be explicit.

[e12e]

I think you would need a way to trigger the cast either way? Eg concatenate with a string?

Otherwise, maybe a function and a generated column (Ed: in a VIEW i guess, if it chokes on the raw enum)?

https://dba.stackexchange.com/questions/276477/immutably-con...

[aidos]

By the time you've done a generated column you might as well go with the articles solution of using text with a check constraint since you're now using the space anyway.

[cryptonector]

The CREATE CAST will help on DMLs, but not SELECTs.

[aidos]

Oh nice. I didn’t know about that feature.

[faangsticle]

> BQ doesn't support enums

Sounds like a reason to use views to work around the limitations of BigQuery

[cryptonector]

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

Yeah, one has to write more adapter queries to add those casts to text (or from text, for DMLs), but it's OK, and ENUMs are just really nice. And as u/faangsticle says, use VIEWs for that.