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); 386SELECT * FROM enumtest WHERE col = 'orange'; 387 col 388-------- 389 orange 390(1 row) 391 392DROP INDEX enumtest_hash; 393-- 394-- End index tests 395-- 396RESET enable_seqscan; 397RESET enable_bitmapscan; 398-- 399-- Domains over enums 400-- 401CREATE DOMAIN rgb AS rainbow CHECK (VALUE IN ('red', 'green', 'blue')); 402SELECT 'red'::rgb; 403 rgb 404----- 405 red 406(1 row) 407 408SELECT 'purple'::rgb; 409ERROR: value for domain rgb violates check constraint "rgb_check" 410SELECT 'purple'::rainbow::rgb; 411ERROR: value for domain rgb violates check constraint "rgb_check" 412DROP DOMAIN rgb; 413-- 414-- Arrays 415-- 416SELECT '{red,green,blue}'::rainbow[]; 417 rainbow 418------------------ 419 {red,green,blue} 420(1 row) 421 422SELECT ('{red,green,blue}'::rainbow[])[2]; 423 rainbow 424--------- 425 green 426(1 row) 427 428SELECT 'red' = ANY ('{red,green,blue}'::rainbow[]); 429 ?column? 430---------- 431 t 432(1 row) 433 434SELECT 'yellow' = ANY ('{red,green,blue}'::rainbow[]); 435 ?column? 436---------- 437 f 438(1 row) 439 440SELECT 'red' = ALL ('{red,green,blue}'::rainbow[]); 441 ?column? 442---------- 443 f 444(1 row) 445 446SELECT 'red' = ALL ('{red,red}'::rainbow[]); 447 ?column? 448---------- 449 t 450(1 row) 451 452-- 453-- Support functions 454-- 455SELECT enum_first(NULL::rainbow); 456 enum_first 457------------ 458 red 459(1 row) 460 461SELECT enum_last('green'::rainbow); 462 enum_last 463----------- 464 purple 465(1 row) 466 467SELECT enum_range(NULL::rainbow); 468 enum_range 469--------------------------------------- 470 {red,orange,yellow,green,blue,purple} 471(1 row) 472 473SELECT enum_range('orange'::rainbow, 'green'::rainbow); 474 enum_range 475----------------------- 476 {orange,yellow,green} 477(1 row) 478 479SELECT enum_range(NULL, 'green'::rainbow); 480 enum_range 481--------------------------- 482 {red,orange,yellow,green} 483(1 row) 484 485SELECT enum_range('orange'::rainbow, NULL); 486 enum_range 487----------------------------------- 488 {orange,yellow,green,blue,purple} 489(1 row) 490 491SELECT enum_range(NULL::rainbow, NULL); 492 enum_range 493--------------------------------------- 494 {red,orange,yellow,green,blue,purple} 495(1 row) 496 497-- 498-- User functions, can't test perl/python etc here since may not be compiled. 499-- 500CREATE FUNCTION echo_me(anyenum) RETURNS text AS $$ 501BEGIN 502RETURN $1::text || 'omg'; 503END 504$$ LANGUAGE plpgsql; 505SELECT echo_me('red'::rainbow); 506 echo_me 507--------- 508 redomg 509(1 row) 510 511-- 512-- Concrete function should override generic one 513-- 514CREATE FUNCTION echo_me(rainbow) RETURNS text AS $$ 515BEGIN 516RETURN $1::text || 'wtf'; 517END 518$$ LANGUAGE plpgsql; 519SELECT echo_me('red'::rainbow); 520 echo_me 521--------- 522 redwtf 523(1 row) 524 525-- 526-- If we drop the original generic one, we don't have to qualify the type 527-- anymore, since there's only one match 528-- 529DROP FUNCTION echo_me(anyenum); 530SELECT echo_me('red'); 531 echo_me 532--------- 533 redwtf 534(1 row) 535 536DROP FUNCTION echo_me(rainbow); 537-- 538-- RI triggers on enum types 539-- 540CREATE TABLE enumtest_parent (id rainbow PRIMARY KEY); 541CREATE TABLE enumtest_child (parent rainbow REFERENCES enumtest_parent); 542INSERT INTO enumtest_parent VALUES ('red'); 543INSERT INTO enumtest_child VALUES ('red'); 544INSERT INTO enumtest_child VALUES ('blue'); -- fail 545ERROR: insert or update on table "enumtest_child" violates foreign key constraint "enumtest_child_parent_fkey" 546DETAIL: Key (parent)=(blue) is not present in table "enumtest_parent". 547DELETE FROM enumtest_parent; -- fail 548ERROR: update or delete on table "enumtest_parent" violates foreign key constraint "enumtest_child_parent_fkey" on table "enumtest_child" 549DETAIL: Key (id)=(red) is still referenced from table "enumtest_child". 550-- 551-- cross-type RI should fail 552-- 553CREATE TYPE bogus AS ENUM('good', 'bad', 'ugly'); 554CREATE TABLE enumtest_bogus_child(parent bogus REFERENCES enumtest_parent); 555ERROR: foreign key constraint "enumtest_bogus_child_parent_fkey" cannot be implemented 556DETAIL: Key columns "parent" and "id" are of incompatible types: bogus and rainbow. 557DROP TYPE bogus; 558-- check renaming a value 559ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson'; 560SELECT enumlabel, enumsortorder 561FROM pg_enum 562WHERE enumtypid = 'rainbow'::regtype 563ORDER BY 2; 564 enumlabel | enumsortorder 565-----------+--------------- 566 crimson | 1 567 orange | 2 568 yellow | 3 569 green | 4 570 blue | 5 571 purple | 6 572(6 rows) 573 574-- check that renaming a non-existent value fails 575ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson'; 576ERROR: "red" is not an existing enum label 577-- check that renaming to an existent value fails 578ALTER TYPE rainbow RENAME VALUE 'blue' TO 'green'; 579ERROR: enum label "green" already exists 580-- 581-- check transactional behaviour of ALTER TYPE ... ADD VALUE 582-- 583CREATE TYPE bogus AS ENUM('good'); 584-- check that we can add new values to existing enums in a transaction 585-- but we can't use them 586BEGIN; 587ALTER TYPE bogus ADD VALUE 'new'; 588SAVEPOINT x; 589SELECT 'new'::bogus; -- unsafe 590ERROR: unsafe use of new value "new" of enum type bogus 591LINE 1: SELECT 'new'::bogus; 592 ^ 593HINT: New enum values must be committed before they can be used. 594ROLLBACK TO x; 595SELECT enum_first(null::bogus); -- safe 596 enum_first 597------------ 598 good 599(1 row) 600 601SELECT enum_last(null::bogus); -- unsafe 602ERROR: unsafe use of new value "new" of enum type bogus 603HINT: New enum values must be committed before they can be used. 604ROLLBACK TO x; 605SELECT enum_range(null::bogus); -- unsafe 606ERROR: unsafe use of new value "new" of enum type bogus 607HINT: New enum values must be committed before they can be used. 608ROLLBACK TO x; 609COMMIT; 610SELECT 'new'::bogus; -- now safe 611 bogus 612------- 613 new 614(1 row) 615 616SELECT enumlabel, enumsortorder 617FROM pg_enum 618WHERE enumtypid = 'bogus'::regtype 619ORDER BY 2; 620 enumlabel | enumsortorder 621-----------+--------------- 622 good | 1 623 new | 2 624(2 rows) 625 626-- check that we recognize the case where the enum already existed but was 627-- modified in the current txn; this should not be considered safe 628BEGIN; 629ALTER TYPE bogus RENAME TO bogon; 630ALTER TYPE bogon ADD VALUE 'bad'; 631SELECT 'bad'::bogon; 632ERROR: unsafe use of new value "bad" of enum type bogon 633LINE 1: SELECT 'bad'::bogon; 634 ^ 635HINT: New enum values must be committed before they can be used. 636ROLLBACK; 637-- but a renamed value is safe to use later in same transaction 638BEGIN; 639ALTER TYPE bogus RENAME VALUE 'good' to 'bad'; 640SELECT 'bad'::bogus; 641 bogus 642------- 643 bad 644(1 row) 645 646ROLLBACK; 647DROP TYPE bogus; 648-- check that values created during CREATE TYPE can be used in any case 649BEGIN; 650CREATE TYPE bogus AS ENUM('good','bad','ugly'); 651ALTER TYPE bogus RENAME TO bogon; 652select enum_range(null::bogon); 653 enum_range 654----------------- 655 {good,bad,ugly} 656(1 row) 657 658ROLLBACK; 659-- ideally, we'd allow this usage; but it requires keeping track of whether 660-- the enum type was created in the current transaction, which is expensive 661BEGIN; 662CREATE TYPE bogus AS ENUM('good'); 663ALTER TYPE bogus RENAME TO bogon; 664ALTER TYPE bogon ADD VALUE 'bad'; 665ALTER TYPE bogon ADD VALUE 'ugly'; 666select enum_range(null::bogon); -- fails 667ERROR: unsafe use of new value "bad" of enum type bogon 668HINT: New enum values must be committed before they can be used. 669ROLLBACK; 670-- 671-- Cleanup 672-- 673DROP TABLE enumtest_child; 674DROP TABLE enumtest_parent; 675DROP TABLE enumtest; 676DROP TYPE rainbow; 677-- 678-- Verify properly cleaned up 679-- 680SELECT COUNT(*) FROM pg_type WHERE typname = 'rainbow'; 681 count 682------- 683 0 684(1 row) 685 686SELECT * FROM pg_enum WHERE NOT EXISTS 687 (SELECT 1 FROM pg_type WHERE pg_type.oid = enumtypid); 688 oid | enumtypid | enumsortorder | enumlabel 689-----+-----------+---------------+----------- 690(0 rows) 691 692