1-- 2-- Enum tests 3-- 4CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); 5-- 6-- Did it create the right number of rows? 7-- 8SELECT COUNT(*) FROM pg_enum WHERE enumtypid = 'rainbow'::regtype; 9 count 10------- 11 6 12(1 row) 13 14-- 15-- I/O functions 16-- 17SELECT 'red'::rainbow; 18 rainbow 19--------- 20 red 21(1 row) 22 23SELECT 'mauve'::rainbow; 24ERROR: invalid input value for enum rainbow: "mauve" 25LINE 1: SELECT 'mauve'::rainbow; 26 ^ 27-- 28-- adding new values 29-- 30CREATE TYPE planets AS ENUM ( 'venus', 'earth', 'mars' ); 31SELECT enumlabel, enumsortorder 32FROM pg_enum 33WHERE enumtypid = 'planets'::regtype 34ORDER BY 2; 35 enumlabel | enumsortorder 36-----------+--------------- 37 venus | 1 38 earth | 2 39 mars | 3 40(3 rows) 41 42ALTER TYPE planets ADD VALUE 'uranus'; 43SELECT enumlabel, enumsortorder 44FROM pg_enum 45WHERE enumtypid = 'planets'::regtype 46ORDER BY 2; 47 enumlabel | enumsortorder 48-----------+--------------- 49 venus | 1 50 earth | 2 51 mars | 3 52 uranus | 4 53(4 rows) 54 55ALTER TYPE planets ADD VALUE 'mercury' BEFORE 'venus'; 56ALTER TYPE planets ADD VALUE 'saturn' BEFORE 'uranus'; 57ALTER TYPE planets ADD VALUE 'jupiter' AFTER 'mars'; 58ALTER TYPE planets ADD VALUE 'neptune' AFTER 'uranus'; 59SELECT enumlabel, enumsortorder 60FROM pg_enum 61WHERE enumtypid = 'planets'::regtype 62ORDER BY 2; 63 enumlabel | enumsortorder 64-----------+--------------- 65 mercury | 0 66 venus | 1 67 earth | 2 68 mars | 3 69 jupiter | 3.25 70 saturn | 3.5 71 uranus | 4 72 neptune | 5 73(8 rows) 74 75SELECT enumlabel, enumsortorder 76FROM pg_enum 77WHERE enumtypid = 'planets'::regtype 78ORDER BY enumlabel::planets; 79 enumlabel | enumsortorder 80-----------+--------------- 81 mercury | 0 82 venus | 1 83 earth | 2 84 mars | 3 85 jupiter | 3.25 86 saturn | 3.5 87 uranus | 4 88 neptune | 5 89(8 rows) 90 91-- errors for adding labels 92ALTER TYPE planets ADD VALUE 93 'plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto'; 94ERROR: invalid enum label "plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto" 95DETAIL: Labels must be 63 characters or less. 96ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus'; 97ERROR: "zeus" is not an existing enum label 98-- if not exists tests 99-- existing value gives error 100ALTER TYPE planets ADD VALUE 'mercury'; 101ERROR: enum label "mercury" already exists 102-- unless IF NOT EXISTS is specified 103ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury'; 104NOTICE: enum label "mercury" already exists, skipping 105-- should be neptune, not mercury 106SELECT enum_last(NULL::planets); 107 enum_last 108----------- 109 neptune 110(1 row) 111 112ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto'; 113-- should be pluto, i.e. the new value 114SELECT enum_last(NULL::planets); 115 enum_last 116----------- 117 pluto 118(1 row) 119 120-- 121-- Test inserting so many values that we have to renumber 122-- 123create type insenum as enum ('L1', 'L2'); 124alter type insenum add value 'i1' before 'L2'; 125alter type insenum add value 'i2' before 'L2'; 126alter type insenum add value 'i3' before 'L2'; 127alter type insenum add value 'i4' before 'L2'; 128alter type insenum add value 'i5' before 'L2'; 129alter type insenum add value 'i6' before 'L2'; 130alter type insenum add value 'i7' before 'L2'; 131alter type insenum add value 'i8' before 'L2'; 132alter type insenum add value 'i9' before 'L2'; 133alter type insenum add value 'i10' before 'L2'; 134alter type insenum add value 'i11' before 'L2'; 135alter type insenum add value 'i12' before 'L2'; 136alter type insenum add value 'i13' before 'L2'; 137alter type insenum add value 'i14' before 'L2'; 138alter type insenum add value 'i15' before 'L2'; 139alter type insenum add value 'i16' before 'L2'; 140alter type insenum add value 'i17' before 'L2'; 141alter type insenum add value 'i18' before 'L2'; 142alter type insenum add value 'i19' before 'L2'; 143alter type insenum add value 'i20' before 'L2'; 144alter type insenum add value 'i21' before 'L2'; 145alter type insenum add value 'i22' before 'L2'; 146alter type insenum add value 'i23' before 'L2'; 147alter type insenum add value 'i24' before 'L2'; 148alter type insenum add value 'i25' before 'L2'; 149alter type insenum add value 'i26' before 'L2'; 150alter type insenum add value 'i27' before 'L2'; 151alter type insenum add value 'i28' before 'L2'; 152alter type insenum add value 'i29' before 'L2'; 153alter type insenum add value 'i30' before 'L2'; 154-- The exact values of enumsortorder will now depend on the local properties 155-- of float4, but in any reasonable implementation we should get at least 156-- 20 splits before having to renumber; so only hide values > 20. 157SELECT enumlabel, 158 case when enumsortorder > 20 then null else enumsortorder end as so 159FROM pg_enum 160WHERE enumtypid = 'insenum'::regtype 161ORDER BY enumsortorder; 162 enumlabel | so 163-----------+---- 164 L1 | 1 165 i1 | 2 166 i2 | 3 167 i3 | 4 168 i4 | 5 169 i5 | 6 170 i6 | 7 171 i7 | 8 172 i8 | 9 173 i9 | 10 174 i10 | 11 175 i11 | 12 176 i12 | 13 177 i13 | 14 178 i14 | 15 179 i15 | 16 180 i16 | 17 181 i17 | 18 182 i18 | 19 183 i19 | 20 184 i20 | 185 i21 | 186 i22 | 187 i23 | 188 i24 | 189 i25 | 190 i26 | 191 i27 | 192 i28 | 193 i29 | 194 i30 | 195 L2 | 196(32 rows) 197 198-- 199-- Basic table creation, row selection 200-- 201CREATE TABLE enumtest (col rainbow); 202INSERT INTO enumtest values ('red'), ('orange'), ('yellow'), ('green'); 203COPY enumtest FROM stdin; 204SELECT * FROM enumtest; 205 col 206-------- 207 red 208 orange 209 yellow 210 green 211 blue 212 purple 213(6 rows) 214 215-- 216-- Operators, no index 217-- 218SELECT * FROM enumtest WHERE col = 'orange'; 219 col 220-------- 221 orange 222(1 row) 223 224SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col; 225 col 226-------- 227 red 228 yellow 229 green 230 blue 231 purple 232(5 rows) 233 234SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col; 235 col 236-------- 237 green 238 blue 239 purple 240(3 rows) 241 242SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col; 243 col 244-------- 245 yellow 246 green 247 blue 248 purple 249(4 rows) 250 251SELECT * FROM enumtest WHERE col < 'green' ORDER BY col; 252 col 253-------- 254 red 255 orange 256 yellow 257(3 rows) 258 259SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col; 260 col 261-------- 262 red 263 orange 264 yellow 265 green 266(4 rows) 267 268-- 269-- Cast to/from text 270-- 271SELECT 'red'::rainbow::text || 'hithere'; 272 ?column? 273------------ 274 redhithere 275(1 row) 276 277SELECT 'red'::text::rainbow = 'red'::rainbow; 278 ?column? 279---------- 280 t 281(1 row) 282 283-- 284-- Aggregates 285-- 286SELECT min(col) FROM enumtest; 287 min 288----- 289 red 290(1 row) 291 292SELECT max(col) FROM enumtest; 293 max 294-------- 295 purple 296(1 row) 297 298SELECT max(col) FROM enumtest WHERE col < 'green'; 299 max 300-------- 301 yellow 302(1 row) 303 304-- 305-- Index tests, force use of index 306-- 307SET enable_seqscan = off; 308SET enable_bitmapscan = off; 309-- 310-- Btree index / opclass with the various operators 311-- 312CREATE UNIQUE INDEX enumtest_btree ON enumtest USING btree (col); 313SELECT * FROM enumtest WHERE col = 'orange'; 314 col 315-------- 316 orange 317(1 row) 318 319SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col; 320 col 321-------- 322 red 323 yellow 324 green 325 blue 326 purple 327(5 rows) 328 329SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col; 330 col 331-------- 332 green 333 blue 334 purple 335(3 rows) 336 337SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col; 338 col 339-------- 340 yellow 341 green 342 blue 343 purple 344(4 rows) 345 346SELECT * FROM enumtest WHERE col < 'green' ORDER BY col; 347 col 348-------- 349 red 350 orange 351 yellow 352(3 rows) 353 354SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col; 355 col 356-------- 357 red 358 orange 359 yellow 360 green 361(4 rows) 362 363SELECT min(col) FROM enumtest; 364 min 365----- 366 red 367(1 row) 368 369SELECT max(col) FROM enumtest; 370 max 371-------- 372 purple 373(1 row) 374 375SELECT max(col) FROM enumtest WHERE col < 'green'; 376 max 377-------- 378 yellow 379(1 row) 380 381DROP INDEX enumtest_btree; 382-- 383-- Hash index / opclass with the = operator 384-- 385CREATE INDEX enumtest_hash ON enumtest USING hash (col); 386WARNING: hash indexes are not WAL-logged and their use is discouraged 387SELECT * FROM enumtest WHERE col = 'orange'; 388 col 389-------- 390 orange 391(1 row) 392 393DROP INDEX enumtest_hash; 394-- 395-- End index tests 396-- 397RESET enable_seqscan; 398RESET enable_bitmapscan; 399-- 400-- Domains over enums 401-- 402CREATE DOMAIN rgb AS rainbow CHECK (VALUE IN ('red', 'green', 'blue')); 403SELECT 'red'::rgb; 404 rgb 405----- 406 red 407(1 row) 408 409SELECT 'purple'::rgb; 410ERROR: value for domain rgb violates check constraint "rgb_check" 411SELECT 'purple'::rainbow::rgb; 412ERROR: value for domain rgb violates check constraint "rgb_check" 413DROP DOMAIN rgb; 414-- 415-- Arrays 416-- 417SELECT '{red,green,blue}'::rainbow[]; 418 rainbow 419------------------ 420 {red,green,blue} 421(1 row) 422 423SELECT ('{red,green,blue}'::rainbow[])[2]; 424 rainbow 425--------- 426 green 427(1 row) 428 429SELECT 'red' = ANY ('{red,green,blue}'::rainbow[]); 430 ?column? 431---------- 432 t 433(1 row) 434 435SELECT 'yellow' = ANY ('{red,green,blue}'::rainbow[]); 436 ?column? 437---------- 438 f 439(1 row) 440 441SELECT 'red' = ALL ('{red,green,blue}'::rainbow[]); 442 ?column? 443---------- 444 f 445(1 row) 446 447SELECT 'red' = ALL ('{red,red}'::rainbow[]); 448 ?column? 449---------- 450 t 451(1 row) 452 453-- 454-- Support functions 455-- 456SELECT enum_first(NULL::rainbow); 457 enum_first 458------------ 459 red 460(1 row) 461 462SELECT enum_last('green'::rainbow); 463 enum_last 464----------- 465 purple 466(1 row) 467 468SELECT enum_range(NULL::rainbow); 469 enum_range 470--------------------------------------- 471 {red,orange,yellow,green,blue,purple} 472(1 row) 473 474SELECT enum_range('orange'::rainbow, 'green'::rainbow); 475 enum_range 476----------------------- 477 {orange,yellow,green} 478(1 row) 479 480SELECT enum_range(NULL, 'green'::rainbow); 481 enum_range 482--------------------------- 483 {red,orange,yellow,green} 484(1 row) 485 486SELECT enum_range('orange'::rainbow, NULL); 487 enum_range 488----------------------------------- 489 {orange,yellow,green,blue,purple} 490(1 row) 491 492SELECT enum_range(NULL::rainbow, NULL); 493 enum_range 494--------------------------------------- 495 {red,orange,yellow,green,blue,purple} 496(1 row) 497 498-- 499-- User functions, can't test perl/python etc here since may not be compiled. 500-- 501CREATE FUNCTION echo_me(anyenum) RETURNS text AS $$ 502BEGIN 503RETURN $1::text || 'omg'; 504END 505$$ LANGUAGE plpgsql; 506SELECT echo_me('red'::rainbow); 507 echo_me 508--------- 509 redomg 510(1 row) 511 512-- 513-- Concrete function should override generic one 514-- 515CREATE FUNCTION echo_me(rainbow) RETURNS text AS $$ 516BEGIN 517RETURN $1::text || 'wtf'; 518END 519$$ LANGUAGE plpgsql; 520SELECT echo_me('red'::rainbow); 521 echo_me 522--------- 523 redwtf 524(1 row) 525 526-- 527-- If we drop the original generic one, we don't have to qualify the type 528-- anymore, since there's only one match 529-- 530DROP FUNCTION echo_me(anyenum); 531SELECT echo_me('red'); 532 echo_me 533--------- 534 redwtf 535(1 row) 536 537DROP FUNCTION echo_me(rainbow); 538-- 539-- RI triggers on enum types 540-- 541CREATE TABLE enumtest_parent (id rainbow PRIMARY KEY); 542CREATE TABLE enumtest_child (parent rainbow REFERENCES enumtest_parent); 543INSERT INTO enumtest_parent VALUES ('red'); 544INSERT INTO enumtest_child VALUES ('red'); 545INSERT INTO enumtest_child VALUES ('blue'); -- fail 546ERROR: insert or update on table "enumtest_child" violates foreign key constraint "enumtest_child_parent_fkey" 547DETAIL: Key (parent)=(blue) is not present in table "enumtest_parent". 548DELETE FROM enumtest_parent; -- fail 549ERROR: update or delete on table "enumtest_parent" violates foreign key constraint "enumtest_child_parent_fkey" on table "enumtest_child" 550DETAIL: Key (id)=(red) is still referenced from table "enumtest_child". 551-- 552-- cross-type RI should fail 553-- 554CREATE TYPE bogus AS ENUM('good', 'bad', 'ugly'); 555CREATE TABLE enumtest_bogus_child(parent bogus REFERENCES enumtest_parent); 556ERROR: foreign key constraint "enumtest_bogus_child_parent_fkey" cannot be implemented 557DETAIL: Key columns "parent" and "id" are of incompatible types: bogus and rainbow. 558DROP TYPE bogus; 559-- 560-- check transactional behaviour of ALTER TYPE ... ADD VALUE 561-- 562CREATE TYPE bogus AS ENUM('good'); 563-- check that we can't add new values to existing enums in a transaction 564BEGIN; 565ALTER TYPE bogus ADD VALUE 'bad'; 566ERROR: ALTER TYPE ... ADD cannot run inside a transaction block 567COMMIT; 568-- check that we recognize the case where the enum already existed but was 569-- modified in the current txn 570BEGIN; 571ALTER TYPE bogus RENAME TO bogon; 572ALTER TYPE bogon ADD VALUE 'bad'; 573ERROR: ALTER TYPE ... ADD cannot run inside a transaction block 574ROLLBACK; 575DROP TYPE bogus; 576-- check that we *can* add new values to existing enums in a transaction, 577-- if the type is new as well 578BEGIN; 579CREATE TYPE bogus AS ENUM(); 580ALTER TYPE bogus ADD VALUE 'good'; 581ALTER TYPE bogus ADD VALUE 'ugly'; 582ROLLBACK; 583-- 584-- Cleanup 585-- 586DROP TABLE enumtest_child; 587DROP TABLE enumtest_parent; 588DROP TABLE enumtest; 589DROP TYPE rainbow; 590-- 591-- Verify properly cleaned up 592-- 593SELECT COUNT(*) FROM pg_type WHERE typname = 'rainbow'; 594 count 595------- 596 0 597(1 row) 598 599SELECT * FROM pg_enum WHERE NOT EXISTS 600 (SELECT 1 FROM pg_type WHERE pg_type.oid = enumtypid); 601 enumtypid | enumsortorder | enumlabel 602-----------+---------------+----------- 603(0 rows) 604 605