native enums

[calvinmetcalf]

postgres has enums, we shouldn't have to manually create one with constraints

[tgriesser]

This is the source I consulted with the enum stuff, the general consensus being that check constraint was preferable.

http://stackoverflow.com/questions/10923213/postgres-enum-data-type-or-check-constraint

Would be interested to hear pros/cons though

[bendrucker]

Any thoughts @calvinmetcalf? This comment is the most useful: http://stackoverflow.com/a/10984951/906162

Just happened to be adding an enum to my schema (via knex) and remembered that this came up.

[gchauvet]

We use enums in an existing postgresql database without "major" issues (except in joins tables, because value is interpreted as a column). Advantages of enum against simple string check are :

• Space occupation : only 4 bytes for enum;
• Simplified SQL rules : enums are automatically checked.

[bendrucker]

If there are good reasons to use native it seems like the ideal solution here is to add a flag so people who know what they're doing can use them. The existing behavior is the least likely to create confusion but you should certainly be able to choose to opt in.

[kentnek]

Hi, so any update on this? I'd prefer the native enums over constraints.

[carragom]

Reading the comments on http://stackoverflow.com/questions/10923213/postgres-enum-data-type-or-check-constraint/10984951#10984951 looks like the general consenus actually is that native enums have all the benefits and no drawbacks.

1. They can be easily listed with a view, see here.
2. They can be casted for extended filtering, see here.

+1 on native PostgreSQL enums.

[elhigu]

Pretty much only drawback I can think of with enum vs. check constraint is that when you take the enum value out of DB it is jus a number and needs some mapping to have readable values on javascript side or convert enum to string before returning it from DB. Benefit is that enum takes less space.

Looks like there is not that much interest for this, since during the 3 years that this issue has been open there hasn't been that many comments / upvotes, but still if someone likes to implement option to use native enums instead of check constraints one I would consider taking that pull request in.

[jeffwillden]

I vote for supporting enum types. I landed here because I was trying to figure out how to create one. Disappointed to see that check constraint hacks are the answer. I agree with their benefits listed above. The "drawback" that elhigu mentioned, that the value comes out of the DB as a number, is actually a benefit if the records are sent to a client-side app. The number is obviously more compact in JSON. That can add up to big savings when sending large numbers of records that have different enumerated statuses, for example.

[elhigu]

@jeffwillden If you read my last comment carefully you will notice that I actually mentioned also the benefit of enums consuming less space :)

I agree that there are times when using enums is better choice, questing is not about that. It is just a choice which way of the functionality is more commonly useful. In knex the choice was using check constraints.

Also in my personal work experience I've never came across in situation where I was wishing that my enums would be smaller in JSON (I completely understand that it is possible case and looks like you came across with one situation like that). On the other hand I've been cursing various times when I had to be working with DB that had enums stored as numbers which made interpreting raw DB data much harder.

Anyways looks like this discussion is starting to go in circles... Default functionality is not going to change, but if someone likes to implement an option { nativeEnum: true } for enum column builder I wont object.

[geekabhi]

will it be implemented for mssql as well?

[elhigu]

@geekabhi should be implemented for every supported platform. However I haven't heard anyone wanting this feature so bad that they would have started implementing it.

[heisian]

ouch.. will definitely be using a combination of knex.raw and knex.specificType here instead of the default enum method...

[heisian]

awesome!