1-- 2-- Enum tests 3-- 4 5CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); 6 7-- 8-- Did it create the right number of rows? 9-- 10SELECT COUNT(*) FROM pg_enum WHERE enumtypid = 'rainbow'::regtype; 11 12-- 13-- I/O functions 14-- 15SELECT 'red'::rainbow; 16SELECT 'mauve'::rainbow; 17 18-- 19-- adding new values 20-- 21 22CREATE TYPE planets AS ENUM ( 'venus', 'earth', 'mars' ); 23 24SELECT enumlabel, enumsortorder 25FROM pg_enum 26WHERE enumtypid = 'planets'::regtype 27ORDER BY 2; 28 29ALTER TYPE planets ADD VALUE 'uranus'; 30 31SELECT enumlabel, enumsortorder 32FROM pg_enum 33WHERE enumtypid = 'planets'::regtype 34ORDER BY 2; 35 36ALTER TYPE planets ADD VALUE 'mercury' BEFORE 'venus'; 37ALTER TYPE planets ADD VALUE 'saturn' BEFORE 'uranus'; 38ALTER TYPE planets ADD VALUE 'jupiter' AFTER 'mars'; 39ALTER TYPE planets ADD VALUE 'neptune' AFTER 'uranus'; 40 41SELECT enumlabel, enumsortorder 42FROM pg_enum 43WHERE enumtypid = 'planets'::regtype 44ORDER BY 2; 45 46SELECT enumlabel, enumsortorder 47FROM pg_enum 48WHERE enumtypid = 'planets'::regtype 49ORDER BY enumlabel::planets; 50 51-- errors for adding labels 52ALTER TYPE planets ADD VALUE 53 'plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto'; 54 55ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus'; 56 57-- if not exists tests 58 59-- existing value gives error 60ALTER TYPE planets ADD VALUE 'mercury'; 61 62-- unless IF NOT EXISTS is specified 63ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury'; 64 65-- should be neptune, not mercury 66SELECT enum_last(NULL::planets); 67 68ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto'; 69 70-- should be pluto, i.e. the new value 71SELECT enum_last(NULL::planets); 72 73-- 74-- Test inserting so many values that we have to renumber 75-- 76 77create type insenum as enum ('L1', 'L2'); 78 79alter type insenum add value 'i1' before 'L2'; 80alter type insenum add value 'i2' before 'L2'; 81alter type insenum add value 'i3' before 'L2'; 82alter type insenum add value 'i4' before 'L2'; 83alter type insenum add value 'i5' before 'L2'; 84alter type insenum add value 'i6' before 'L2'; 85alter type insenum add value 'i7' before 'L2'; 86alter type insenum add value 'i8' before 'L2'; 87alter type insenum add value 'i9' before 'L2'; 88alter type insenum add value 'i10' before 'L2'; 89alter type insenum add value 'i11' before 'L2'; 90alter type insenum add value 'i12' before 'L2'; 91alter type insenum add value 'i13' before 'L2'; 92alter type insenum add value 'i14' before 'L2'; 93alter type insenum add value 'i15' before 'L2'; 94alter type insenum add value 'i16' before 'L2'; 95alter type insenum add value 'i17' before 'L2'; 96alter type insenum add value 'i18' before 'L2'; 97alter type insenum add value 'i19' before 'L2'; 98alter type insenum add value 'i20' before 'L2'; 99alter type insenum add value 'i21' before 'L2'; 100alter type insenum add value 'i22' before 'L2'; 101alter type insenum add value 'i23' before 'L2'; 102alter type insenum add value 'i24' before 'L2'; 103alter type insenum add value 'i25' before 'L2'; 104alter type insenum add value 'i26' before 'L2'; 105alter type insenum add value 'i27' before 'L2'; 106alter type insenum add value 'i28' before 'L2'; 107alter type insenum add value 'i29' before 'L2'; 108alter type insenum add value 'i30' before 'L2'; 109 110-- The exact values of enumsortorder will now depend on the local properties 111-- of float4, but in any reasonable implementation we should get at least 112-- 20 splits before having to renumber; so only hide values > 20. 113 114SELECT enumlabel, 115 case when enumsortorder > 20 then null else enumsortorder end as so 116FROM pg_enum 117WHERE enumtypid = 'insenum'::regtype 118ORDER BY enumsortorder; 119 120-- 121-- Basic table creation, row selection 122-- 123CREATE TABLE enumtest (col rainbow); 124INSERT INTO enumtest values ('red'), ('orange'), ('yellow'), ('green'); 125COPY enumtest FROM stdin; 126blue 127purple 128\. 129SELECT * FROM enumtest; 130 131-- 132-- Operators, no index 133-- 134SELECT * FROM enumtest WHERE col = 'orange'; 135SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col; 136SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col; 137SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col; 138SELECT * FROM enumtest WHERE col < 'green' ORDER BY col; 139SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col; 140 141-- 142-- Cast to/from text 143-- 144SELECT 'red'::rainbow::text || 'hithere'; 145SELECT 'red'::text::rainbow = 'red'::rainbow; 146 147-- 148-- Aggregates 149-- 150SELECT min(col) FROM enumtest; 151SELECT max(col) FROM enumtest; 152SELECT max(col) FROM enumtest WHERE col < 'green'; 153 154-- 155-- Index tests, force use of index 156-- 157SET enable_seqscan = off; 158SET enable_bitmapscan = off; 159 160-- 161-- Btree index / opclass with the various operators 162-- 163CREATE UNIQUE INDEX enumtest_btree ON enumtest USING btree (col); 164SELECT * FROM enumtest WHERE col = 'orange'; 165SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col; 166SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col; 167SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col; 168SELECT * FROM enumtest WHERE col < 'green' ORDER BY col; 169SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col; 170SELECT min(col) FROM enumtest; 171SELECT max(col) FROM enumtest; 172SELECT max(col) FROM enumtest WHERE col < 'green'; 173DROP INDEX enumtest_btree; 174 175-- 176-- Hash index / opclass with the = operator 177-- 178CREATE INDEX enumtest_hash ON enumtest USING hash (col); 179SELECT * FROM enumtest WHERE col = 'orange'; 180DROP INDEX enumtest_hash; 181 182-- 183-- End index tests 184-- 185RESET enable_seqscan; 186RESET enable_bitmapscan; 187 188-- 189-- Domains over enums 190-- 191CREATE DOMAIN rgb AS rainbow CHECK (VALUE IN ('red', 'green', 'blue')); 192SELECT 'red'::rgb; 193SELECT 'purple'::rgb; 194SELECT 'purple'::rainbow::rgb; 195DROP DOMAIN rgb; 196 197-- 198-- Arrays 199-- 200SELECT '{red,green,blue}'::rainbow[]; 201SELECT ('{red,green,blue}'::rainbow[])[2]; 202SELECT 'red' = ANY ('{red,green,blue}'::rainbow[]); 203SELECT 'yellow' = ANY ('{red,green,blue}'::rainbow[]); 204SELECT 'red' = ALL ('{red,green,blue}'::rainbow[]); 205SELECT 'red' = ALL ('{red,red}'::rainbow[]); 206 207-- 208-- Support functions 209-- 210SELECT enum_first(NULL::rainbow); 211SELECT enum_last('green'::rainbow); 212SELECT enum_range(NULL::rainbow); 213SELECT enum_range('orange'::rainbow, 'green'::rainbow); 214SELECT enum_range(NULL, 'green'::rainbow); 215SELECT enum_range('orange'::rainbow, NULL); 216SELECT enum_range(NULL::rainbow, NULL); 217 218-- 219-- User functions, can't test perl/python etc here since may not be compiled. 220-- 221CREATE FUNCTION echo_me(anyenum) RETURNS text AS $$ 222BEGIN 223RETURN $1::text || 'omg'; 224END 225$$ LANGUAGE plpgsql; 226SELECT echo_me('red'::rainbow); 227-- 228-- Concrete function should override generic one 229-- 230CREATE FUNCTION echo_me(rainbow) RETURNS text AS $$ 231BEGIN 232RETURN $1::text || 'wtf'; 233END 234$$ LANGUAGE plpgsql; 235SELECT echo_me('red'::rainbow); 236-- 237-- If we drop the original generic one, we don't have to qualify the type 238-- anymore, since there's only one match 239-- 240DROP FUNCTION echo_me(anyenum); 241SELECT echo_me('red'); 242DROP FUNCTION echo_me(rainbow); 243 244-- 245-- RI triggers on enum types 246-- 247CREATE TABLE enumtest_parent (id rainbow PRIMARY KEY); 248CREATE TABLE enumtest_child (parent rainbow REFERENCES enumtest_parent); 249INSERT INTO enumtest_parent VALUES ('red'); 250INSERT INTO enumtest_child VALUES ('red'); 251INSERT INTO enumtest_child VALUES ('blue'); -- fail 252DELETE FROM enumtest_parent; -- fail 253-- 254-- cross-type RI should fail 255-- 256CREATE TYPE bogus AS ENUM('good', 'bad', 'ugly'); 257CREATE TABLE enumtest_bogus_child(parent bogus REFERENCES enumtest_parent); 258DROP TYPE bogus; 259 260-- check renaming a value 261ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson'; 262SELECT enumlabel, enumsortorder 263FROM pg_enum 264WHERE enumtypid = 'rainbow'::regtype 265ORDER BY 2; 266-- check that renaming a non-existent value fails 267ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson'; 268-- check that renaming to an existent value fails 269ALTER TYPE rainbow RENAME VALUE 'blue' TO 'green'; 270 271-- 272-- check transactional behaviour of ALTER TYPE ... ADD VALUE 273-- 274CREATE TYPE bogus AS ENUM('good'); 275 276-- check that we can add new values to existing enums in a transaction 277-- but we can't use them 278BEGIN; 279ALTER TYPE bogus ADD VALUE 'new'; 280SAVEPOINT x; 281SELECT 'new'::bogus; -- unsafe 282ROLLBACK TO x; 283SELECT enum_first(null::bogus); -- safe 284SELECT enum_last(null::bogus); -- unsafe 285ROLLBACK TO x; 286SELECT enum_range(null::bogus); -- unsafe 287ROLLBACK TO x; 288COMMIT; 289SELECT 'new'::bogus; -- now safe 290SELECT enumlabel, enumsortorder 291FROM pg_enum 292WHERE enumtypid = 'bogus'::regtype 293ORDER BY 2; 294 295-- check that we recognize the case where the enum already existed but was 296-- modified in the current txn; this should not be considered safe 297BEGIN; 298ALTER TYPE bogus RENAME TO bogon; 299ALTER TYPE bogon ADD VALUE 'bad'; 300SELECT 'bad'::bogon; 301ROLLBACK; 302 303-- but a renamed value is safe to use later in same transaction 304BEGIN; 305ALTER TYPE bogus RENAME VALUE 'good' to 'bad'; 306SELECT 'bad'::bogus; 307ROLLBACK; 308 309DROP TYPE bogus; 310 311-- check that values created during CREATE TYPE can be used in any case 312BEGIN; 313CREATE TYPE bogus AS ENUM('good','bad','ugly'); 314ALTER TYPE bogus RENAME TO bogon; 315select enum_range(null::bogon); 316ROLLBACK; 317 318-- ideally, we'd allow this usage; but it requires keeping track of whether 319-- the enum type was created in the current transaction, which is expensive 320BEGIN; 321CREATE TYPE bogus AS ENUM('good'); 322ALTER TYPE bogus RENAME TO bogon; 323ALTER TYPE bogon ADD VALUE 'bad'; 324ALTER TYPE bogon ADD VALUE 'ugly'; 325select enum_range(null::bogon); -- fails 326ROLLBACK; 327 328-- 329-- Cleanup 330-- 331DROP TABLE enumtest_child; 332DROP TABLE enumtest_parent; 333DROP TABLE enumtest; 334DROP TYPE rainbow; 335 336-- 337-- Verify properly cleaned up 338-- 339SELECT COUNT(*) FROM pg_type WHERE typname = 'rainbow'; 340SELECT * FROM pg_enum WHERE NOT EXISTS 341 (SELECT 1 FROM pg_type WHERE pg_type.oid = enumtypid); 342