CODEWITHSUNDEEP
databaseenumstypesrdbms
Tomalak
Tomalak

Reputation: 338188

Which database systems support an ENUM data type, which don't?

Following up this question: "Database enums - pros and cons", I'd like to know which database systems support enumeration data types, and a bit of detail on how they do it (e.g. what is stored internally, what are the limits, query syntax implications, indexing implications, ...).

Discussion of use cases or the pros and cons should take place in the other questions.

Upvotes: 15

Views: 7673

Answers (6)

mhu
mhu

Reputation: 18041

MSSQL doesn't support ENUM.

When you use Entity Framework 5, you can use enums (look at: Enumeration Support in Entity Framework and EF5 Enum Types Walkthrough), but even then the values are stored as int in the database.

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 753695

AFAIK, neither IBM DB2 nor IBM Informix Dynamic Server support ENUM types.

Upvotes: 4

mat
mat

Reputation: 13353

PostgreSQL supports ENUM from 8.3 onwards. For older versions, you can use :

You can simulate an ENUM by doing something like this :

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

You could also have :

CREATE TABLE colours (
  colour_id int not null primary key,
  colour varchar(255) not null
)
CREATE TABLE persons (
  person_id int not null primary key,
  favourite_colour_id integer NOT NULL references colours(colour_id),
);

which would have you add a join when you get to know the favorite colour, but has the advantage that you can add colours simply by adding an entry to the colour table, and not that you would not need to change the schema each time. You also could add attribute to the colour, like the HTML code, or the RVB values.

You also could create your own type which does an enum, but I don't think it would be any more faster than the varchar and the CHECK.

Upvotes: 7

bortzmeyer
bortzmeyer

Reputation: 35459

Unlike what mat said, PostgreSQL does support ENUM (since version 8.3, the last one):

essais=> CREATE TYPE rcount AS ENUM (
essais(>   'one',
essais(>   'two',
essais(>   'three'
essais(> );
CREATE TYPE
essais=> 
essais=> CREATE TABLE dummy (id SERIAL, num rcount);
NOTICE:  CREATE TABLE will create implicit sequence "dummy_id_seq" for serial column "dummy.id"
CREATE TABLE
essais=> INSERT INTO dummy (num) VALUES ('one');
INSERT 0 1
essais=> INSERT INTO dummy (num) VALUES ('three');
INSERT 0 1
essais=> INSERT INTO dummy (num) VALUES ('four');
ERROR:  invalid input value for enum rcount: "four"
essais=> 
essais=> SELECT * FROM dummy WHERE num='three';
 id |  num  
----+-------
  2 | three
  4 | three

There are functions which work specifically on enums.

Indexing works fine on enum types.

According to the manual, implementation is as follows:

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'. Spaces in the labels are significant, too.

Upvotes: 3

Tomalak
Tomalak

Reputation: 338188

I know that MySQL does support ENUM:

  • the data type is implemented as integer value with associated strings
  • you can have a maximum of 65.535 elements for a single enumeration
  • each string has a numerical equivalent, counting from 1, in the order of definition
  • the numerical value of the field is accessible via "SELECT enum_col+0"
  • in non-strict SQL mode, assigning not-in-list values does not necessarily result in an error, but rather a special error value is assigned instead, having the numerical value 0
  • sorting occurs in numerical order (e.g. order of definition), not in alphabetical order of the string equivalents
  • assignment either works via the value string or the index number
  • this: ENUM('0','1','2') should be avoided, because '0' would have integer value 1

Upvotes: 8

Tony Andrews
Tony Andrews

Reputation: 132570

Oracle doesn't support ENUM at all.

Upvotes: 4

Related Questions