Postgresql enum what are the advantages and disadvantages?

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

However, if you don't care about readability, an int is as good as an enum.


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

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.
  • Adding values requires DDL changes
  • Makes language localization difficult
  • Database portability is decreased