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

71–76 of 76 posts

Re: Representing Enums in PostgreSQL

#71

Post by ceving »

jpalomaki wrote: post #31

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.

The "less space" argument does not seem to be true in every situation. Enums may suffer from alignments. See here: https://dba.stackexchange.com/a/258591/50410

Re: Representing Enums in PostgreSQL

#72

Post by aidos »

marcosdumay wrote: post #38
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…

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

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)

Re: Representing Enums in PostgreSQL

#73

Post by somat »

I am probably missing something obvious(space/time optimization for very large/busy tables?) but isn't this a perfect example of why foreign keys exist. In fact both case studies presented by the article(enums and fixed string check constraints) are clunky enough that I would say both are wrong and they should just go with a foreign key.

Re: Representing Enums in PostgreSQL

#74

Post by marcosdumay »

aidos wrote: post #72
marcosdumay wrote: post #38

Earlier quoted context omitted.

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

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)

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.

Re: Representing Enums in PostgreSQL

#75

Post by Too »

jpalomaki wrote: post #31

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.

Isn’t index size more proportional to number of distinct values and number of rows. With few distinct enum options, the size of each shouldn’t have big impact?
Post reply on HN