PostgreSQL & PHP Tutorials - PostgreSQL Enum Types

PostgreSQL »  PostgreSQL Enum Types
PostgreSQL »  Hints and Tips »  PostgreSQL Enum Types

Posted By Chris Smith Posted on 26 Mar 2006, 11:22 PM
An enum datatype allows only certain values to be entered into a particular field (for example - 'red', 'blue', 'yellow', 'purple' for favourite colours).

Postgresql doesn't have an enum datatype, but we can emulate it quickly and easily.

Instead of an enum type we can set up a CHECK CONSTRAINT - this tells postgresql to make sure that the value we are entering is valid.

CREATE TABLE person (
personid int not null primary key,
favourite_colour varchar(255) NOT NULL,
CHECK (favourite_colour IN ('red', 'blue', 'yellow', 'purple'))
);

Now that's done, let's check it works:

test=# insert into person(personid, favourite_colour) values (1, 'red');
INSERT 0 1

Now for something not in the list:

test=# insert into person(personid, favourite_colour) values (2, 'green');
ERROR: new row for relation "person" violates check constraint "person_favourite_colour_check"

Done! Nice and easy!


Avg Rating: 4
Vote Count: 19


              
  New Reply

This will however not emulate the way data is stored in MySQL. A less important aspect if this is that you can also use the underlying numeric values that enum values are associated with. Slightly more important is the reduced storage requirements. What I find neat however is that the order of the enum items in MySQL also defines the sort order. Lukas 12 Apr 2006 Reply
While this works, I don't think it is the best design. First of all, you can move the check constraint to a the domain level. However, by far the best approach is to create a color table:

create table color
(
color text primary key
);

insert into color values ('red'); etc

in the referencing table you just do:

create table person
(
personid int not null primary key,
color text references color
);

now you get the benefits of constraint checking but also:
1. can control color list by inserting/deleting records
2. can allow RI to cascade chagnes if you want to change color text
3. have a color table you can query for UI pulldown boxes, etc

merlin

 		Merlin Moncure 13 Apr 2006 Reply
this is the approach taken by my mysql to postgres perl convertor. (sorry for being slow to maintain this much requested file).
if (/(\w*)\s+enum\(((?:['"]\w+['"]\s*,)+['"]\w+['"])\)(.*)$/i) { # enum handling
$enum_column=$1;
$enum_datafield{$enum_column} = $2; # 'abc','def', ...
$_ = qq~ $1 varchar CONSTRAINT ${table}_${enum_column}_constraint REFERENCES ${table}_${enum_column}_constraint_table (${enum_column}) $3\n~; 		joesph 06 May 2006 Reply

Want to post a comment? Fill in the details below.
Your Name  : 
Your Email  : 
Your Website  : 
Spam Check! Please answer this question  : 5 + 5 =
Comment