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

61–70 of 76 posts

Re: Representing Enums in PostgreSQL

#61

Post by SPBS »

faangsticle wrote: post #50
SPBS wrote: post #28

Earlier quoted context omitted.

I prefer directly using strings as enums, and using the foreign key constraint only to validate enum values. CREATE TABLE my_enum ( name TEXT PRIMARY KEY ); CREATE TABLE foo ( my_enum TEXT REFERENCES my_enum (name) ); The reason is because a SELECT * FROM foo showing cryptic enum ordinals is a headache, and having to join the enum table every time is potentially slower than just reading from the column directly. An A…

The point of enums is so the names can be descriptive, which typically will be longer than 4 characters.

Perhaps I was unclear, my underlying point is that short strings are cheap. And enum names are almost always short strings. 4 characters not enough? What about 8? That’s the size of a BIGINT, which everybody now uses for primary keys (they’re not slow). What about 16 characters? That’s the size of a UUID, which everyone now uses for primary keys (they’re not slow).

Even 4 characters (which perform just as fast as Postgres’ native enums) is enough if you develop a system of abbreviations. The airline industry gets by with just three characters!

Re: Representing Enums in PostgreSQL

#62

Post by Rapzid »

fabian2k wrote: post #10

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…

IDK I use the npgsql enum mapping to native pg enum and it works great. Pretty sure I have it configured for string values too.

Re: Representing Enums in PostgreSQL

#63

Post by hn_throwaway_99 »

thom wrote: post #53
hn_throwaway_99 wrote: post #35

Earlier quoted context omitted.

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…

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

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.

Re: Representing Enums in PostgreSQL

#64

Post by hn_throwaway_99 »

SPBS wrote: post #61
faangsticle wrote: post #50

Earlier quoted context omitted.

The point of enums is so the names can be descriptive, which typically will be longer than 4 characters.

Perhaps I was unclear, my underlying point is that short strings are cheap . And enum names are almost always short strings. 4 characters not enough? What about 8? That’s the size of a BIGINT, which everybody now uses for primary keys (they’re not slow). What about 16 characters? That’s the size of a UUID, which everyone now uses for primary keys (they’re not slow). Even 4 characters (which perform just as fast as Po…

Just wanted to let you know I really appreciate your comments. I hadn't thought about doing it the way you recommend (in my mind I was thinking "oh, storing enum values as text will be inefficient", but I never did the math as you did to realize storing anything up to 8 chars is no worse than bigint), but I think it's a pretty optimal solution. I also checked my DB where we heavily use enums, and basically every enum text value that is longer than 8 characters could easily be shortened/abbreviated to 8 chars and still be very easily understood. Thanks very much!

Re: Representing Enums in PostgreSQL

#65

Post by cryptonector »

hn_throwaway_99 wrote: post #63
thom wrote: post #53

Earlier quoted context omitted.

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

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;…

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

Re: Representing Enums in PostgreSQL

#66

Post by e12e »

hn_throwaway_99 wrote: post #63
thom wrote: post #53

Earlier quoted context omitted.

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

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;…

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

Re: Representing Enums in PostgreSQL

#68

Post by thom »

hn_throwaway_99 wrote: post #63
thom wrote: post #53

Earlier quoted context omitted.

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

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;…

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.

Re: Representing Enums in PostgreSQL

#69

Post by aidos »

e12e wrote: post #66
hn_throwaway_99 wrote: post #63

Earlier quoted context omitted.

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;…

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

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.

Re: Representing Enums in PostgreSQL

#70

Post by aidos »

mike_hock wrote: post #55
faangsticle wrote: post #51

Earlier quoted context omitted.

> 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

So, recent addition.

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