4 CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');
6 -- Did it create the right number of rows?
8 SELECT COUNT(*) FROM pg_enum WHERE enumtypid = 'rainbow'::regtype;
17 SELECT 'red'::rainbow;
23 SELECT 'mauve'::rainbow;
24 ERROR: invalid input value for enum rainbow: "mauve"
25 LINE 1: SELECT 'mauve'::rainbow;
30 CREATE TYPE planets AS ENUM ( 'venus', 'earth', 'mars' );
31 SELECT enumlabel, enumsortorder
33 WHERE enumtypid = 'planets'::regtype
35 enumlabel | enumsortorder
36 -----------+---------------
42 ALTER TYPE planets ADD VALUE 'uranus';
43 SELECT enumlabel, enumsortorder
45 WHERE enumtypid = 'planets'::regtype
47 enumlabel | enumsortorder
48 -----------+---------------
55 ALTER TYPE planets ADD VALUE 'mercury' BEFORE 'venus';
56 ALTER TYPE planets ADD VALUE 'saturn' BEFORE 'uranus';
57 ALTER TYPE planets ADD VALUE 'jupiter' AFTER 'mars';
58 ALTER TYPE planets ADD VALUE 'neptune' AFTER 'uranus';
59 SELECT enumlabel, enumsortorder
61 WHERE enumtypid = 'planets'::regtype
63 enumlabel | enumsortorder
64 -----------+---------------
75 SELECT enumlabel, enumsortorder
77 WHERE enumtypid = 'planets'::regtype
78 ORDER BY enumlabel::planets;
79 enumlabel | enumsortorder
80 -----------+---------------
91 -- errors for adding labels
92 ALTER TYPE planets ADD VALUE
93 'plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto';
94 ERROR: invalid enum label "plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto"
95 DETAIL: Labels must be 63 characters or less.
96 ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
97 ERROR: "zeus" is not an existing enum label
99 -- Test inserting so many values that we have to renumber
101 create type insenum as enum ('L1', 'L2');
102 alter type insenum add value 'i1' before 'L2';
103 alter type insenum add value 'i2' before 'L2';
104 alter type insenum add value 'i3' before 'L2';
105 alter type insenum add value 'i4' before 'L2';
106 alter type insenum add value 'i5' before 'L2';
107 alter type insenum add value 'i6' before 'L2';
108 alter type insenum add value 'i7' before 'L2';
109 alter type insenum add value 'i8' before 'L2';
110 alter type insenum add value 'i9' before 'L2';
111 alter type insenum add value 'i10' before 'L2';
112 alter type insenum add value 'i11' before 'L2';
113 alter type insenum add value 'i12' before 'L2';
114 alter type insenum add value 'i13' before 'L2';
115 alter type insenum add value 'i14' before 'L2';
116 alter type insenum add value 'i15' before 'L2';
117 alter type insenum add value 'i16' before 'L2';
118 alter type insenum add value 'i17' before 'L2';
119 alter type insenum add value 'i18' before 'L2';
120 alter type insenum add value 'i19' before 'L2';
121 alter type insenum add value 'i20' before 'L2';
122 alter type insenum add value 'i21' before 'L2';
123 alter type insenum add value 'i22' before 'L2';
124 alter type insenum add value 'i23' before 'L2';
125 alter type insenum add value 'i24' before 'L2';
126 alter type insenum add value 'i25' before 'L2';
127 alter type insenum add value 'i26' before 'L2';
128 alter type insenum add value 'i27' before 'L2';
129 alter type insenum add value 'i28' before 'L2';
130 alter type insenum add value 'i29' before 'L2';
131 alter type insenum add value 'i30' before 'L2';
132 -- The exact values of enumsortorder will now depend on the local properties
133 -- of float4, but in any reasonable implementation we should get at least
134 -- 20 splits before having to renumber; so only hide values > 20.
136 case when enumsortorder > 20 then null else enumsortorder end as so
138 WHERE enumtypid = 'insenum'::regtype
139 ORDER BY enumsortorder;
177 -- Basic table creation, row selection
179 CREATE TABLE enumtest (col rainbow);
180 INSERT INTO enumtest values ('red'), ('orange'), ('yellow'), ('green');
181 COPY enumtest FROM stdin;
182 SELECT * FROM enumtest;
194 -- Operators, no index
196 SELECT * FROM enumtest WHERE col = 'orange';
202 SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col;
212 SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col;
220 SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col;
229 SELECT * FROM enumtest WHERE col < 'green' ORDER BY col;
237 SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col;
249 SELECT 'red'::rainbow::text || 'hithere';
255 SELECT 'red'::text::rainbow = 'red'::rainbow;
264 SELECT min(col) FROM enumtest;
270 SELECT max(col) FROM enumtest;
276 SELECT max(col) FROM enumtest WHERE col < 'green';
283 -- Index tests, force use of index
285 SET enable_seqscan = off;
286 SET enable_bitmapscan = off;
288 -- Btree index / opclass with the various operators
290 CREATE UNIQUE INDEX enumtest_btree ON enumtest USING btree (col);
291 SELECT * FROM enumtest WHERE col = 'orange';
297 SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col;
307 SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col;
315 SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col;
324 SELECT * FROM enumtest WHERE col < 'green' ORDER BY col;
332 SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col;
341 SELECT min(col) FROM enumtest;
347 SELECT max(col) FROM enumtest;
353 SELECT max(col) FROM enumtest WHERE col < 'green';
359 DROP INDEX enumtest_btree;
361 -- Hash index / opclass with the = operator
363 CREATE INDEX enumtest_hash ON enumtest USING hash (col);
364 SELECT * FROM enumtest WHERE col = 'orange';
370 DROP INDEX enumtest_hash;
374 RESET enable_seqscan;
375 RESET enable_bitmapscan;
377 -- Domains over enums
379 CREATE DOMAIN rgb AS rainbow CHECK (VALUE IN ('red', 'green', 'blue'));
386 SELECT 'purple'::rgb;
387 ERROR: value for domain rgb violates check constraint "rgb_check"
388 SELECT 'purple'::rainbow::rgb;
389 ERROR: value for domain rgb violates check constraint "rgb_check"
394 SELECT '{red,green,blue}'::rainbow[];
400 SELECT ('{red,green,blue}'::rainbow[])[2];
406 SELECT 'red' = ANY ('{red,green,blue}'::rainbow[]);
412 SELECT 'yellow' = ANY ('{red,green,blue}'::rainbow[]);
418 SELECT 'red' = ALL ('{red,green,blue}'::rainbow[]);
424 SELECT 'red' = ALL ('{red,red}'::rainbow[]);
433 SELECT enum_first(NULL::rainbow);
439 SELECT enum_last('green'::rainbow);
445 SELECT enum_range(NULL::rainbow);
447 ---------------------------------------
448 {red,orange,yellow,green,blue,purple}
451 SELECT enum_range('orange'::rainbow, 'green'::rainbow);
453 -----------------------
454 {orange,yellow,green}
457 SELECT enum_range(NULL, 'green'::rainbow);
459 ---------------------------
460 {red,orange,yellow,green}
463 SELECT enum_range('orange'::rainbow, NULL);
465 -----------------------------------
466 {orange,yellow,green,blue,purple}
469 SELECT enum_range(NULL::rainbow, NULL);
471 ---------------------------------------
472 {red,orange,yellow,green,blue,purple}
476 -- User functions, can't test perl/python etc here since may not be compiled.
478 CREATE FUNCTION echo_me(anyenum) RETURNS text AS $$
480 RETURN $1::text || 'omg';
483 SELECT echo_me('red'::rainbow);
490 -- Concrete function should override generic one
492 CREATE FUNCTION echo_me(rainbow) RETURNS text AS $$
494 RETURN $1::text || 'wtf';
497 SELECT echo_me('red'::rainbow);
504 -- If we drop the original generic one, we don't have to qualify the type
505 -- anymore, since there's only one match
507 DROP FUNCTION echo_me(anyenum);
508 SELECT echo_me('red');
514 DROP FUNCTION echo_me(rainbow);
516 -- RI triggers on enum types
518 CREATE TABLE enumtest_parent (id rainbow PRIMARY KEY);
519 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "enumtest_parent_pkey" for table "enumtest_parent"
520 CREATE TABLE enumtest_child (parent rainbow REFERENCES enumtest_parent);
521 INSERT INTO enumtest_parent VALUES ('red');
522 INSERT INTO enumtest_child VALUES ('red');
523 INSERT INTO enumtest_child VALUES ('blue'); -- fail
524 ERROR: insert or update on table "enumtest_child" violates foreign key constraint "enumtest_child_parent_fkey"
525 DETAIL: Key (parent)=(blue) is not present in table "enumtest_parent".
526 DELETE FROM enumtest_parent; -- fail
527 ERROR: update or delete on table "enumtest_parent" violates foreign key constraint "enumtest_child_parent_fkey" on table "enumtest_child"
528 DETAIL: Key (id)=(red) is still referenced from table "enumtest_child".
530 -- cross-type RI should fail
532 CREATE TYPE bogus AS ENUM('good', 'bad', 'ugly');
533 CREATE TABLE enumtest_bogus_child(parent bogus REFERENCES enumtest_parent);
534 ERROR: foreign key constraint "enumtest_bogus_child_parent_fkey" cannot be implemented
535 DETAIL: Key columns "parent" and "id" are of incompatible types: bogus and rainbow.
540 DROP TABLE enumtest_child;
541 DROP TABLE enumtest_parent;
545 -- Verify properly cleaned up
547 SELECT COUNT(*) FROM pg_type WHERE typname = 'rainbow';
553 SELECT * FROM pg_enum WHERE NOT EXISTS
554 (SELECT 1 FROM pg_type WHERE pg_type.oid = enumtypid);
555 enumtypid | enumsortorder | enumlabel
556 -----------+---------------+-----------