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"
26 -- Basic table creation, row selection
28 CREATE TABLE enumtest (col rainbow);
29 INSERT INTO enumtest values ('red'), ('orange'), ('yellow'), ('green');
30 COPY enumtest FROM stdin;
31 SELECT * FROM enumtest;
43 -- Operators, no index
45 SELECT * FROM enumtest WHERE col = 'orange';
51 SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col;
61 SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col;
69 SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col;
78 SELECT * FROM enumtest WHERE col < 'green' ORDER BY col;
86 SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col;
98 SELECT 'red'::rainbow::text || 'hithere';
104 SELECT 'red'::text::rainbow = 'red'::rainbow;
113 SELECT min(col) FROM enumtest;
119 SELECT max(col) FROM enumtest;
125 SELECT max(col) FROM enumtest WHERE col < 'green';
132 -- Index tests, force use of index
134 SET enable_seqscan = off;
135 SET enable_bitmapscan = off;
137 -- Btree index / opclass with the various operators
139 CREATE UNIQUE INDEX enumtest_btree ON enumtest USING btree (col);
140 SELECT * FROM enumtest WHERE col = 'orange';
146 SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col;
156 SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col;
164 SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col;
173 SELECT * FROM enumtest WHERE col < 'green' ORDER BY col;
181 SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col;
190 SELECT min(col) FROM enumtest;
196 SELECT max(col) FROM enumtest;
202 SELECT max(col) FROM enumtest WHERE col < 'green';
208 DROP INDEX enumtest_btree;
210 -- Hash index / opclass with the = operator
212 CREATE INDEX enumtest_hash ON enumtest USING hash (col);
213 SELECT * FROM enumtest WHERE col = 'orange';
219 DROP INDEX enumtest_hash;
223 RESET enable_seqscan;
224 RESET enable_bitmapscan;
226 -- Domains over enums
228 CREATE DOMAIN rgb AS rainbow CHECK (VALUE IN ('red', 'green', 'blue'));
235 SELECT 'purple'::rgb;
236 ERROR: value for domain rgb violates check constraint "rgb_check"
237 SELECT 'purple'::rainbow::rgb;
238 ERROR: value for domain rgb violates check constraint "rgb_check"
243 SELECT '{red,green,blue}'::rainbow[];
249 SELECT ('{red,green,blue}'::rainbow[])[2];
255 SELECT 'red' = ANY ('{red,green,blue}'::rainbow[]);
261 SELECT 'yellow' = ANY ('{red,green,blue}'::rainbow[]);
267 SELECT 'red' = ALL ('{red,green,blue}'::rainbow[]);
273 SELECT 'red' = ALL ('{red,red}'::rainbow[]);
282 SELECT enum_first(NULL::rainbow);
288 SELECT enum_last('green'::rainbow);
294 SELECT enum_range(NULL::rainbow);
296 ---------------------------------------
297 {red,orange,yellow,green,blue,purple}
300 SELECT enum_range('orange'::rainbow, 'green'::rainbow);
302 -----------------------
303 {orange,yellow,green}
306 SELECT enum_range(NULL, 'green'::rainbow);
308 ---------------------------
309 {red,orange,yellow,green}
312 SELECT enum_range('orange'::rainbow, NULL);
314 -----------------------------------
315 {orange,yellow,green,blue,purple}
318 SELECT enum_range(NULL::rainbow, NULL);
320 ---------------------------------------
321 {red,orange,yellow,green,blue,purple}
325 -- User functions, can't test perl/python etc here since may not be compiled.
327 CREATE FUNCTION echo_me(anyenum) RETURNS text AS $$
329 RETURN $1::text || 'omg';
332 SELECT echo_me('red'::rainbow);
339 -- Concrete function should override generic one
341 CREATE FUNCTION echo_me(rainbow) RETURNS text AS $$
343 RETURN $1::text || 'wtf';
346 SELECT echo_me('red'::rainbow);
353 -- If we drop the original generic one, we don't have to qualify the type
354 -- anymore, since there's only one match
356 DROP FUNCTION echo_me(anyenum);
357 SELECT echo_me('red');
363 DROP FUNCTION echo_me(rainbow);
365 -- RI triggers on enum types
367 CREATE TABLE enumtest_parent (id rainbow PRIMARY KEY);
368 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "enumtest_parent_pkey" for table "enumtest_parent"
369 CREATE TABLE enumtest_child (parent rainbow REFERENCES enumtest_parent);
370 INSERT INTO enumtest_parent VALUES ('red');
371 INSERT INTO enumtest_child VALUES ('red');
372 INSERT INTO enumtest_child VALUES ('blue'); -- fail
373 ERROR: insert or update on table "enumtest_child" violates foreign key constraint "enumtest_child_parent_fkey"
374 DETAIL: Key (parent)=(blue) is not present in table "enumtest_parent".
375 DELETE FROM enumtest_parent; -- fail
376 ERROR: update or delete on table "enumtest_parent" violates foreign key constraint "enumtest_child_parent_fkey" on table "enumtest_child"
377 DETAIL: Key (id)=(red) is still referenced from table "enumtest_child".
379 -- cross-type RI should fail
381 CREATE TYPE bogus AS ENUM('good', 'bad', 'ugly');
382 CREATE TABLE enumtest_bogus_child(parent bogus REFERENCES enumtest_parent);
383 ERROR: foreign key constraint "enumtest_bogus_child_parent_fkey" cannot be implemented
384 DETAIL: Key columns "parent" and "id" are of incompatible types: bogus and rainbow.
389 DROP TABLE enumtest_child;
390 DROP TABLE enumtest_parent;
394 -- Verify properly cleaned up
396 SELECT COUNT(*) FROM pg_type WHERE typname = 'rainbow';
402 SELECT * FROM pg_enum WHERE NOT EXISTS
403 (SELECT 1 FROM pg_type WHERE pg_type.oid = enumtypid);
404 enumtypid | enumlabel
405 -----------+-----------