Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have been migrating a MySQL db to Pg (9.1), and have been emulating MySQL ENUM data types by creating a new data type in Pg, and then using that as the column definition. My question -- could I, and would it be better to, use a CHECK CONSTRAINT instead? The MySQL ENUM types are implemented to enforce specific values entries in the rows. Could that be done with a CHECK CONSTRAINT? and, if yes, would it be better (or worse)?

share|improve this question
    
Why don't you use PG's enum type? Why do you think you need to "emulate" one? –  a_horse_with_no_name Jun 8 '12 at 7:31

3 Answers 3

Based on the comments and answers here, and some rudimentary research, I have the following summary to offer for comments from the Postgres-erati. Will really appreciate your input.

There are three ways to restrict entries in a Postgres database table column. Consider a table to store "colors" where you want only 'red', 'green', or 'blue' to be valid entries.

  1. Enumerated data type

    CREATE TYPE valid_colors AS ENUM ('red', 'green', 'blue');
    
    CREATE TABLE t (
        color VALID_COLORS
    );
    

    Advantages are that the type can be defined once and then reused in as many tables as needed. A standard query can list all the values for an ENUM type, and can be used to make application form widgets.

    SELECT  n.nspname AS enum_schema,  
            t.typname AS enum_name,  
            e.enumlabel AS enum_value
    FROM    pg_type t JOIN 
            pg_enum e ON t.oid = e.enumtypid JOIN 
            pg_catalog.pg_namespace n ON n.oid = t.typnamespace
    WHERE   t.typname = 'valid_colors'
    
     enum_schema | enum_name     | enum_value 
    -------------+---------------+------------
     public      | valid_colors  | red
     public      | valid_colors  | green
     public      | valid_colors  | blue
    

    Disadvantages are, the ENUM type is stored in system catalogs, so a query as above is required to view its definition. These values are not apparent when viewing the table definition. And, since an ENUM type is actually a data type separate from the built in NUMERIC and TEXT data types, the regular numeric and string operators and functions don't work on it. So, one can't do a query like

    SELECT FROM t WHERE color LIKE 'bl%'; 
    
  2. Check constraints

    CREATE TABLE t (
        colors TEXT CHECK (colors IN ('red', 'green', 'blue'))
    );
    

    Two advantage are that, one, "what you see is what you get," that is, the valid values for the column are recorded right in the table definition, and two, all native string or numeric operators work.

  3. Foreign keys

    CREATE TABLE valid_colors (
        id SERIAL PRIMARY KEY NOT NULL,
        color TEXT
    );
    
    INSERT INTO valid_colors (colors) VALUES 
        ('red'),
        ('green'),
        ('blue');
    
    CREATE TABLE t (
        color_id INTEGER REFERENCES valid_colors (id)
    );
    

    Essentially the same as creating an ENUM type, except, the native numeric or string operators work, and one doesn't have to query system catalogs to discover the valid values.

share|improve this answer
    
I'd go for 2) or 3) most probably 3) –  a_horse_with_no_name Jun 11 '12 at 19:33
3  
Also, with another table and a foreign key, you can delegate the management of colors to an ordinary user without letting them alter tables. –  Mike Sherrill 'Cat Recall' Jun 11 '12 at 22:37
    
From an abstract perspective I see no diff in 1) and 3) .. The idea is the same, diff being that PG created the REF table. Either way I can easily select/view/alter the valid options from that REF table. Don't forget that it would be slick to dynamically create a list of valid options in the UI based on the optional values in the DB. Not so simple with 2). Very simple with 1).. or 3) using something like: CREATE VIEW vw_enums AS SELECT t.typname, e.enumlabel, e.enumsortorder FROM pg_enum e JOIN pg_type t ON e.enumtypid = t.oid; For portability this view easily dumps for use as a REF table. –  Michael.M Apr 7 '13 at 3:31

PostgreSQL has enum types, works as it should. I don't know if an enum is "better" than a constraint, they just both work.

share|improve this answer
2  
Personally I prefer using a check constraint (or even a lookup table with a FK constraint) for these kind of things. It makes changing them later easier. –  a_horse_with_no_name Jun 8 '12 at 7:31

I'm hoping somebody will chime in with a good answer from the PostgreSQL database side as to why one might be preferable to the other.

From a software developer point of view, I have a slight preference for using check constraints, since PostgreSQL enum's require a cast in your SQL to do an update/insert, such as:

INSERT INTO table1 (colA, colB) VALUES('foo', 'bar'::myenum)

where "myenum" is the enum type you specified in PostgreSQL.

This certainly makes the SQL non-portable (which may not be a big deal for most people), but also is just yet another thing you have to deal with while developing applications, so I prefer having VARCHARs (or other typical primitives) with check constraints.

As a side note, I've noticed that MySQL enums do not require this type of cast, so this is something particular to PostgreSQL in my experience.

share|improve this answer
1  
an additional issue with ENUM types is that string operators don't work. For example, if I have an ENUM field with text options, I can't use LIKE on it. On the other hand, with CHECK CONSTRAINTS text would still be treated as text. –  punkish Jun 10 '12 at 18:40
    
For PostgreSQL again - agreed. I've seen this as well and should have thought of it for my answer. I did just go and test it in MySQL as well and there again MySQL doesn't impose that restriction - you can use LIKE with an enum. So my vote with PostgreSQL is to avoid enums unless there is some other good reason to use them that I'm not aware of. –  quux00 Jun 11 '12 at 1:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.