Postgresql enum what are the advantages and disadvantages?
database, postgresql
Solution
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
Problem
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. I was wondering what exactly are the advantages of postgres enums (besides readablity) and what are the disadvatages?