Text Size: Normal / Large

Home → Documentation → Manuals → PostgreSQL 8.4

PostgreSQL 8.4.3 Documentation
Prev	Fast Backward	Chapter 8. Data Types	Fast Forward	Next

8.7. Enumerated Types

Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data.

8.7.1. Declaration of Enumerated Types

Enum types are created using the CREATE TYPE command, for example:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Once created, the enum type can be used in table and function definitions much like any other type:

Example 8-3. Basic Enum Usage

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

8.7.2. Ordering

The ordering of the values in an enum type is the order in which the values were listed when the type was created. All standard comparison operators and related aggregate functions are supported for enums. For example:

Example 8-4. Enum Ordering

INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');
SELECT * FROM person WHERE current_mood > 'sad';
 name  | current_mood 
-------+--------------
 Moe   | happy
 Curly | ok
(2 rows)

SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
 name  | current_mood 
-------+--------------
 Curly | ok
 Moe   | happy
(2 rows)

SELECT name 
FROM person
WHERE current_mood = (SELECT MIN(current_mood) FROM person);
 name  
-------
 Larry
(1 row)

8.7.3. Type Safety

Each enumerated data type is separate and cannot be compared with other enumerated types.

Example 8-5. Lack of Casting

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (
    num_weeks integer,
    happiness happiness
);
INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
ERROR:  invalid input value for enum happiness: "sad"
SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood = holidays.happiness;
ERROR:  operator does not exist: mood = happiness

If you really need to do something like that, you can either write a custom operator or add explicit casts to your query:

Example 8-6. Comparing Different Enums by Casting to Text

SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood::text = holidays.happiness::text;
 name | num_weeks 
------+-----------
 Moe  |         4
(1 row)

8.7.4. Implementation Details

An enum value occupies four bytes on disk. The length of an enum value's textual label is limited by the NAMEDATALEN setting compiled into PostgreSQL; in standard builds this means at most 63 bytes.

Enum labels are case sensitive, so 'happy' is not the same as 'HAPPY'. White space in the labels is significant too.

The translations from internal enum values to textual labels are kept in the system catalog pg_enum. Querying this catalog directly can be useful.

Prev	Home	Next
Boolean Type	Up	Geometric Types

User Comments

KARASZI István

21 Oct 2009 13:13:56

Here is an example for a custom oeprator:

--DROP SCHEMA "test" CASCADE;

CREATE SCHEMA "test";

CREATE TYPE test.myenum AS ENUM (
    'FOO',
    'BAR'
);

CREATE TABLE test.test (
    id    serial NOT NULL UNIQUE,
    data  test.myenum NOT NULL
);

INSERT INTO test.test (data) VALUES ('FOO'::test.myenum);

CREATE OR REPLACE FUNCTION test.myenum_equal( test.myenum, varchar ) RETURNS boolean AS $$
    SELECT $1 = CAST($2 AS test.myenum);
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

CREATE OPERATOR test.= (
    PROCEDURE = test.myenum_equal,
    LEFTARG = test.myenum, RIGHTARG = varchar
);

SELECT * FROM test.test WHERE data='FOO';
SELECT * FROM test.test WHERE data='BAR';

Add Comment

Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. Please note, this is not a support forum, and your IP address will be logged. If you have a question or need help, please see the faq, try a mailing list, or join us on IRC. Note that submissions containing URLs or other keywords commonly found in 'spam' comments may be silently discarded. Please contact the webmaster if you think this is happening to you in error.

In order to submit a comment, you must have a community account.

* denotes required field