postgresql - Postgres ENUM data type or CHECK CONSTRAINT? -

i have been migrating mysql db pg (9.1), , have been emulating mysql enum data types creating new data type in pg, , using column definition. question -- i, , better to, use check constraint instead? mysql enum types implemented enforce specific values entries in rows. done check constraint? and, if yes, better (or worse)?

based on comments , answers here, , rudimentary research, have following summary offer comments postgres-erati. appreciate input.

there 3 ways restrict entries in postgres database table column. consider table store "colors" want 'red', 'green', or 'blue' valid entries.

1. enumerated data type

   create type valid_colors enum ('red', 'green', 'blue');  create table t (     color valid_colors ); 

   advantages type can defined once , reused in many tables needed. standard query can list values enum type, , can used make application form widgets.

   select  n.nspname enum_schema,           t.typname enum_name,           e.enumlabel enum_value    pg_type t join          pg_enum e on t.oid = e.enumtypid join          pg_catalog.pg_namespace n on n.oid = t.typnamespace   t.typname = 'valid_colors'   enum_schema | enum_name     | enum_value  -------------+---------------+------------  public      | valid_colors  | red  public      | valid_colors  | green  public      | valid_colors  | blue 

   disadvantages are, enum type stored in system catalogs, query above required view definition. these values not apparent when viewing table definition. and, since enum type data type separate built in numeric , text data types, regular numeric , string operators , functions don't work on it. so, 1 can't query like

   select t color 'bl%';  

2. check constraints

   create table t (     colors text check (colors in ('red', 'green', 'blue')) ); 

   two advantage that, one, "what see get," is, valid values column recorded right in table definition, , two, native string or numeric operators work.

3. foreign keys

   create table valid_colors (     id serial primary key not null,     color text );  insert valid_colors (color) values      ('red'),     ('green'),     ('blue');  create table t (     color_id integer references valid_colors (id) ); 

   essentially same creating enum type, except, native numeric or string operators work, , 1 doesn't have query system catalogs discover valid values. join required link color_id desired text value.