Where I work we use a postgres database (8.3 soon to migrate to 8.4). There is a small debate currently on the use of enums in the database. Personally I do not like the db enum type. Among other things it puts application logic in the database and creates a posibility for a mismatch between code and data.
在我工作的地方，我们使用postgres数据库（8.3很快就会迁移到8.4）。目前关于在数据库中使用枚举的争论很少。我个人不喜欢db enum类型。除此之外，它还将应用程序逻辑放在数据库中，并为代码和数据之间的不匹配创建了可能性。
I was wondering what exactly are the advantages of postgres enums (besides readablity) and what are the disadvatages?
我想知道postgres枚举的优点究竟是什么（除了可读性）以及有什么不妥之处？
4 个解决方案
#1
24
The advantages of enums are:
枚举的优点是：
- Performance is better. You can just display what you get out of the core table instead of either having a separate lookup table that translates a code to a value or having app logic that translates a code to a value. This can be especially useful in datawarehouse applications.
- 表现更好。您可以只显示从核心表中获取的内容，而不是使用单独的查找表将代码转换为值或使用应用程序逻辑将代码转换为值。这在数据仓库应用程序中尤其有用。
- Ad hoc SQL is easier to write
- Ad hoc SQL更容易编写
The disadvantages are:
缺点是：
- Encoding display values into your database ddl is bad form. If you translate the enum value into a different display value in your app code, then you lost a lot of the advantages of using enums.
- 将显示值编码到数据库ddl中的形式不好。如果您将应用代码中的枚举值转换为不同的显示值，那么您将失去使用枚举的许多优点。
- Adding values requires DDL changes
- 添加值需要DDL更改
- Makes language localization difficult
- 使语言本地化变得困难
- Database portability is decreased
- 数据库可移植性降低
#2
2
Enums combine the advantages of ints with the advantages of strings: they are small and fast like ints, readable like strings, and have the additional advantage of being safe (you can't mis-spell an enum).
Enums结合了int的优点和字符串的优点：它们像int一样小而快，像字符串一样可读，并且具有安全的额外优点（你不能错误地拼写枚举）。
However, if you don't care about readability, an int is as good as an enum.
但是，如果您不关心可读性，则int与枚举一样好。
#3
0
As advantage you have also DB checking, that nothing else enum value couldn't be recorded in column. The big disadvantage for me was, that enum could be modified only by adding value to the end, but since Postgres 9.1 it is in the past: https://*.com/a/7834949/548473
作为优势，您还可以进行数据库检查，无法在列中记录任何其他枚举值。对我来说最大的缺点是，枚举只能通过增加值来修改，但是自Postgres 9.1以来它就是过去：https：//*.com/a/7834949/548473
#4
-1
Point is, if applications are allowed to do DDL, they are more likely to cause blocking or conflict. DDL is best done offline i.e. in single-user mode.
重点是，如果允许应用程序执行DDL，则它们更有可能导致阻塞或冲突。 DDL最好离线完成，即在单用户模式下完成。
#1
24
The advantages of enums are:
枚举的优点是：
- Performance is better. You can just display what you get out of the core table instead of either having a separate lookup table that translates a code to a value or having app logic that translates a code to a value. This can be especially useful in datawarehouse applications.
- 表现更好。您可以只显示从核心表中获取的内容，而不是使用单独的查找表将代码转换为值或使用应用程序逻辑将代码转换为值。这在数据仓库应用程序中尤其有用。
- Ad hoc SQL is easier to write
- Ad hoc SQL更容易编写
The disadvantages are:
缺点是：
- Encoding display values into your database ddl is bad form. If you translate the enum value into a different display value in your app code, then you lost a lot of the advantages of using enums.
- 将显示值编码到数据库ddl中的形式不好。如果您将应用代码中的枚举值转换为不同的显示值，那么您将失去使用枚举的许多优点。
- Adding values requires DDL changes
- 添加值需要DDL更改
- Makes language localization difficult
- 使语言本地化变得困难
- Database portability is decreased
- 数据库可移植性降低
#2
2
Enums combine the advantages of ints with the advantages of strings: they are small and fast like ints, readable like strings, and have the additional advantage of being safe (you can't mis-spell an enum).
Enums结合了int的优点和字符串的优点：它们像int一样小而快，像字符串一样可读，并且具有安全的额外优点（你不能错误地拼写枚举）。
However, if you don't care about readability, an int is as good as an enum.
但是，如果您不关心可读性，则int与枚举一样好。
#3
0
As advantage you have also DB checking, that nothing else enum value couldn't be recorded in column. The big disadvantage for me was, that enum could be modified only by adding value to the end, but since Postgres 9.1 it is in the past: https://*.com/a/7834949/548473
作为优势，您还可以进行数据库检查，无法在列中记录任何其他枚举值。对我来说最大的缺点是，枚举只能通过增加值来修改，但是自Postgres 9.1以来它就是过去：https：//*.com/a/7834949/548473
#4
-1
Point is, if applications are allowed to do DDL, they are more likely to cause blocking or conflict. DDL is best done offline i.e. in single-user mode.
重点是，如果允许应用程序执行DDL，则它们更有可能导致阻塞或冲突。 DDL最好离线完成，即在单用户模式下完成。