Simulating ENUM in PostgreSQL using CHECK expression

PostgreSQL is a very powerful database. One of the things that seems missing when moving from MySQL is the ability to simply create an enumeration. ENUM is nice when you have a programmatically-semantic set of values for a field.

In PostgreSQL, you have several choices. But one simple one is to create a Check expression, like follows. Skip the IS NULL part if you don’t want the field nullable.

ALTER TABLE 
   "public"."CallCenter_Transfer"
ADD CONSTRAINT 
   "CallCenter_Transfer_TransferStatus"
   CHECK (
      "TransferStatus" IS NULL 
       OR 
      "TransferStatus" = ANY(ARRAY['TransferComplete', 'TransferFailedNoAnswer', 'TransferFailedProspectLost'])
      )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s