Allow IF NOT EXISTS when add a new enum label.

If the label is already in the enum the statement becomes a no-op.
This will reduce the pain that comes from our not allowing this
operation inside a transaction block.

Andrew Dunstan, reviewed by Tom Lane and Magnus Hagander.

1 files changed, 20 insertions, 0 deletions

diff --git a/src/test/regress/sql/enum.sql b/src/test/regress/sql/enum.sql
index 7ca62483096..db7bf44b408 100644
--- a/src/test/regress/sql/enum.sql
+++ b/src/test/regress/sql/enum.sql
@@ -54,6 +54,26 @@ ALTER TYPE planets ADD VALUE
 
 ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
 
+-- if not exists tests
+
+--  existing value gives error
+
+-- We can't do this test because the error contains the
+-- offending Oid value, which is unpredictable.
+-- ALTER TYPE planets ADD VALUE 'mercury';
+
+-- unless IF NOT EXISTS is specified
+ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury';
+
+-- should be neptune, not mercury
+SELECT enum_last(NULL::planets);
+
+ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto';
+
+-- should be pluto, i.e. the new value
+SELECT enum_last(NULL::planets);
+
+
 --
 -- Test inserting so many values that we have to renumber
 --