summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/enum.out
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /src/test/regress/expected/enum.out
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/test/regress/expected/enum.out')
-rw-r--r--src/test/regress/expected/enum.out691
1 files changed, 691 insertions, 0 deletions
diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.out
new file mode 100644
index 0000000..908f67e
--- /dev/null
+++ b/src/test/regress/expected/enum.out
@@ -0,0 +1,691 @@
+--
+-- Enum tests
+--
+CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');
+--
+-- Did it create the right number of rows?
+--
+SELECT COUNT(*) FROM pg_enum WHERE enumtypid = 'rainbow'::regtype;
+ count
+-------
+ 6
+(1 row)
+
+--
+-- I/O functions
+--
+SELECT 'red'::rainbow;
+ rainbow
+---------
+ red
+(1 row)
+
+SELECT 'mauve'::rainbow;
+ERROR: invalid input value for enum rainbow: "mauve"
+LINE 1: SELECT 'mauve'::rainbow;
+ ^
+--
+-- adding new values
+--
+CREATE TYPE planets AS ENUM ( 'venus', 'earth', 'mars' );
+SELECT enumlabel, enumsortorder
+FROM pg_enum
+WHERE enumtypid = 'planets'::regtype
+ORDER BY 2;
+ enumlabel | enumsortorder
+-----------+---------------
+ venus | 1
+ earth | 2
+ mars | 3
+(3 rows)
+
+ALTER TYPE planets ADD VALUE 'uranus';
+SELECT enumlabel, enumsortorder
+FROM pg_enum
+WHERE enumtypid = 'planets'::regtype
+ORDER BY 2;
+ enumlabel | enumsortorder
+-----------+---------------
+ venus | 1
+ earth | 2
+ mars | 3
+ uranus | 4
+(4 rows)
+
+ALTER TYPE planets ADD VALUE 'mercury' BEFORE 'venus';
+ALTER TYPE planets ADD VALUE 'saturn' BEFORE 'uranus';
+ALTER TYPE planets ADD VALUE 'jupiter' AFTER 'mars';
+ALTER TYPE planets ADD VALUE 'neptune' AFTER 'uranus';
+SELECT enumlabel, enumsortorder
+FROM pg_enum
+WHERE enumtypid = 'planets'::regtype
+ORDER BY 2;
+ enumlabel | enumsortorder
+-----------+---------------
+ mercury | 0
+ venus | 1
+ earth | 2
+ mars | 3
+ jupiter | 3.25
+ saturn | 3.5
+ uranus | 4
+ neptune | 5
+(8 rows)
+
+SELECT enumlabel, enumsortorder
+FROM pg_enum
+WHERE enumtypid = 'planets'::regtype
+ORDER BY enumlabel::planets;
+ enumlabel | enumsortorder
+-----------+---------------
+ mercury | 0
+ venus | 1
+ earth | 2
+ mars | 3
+ jupiter | 3.25
+ saturn | 3.5
+ uranus | 4
+ neptune | 5
+(8 rows)
+
+-- errors for adding labels
+ALTER TYPE planets ADD VALUE
+ 'plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto';
+ERROR: invalid enum label "plutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutoplutopluto"
+DETAIL: Labels must be 63 bytes or less.
+ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
+ERROR: "zeus" is not an existing enum label
+-- if not exists tests
+-- existing value gives error
+ALTER TYPE planets ADD VALUE 'mercury';
+ERROR: enum label "mercury" already exists
+-- unless IF NOT EXISTS is specified
+ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury';
+NOTICE: enum label "mercury" already exists, skipping
+-- should be neptune, not mercury
+SELECT enum_last(NULL::planets);
+ enum_last
+-----------
+ neptune
+(1 row)
+
+ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto';
+-- should be pluto, i.e. the new value
+SELECT enum_last(NULL::planets);
+ enum_last
+-----------
+ pluto
+(1 row)
+
+--
+-- Test inserting so many values that we have to renumber
+--
+create type insenum as enum ('L1', 'L2');
+alter type insenum add value 'i1' before 'L2';
+alter type insenum add value 'i2' before 'L2';
+alter type insenum add value 'i3' before 'L2';
+alter type insenum add value 'i4' before 'L2';
+alter type insenum add value 'i5' before 'L2';
+alter type insenum add value 'i6' before 'L2';
+alter type insenum add value 'i7' before 'L2';
+alter type insenum add value 'i8' before 'L2';
+alter type insenum add value 'i9' before 'L2';
+alter type insenum add value 'i10' before 'L2';
+alter type insenum add value 'i11' before 'L2';
+alter type insenum add value 'i12' before 'L2';
+alter type insenum add value 'i13' before 'L2';
+alter type insenum add value 'i14' before 'L2';
+alter type insenum add value 'i15' before 'L2';
+alter type insenum add value 'i16' before 'L2';
+alter type insenum add value 'i17' before 'L2';
+alter type insenum add value 'i18' before 'L2';
+alter type insenum add value 'i19' before 'L2';
+alter type insenum add value 'i20' before 'L2';
+alter type insenum add value 'i21' before 'L2';
+alter type insenum add value 'i22' before 'L2';
+alter type insenum add value 'i23' before 'L2';
+alter type insenum add value 'i24' before 'L2';
+alter type insenum add value 'i25' before 'L2';
+alter type insenum add value 'i26' before 'L2';
+alter type insenum add value 'i27' before 'L2';
+alter type insenum add value 'i28' before 'L2';
+alter type insenum add value 'i29' before 'L2';
+alter type insenum add value 'i30' before 'L2';
+-- The exact values of enumsortorder will now depend on the local properties
+-- of float4, but in any reasonable implementation we should get at least
+-- 20 splits before having to renumber; so only hide values > 20.
+SELECT enumlabel,
+ case when enumsortorder > 20 then null else enumsortorder end as so
+FROM pg_enum
+WHERE enumtypid = 'insenum'::regtype
+ORDER BY enumsortorder;
+ enumlabel | so
+-----------+----
+ L1 | 1
+ i1 | 2
+ i2 | 3
+ i3 | 4
+ i4 | 5
+ i5 | 6
+ i6 | 7
+ i7 | 8
+ i8 | 9
+ i9 | 10
+ i10 | 11
+ i11 | 12
+ i12 | 13
+ i13 | 14
+ i14 | 15
+ i15 | 16
+ i16 | 17
+ i17 | 18
+ i18 | 19
+ i19 | 20
+ i20 |
+ i21 |
+ i22 |
+ i23 |
+ i24 |
+ i25 |
+ i26 |
+ i27 |
+ i28 |
+ i29 |
+ i30 |
+ L2 |
+(32 rows)
+
+--
+-- Basic table creation, row selection
+--
+CREATE TABLE enumtest (col rainbow);
+INSERT INTO enumtest values ('red'), ('orange'), ('yellow'), ('green');
+COPY enumtest FROM stdin;
+SELECT * FROM enumtest;
+ col
+--------
+ red
+ orange
+ yellow
+ green
+ blue
+ purple
+(6 rows)
+
+--
+-- Operators, no index
+--
+SELECT * FROM enumtest WHERE col = 'orange';
+ col
+--------
+ orange
+(1 row)
+
+SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col;
+ col
+--------
+ red
+ yellow
+ green
+ blue
+ purple
+(5 rows)
+
+SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col;
+ col
+--------
+ green
+ blue
+ purple
+(3 rows)
+
+SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col;
+ col
+--------
+ yellow
+ green
+ blue
+ purple
+(4 rows)
+
+SELECT * FROM enumtest WHERE col < 'green' ORDER BY col;
+ col
+--------
+ red
+ orange
+ yellow
+(3 rows)
+
+SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col;
+ col
+--------
+ red
+ orange
+ yellow
+ green
+(4 rows)
+
+--
+-- Cast to/from text
+--
+SELECT 'red'::rainbow::text || 'hithere';
+ ?column?
+------------
+ redhithere
+(1 row)
+
+SELECT 'red'::text::rainbow = 'red'::rainbow;
+ ?column?
+----------
+ t
+(1 row)
+
+--
+-- Aggregates
+--
+SELECT min(col) FROM enumtest;
+ min
+-----
+ red
+(1 row)
+
+SELECT max(col) FROM enumtest;
+ max
+--------
+ purple
+(1 row)
+
+SELECT max(col) FROM enumtest WHERE col < 'green';
+ max
+--------
+ yellow
+(1 row)
+
+--
+-- Index tests, force use of index
+--
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+--
+-- Btree index / opclass with the various operators
+--
+CREATE UNIQUE INDEX enumtest_btree ON enumtest USING btree (col);
+SELECT * FROM enumtest WHERE col = 'orange';
+ col
+--------
+ orange
+(1 row)
+
+SELECT * FROM enumtest WHERE col <> 'orange' ORDER BY col;
+ col
+--------
+ red
+ yellow
+ green
+ blue
+ purple
+(5 rows)
+
+SELECT * FROM enumtest WHERE col > 'yellow' ORDER BY col;
+ col
+--------
+ green
+ blue
+ purple
+(3 rows)
+
+SELECT * FROM enumtest WHERE col >= 'yellow' ORDER BY col;
+ col
+--------
+ yellow
+ green
+ blue
+ purple
+(4 rows)
+
+SELECT * FROM enumtest WHERE col < 'green' ORDER BY col;
+ col
+--------
+ red
+ orange
+ yellow
+(3 rows)
+
+SELECT * FROM enumtest WHERE col <= 'green' ORDER BY col;
+ col
+--------
+ red
+ orange
+ yellow
+ green
+(4 rows)
+
+SELECT min(col) FROM enumtest;
+ min
+-----
+ red
+(1 row)
+
+SELECT max(col) FROM enumtest;
+ max
+--------
+ purple
+(1 row)
+
+SELECT max(col) FROM enumtest WHERE col < 'green';
+ max
+--------
+ yellow
+(1 row)
+
+DROP INDEX enumtest_btree;
+--
+-- Hash index / opclass with the = operator
+--
+CREATE INDEX enumtest_hash ON enumtest USING hash (col);
+SELECT * FROM enumtest WHERE col = 'orange';
+ col
+--------
+ orange
+(1 row)
+
+DROP INDEX enumtest_hash;
+--
+-- End index tests
+--
+RESET enable_seqscan;
+RESET enable_bitmapscan;
+--
+-- Domains over enums
+--
+CREATE DOMAIN rgb AS rainbow CHECK (VALUE IN ('red', 'green', 'blue'));
+SELECT 'red'::rgb;
+ rgb
+-----
+ red
+(1 row)
+
+SELECT 'purple'::rgb;
+ERROR: value for domain rgb violates check constraint "rgb_check"
+SELECT 'purple'::rainbow::rgb;
+ERROR: value for domain rgb violates check constraint "rgb_check"
+DROP DOMAIN rgb;
+--
+-- Arrays
+--
+SELECT '{red,green,blue}'::rainbow[];
+ rainbow
+------------------
+ {red,green,blue}
+(1 row)
+
+SELECT ('{red,green,blue}'::rainbow[])[2];
+ rainbow
+---------
+ green
+(1 row)
+
+SELECT 'red' = ANY ('{red,green,blue}'::rainbow[]);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT 'yellow' = ANY ('{red,green,blue}'::rainbow[]);
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT 'red' = ALL ('{red,green,blue}'::rainbow[]);
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT 'red' = ALL ('{red,red}'::rainbow[]);
+ ?column?
+----------
+ t
+(1 row)
+
+--
+-- Support functions
+--
+SELECT enum_first(NULL::rainbow);
+ enum_first
+------------
+ red
+(1 row)
+
+SELECT enum_last('green'::rainbow);
+ enum_last
+-----------
+ purple
+(1 row)
+
+SELECT enum_range(NULL::rainbow);
+ enum_range
+---------------------------------------
+ {red,orange,yellow,green,blue,purple}
+(1 row)
+
+SELECT enum_range('orange'::rainbow, 'green'::rainbow);
+ enum_range
+-----------------------
+ {orange,yellow,green}
+(1 row)
+
+SELECT enum_range(NULL, 'green'::rainbow);
+ enum_range
+---------------------------
+ {red,orange,yellow,green}
+(1 row)
+
+SELECT enum_range('orange'::rainbow, NULL);
+ enum_range
+-----------------------------------
+ {orange,yellow,green,blue,purple}
+(1 row)
+
+SELECT enum_range(NULL::rainbow, NULL);
+ enum_range
+---------------------------------------
+ {red,orange,yellow,green,blue,purple}
+(1 row)
+
+--
+-- User functions, can't test perl/python etc here since may not be compiled.
+--
+CREATE FUNCTION echo_me(anyenum) RETURNS text AS $$
+BEGIN
+RETURN $1::text || 'omg';
+END
+$$ LANGUAGE plpgsql;
+SELECT echo_me('red'::rainbow);
+ echo_me
+---------
+ redomg
+(1 row)
+
+--
+-- Concrete function should override generic one
+--
+CREATE FUNCTION echo_me(rainbow) RETURNS text AS $$
+BEGIN
+RETURN $1::text || 'wtf';
+END
+$$ LANGUAGE plpgsql;
+SELECT echo_me('red'::rainbow);
+ echo_me
+---------
+ redwtf
+(1 row)
+
+--
+-- If we drop the original generic one, we don't have to qualify the type
+-- anymore, since there's only one match
+--
+DROP FUNCTION echo_me(anyenum);
+SELECT echo_me('red');
+ echo_me
+---------
+ redwtf
+(1 row)
+
+DROP FUNCTION echo_me(rainbow);
+--
+-- RI triggers on enum types
+--
+CREATE TABLE enumtest_parent (id rainbow PRIMARY KEY);
+CREATE TABLE enumtest_child (parent rainbow REFERENCES enumtest_parent);
+INSERT INTO enumtest_parent VALUES ('red');
+INSERT INTO enumtest_child VALUES ('red');
+INSERT INTO enumtest_child VALUES ('blue'); -- fail
+ERROR: insert or update on table "enumtest_child" violates foreign key constraint "enumtest_child_parent_fkey"
+DETAIL: Key (parent)=(blue) is not present in table "enumtest_parent".
+DELETE FROM enumtest_parent; -- fail
+ERROR: update or delete on table "enumtest_parent" violates foreign key constraint "enumtest_child_parent_fkey" on table "enumtest_child"
+DETAIL: Key (id)=(red) is still referenced from table "enumtest_child".
+--
+-- cross-type RI should fail
+--
+CREATE TYPE bogus AS ENUM('good', 'bad', 'ugly');
+CREATE TABLE enumtest_bogus_child(parent bogus REFERENCES enumtest_parent);
+ERROR: foreign key constraint "enumtest_bogus_child_parent_fkey" cannot be implemented
+DETAIL: Key columns "parent" and "id" are of incompatible types: bogus and rainbow.
+DROP TYPE bogus;
+-- check renaming a value
+ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson';
+SELECT enumlabel, enumsortorder
+FROM pg_enum
+WHERE enumtypid = 'rainbow'::regtype
+ORDER BY 2;
+ enumlabel | enumsortorder
+-----------+---------------
+ crimson | 1
+ orange | 2
+ yellow | 3
+ green | 4
+ blue | 5
+ purple | 6
+(6 rows)
+
+-- check that renaming a non-existent value fails
+ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson';
+ERROR: "red" is not an existing enum label
+-- check that renaming to an existent value fails
+ALTER TYPE rainbow RENAME VALUE 'blue' TO 'green';
+ERROR: enum label "green" already exists
+--
+-- check transactional behaviour of ALTER TYPE ... ADD VALUE
+--
+CREATE TYPE bogus AS ENUM('good');
+-- check that we can add new values to existing enums in a transaction
+-- but we can't use them
+BEGIN;
+ALTER TYPE bogus ADD VALUE 'new';
+SAVEPOINT x;
+SELECT 'new'::bogus; -- unsafe
+ERROR: unsafe use of new value "new" of enum type bogus
+LINE 1: SELECT 'new'::bogus;
+ ^
+HINT: New enum values must be committed before they can be used.
+ROLLBACK TO x;
+SELECT enum_first(null::bogus); -- safe
+ enum_first
+------------
+ good
+(1 row)
+
+SELECT enum_last(null::bogus); -- unsafe
+ERROR: unsafe use of new value "new" of enum type bogus
+HINT: New enum values must be committed before they can be used.
+ROLLBACK TO x;
+SELECT enum_range(null::bogus); -- unsafe
+ERROR: unsafe use of new value "new" of enum type bogus
+HINT: New enum values must be committed before they can be used.
+ROLLBACK TO x;
+COMMIT;
+SELECT 'new'::bogus; -- now safe
+ bogus
+-------
+ new
+(1 row)
+
+SELECT enumlabel, enumsortorder
+FROM pg_enum
+WHERE enumtypid = 'bogus'::regtype
+ORDER BY 2;
+ enumlabel | enumsortorder
+-----------+---------------
+ good | 1
+ new | 2
+(2 rows)
+
+-- check that we recognize the case where the enum already existed but was
+-- modified in the current txn; this should not be considered safe
+BEGIN;
+ALTER TYPE bogus RENAME TO bogon;
+ALTER TYPE bogon ADD VALUE 'bad';
+SELECT 'bad'::bogon;
+ERROR: unsafe use of new value "bad" of enum type bogon
+LINE 1: SELECT 'bad'::bogon;
+ ^
+HINT: New enum values must be committed before they can be used.
+ROLLBACK;
+-- but a renamed value is safe to use later in same transaction
+BEGIN;
+ALTER TYPE bogus RENAME VALUE 'good' to 'bad';
+SELECT 'bad'::bogus;
+ bogus
+-------
+ bad
+(1 row)
+
+ROLLBACK;
+DROP TYPE bogus;
+-- check that values created during CREATE TYPE can be used in any case
+BEGIN;
+CREATE TYPE bogus AS ENUM('good','bad','ugly');
+ALTER TYPE bogus RENAME TO bogon;
+select enum_range(null::bogon);
+ enum_range
+-----------------
+ {good,bad,ugly}
+(1 row)
+
+ROLLBACK;
+-- ideally, we'd allow this usage; but it requires keeping track of whether
+-- the enum type was created in the current transaction, which is expensive
+BEGIN;
+CREATE TYPE bogus AS ENUM('good');
+ALTER TYPE bogus RENAME TO bogon;
+ALTER TYPE bogon ADD VALUE 'bad';
+ALTER TYPE bogon ADD VALUE 'ugly';
+select enum_range(null::bogon); -- fails
+ERROR: unsafe use of new value "bad" of enum type bogon
+HINT: New enum values must be committed before they can be used.
+ROLLBACK;
+--
+-- Cleanup
+--
+DROP TABLE enumtest_child;
+DROP TABLE enumtest_parent;
+DROP TABLE enumtest;
+DROP TYPE rainbow;
+--
+-- Verify properly cleaned up
+--
+SELECT COUNT(*) FROM pg_type WHERE typname = 'rainbow';
+ count
+-------
+ 0
+(1 row)
+
+SELECT * FROM pg_enum WHERE NOT EXISTS
+ (SELECT 1 FROM pg_type WHERE pg_type.oid = enumtypid);
+ oid | enumtypid | enumsortorder | enumlabel
+-----+-----------+---------------+-----------
+(0 rows)
+