Postgresql enum what are the advantages and disadvantages?

Question

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?

Answer 1

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.

Answer 2

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