Hi Jim,
On Mon, 25 Aug 2025 10:08:23 +0200
Jim Jones <[email protected]> wrote:
> Hi Yugo
>
> On 03.07.25 17:04, Yugo Nagata wrote:
> > Currently, when creating an enum type, duplicate labels are caught by a
> > unique
> > index on pg_enum, resulting in a generic error message.
> >
> > postgres=# create type t as enum ('a','b','a');
> > ERROR: duplicate key value violates unique constraint
> > "pg_enum_typid_label_index"
> > DETAIL: Key (enumtypid, enumlabel)=(16418, a) already exists.
> >
> > I propose adding an explicit check for duplicate labels during enum
> > creation,
> > so that a more user-friendly and descriptive error message can be produced,
> > similar to what is already done in ALTER TYPE ... ADD VALUE
> > or ALTER TYPE ... RENAME VALUE .. TO ....
> >
> > With the attached patch applied, the error message becomes:
> >
> > ERROR: label "a" used more than once
> >
>
> The error message for already existing enum labels starts with "enum",
> e.g. ERROR: enum label "bar" already exists. So, perhaps this new
> error message should follow the same pattern?
Thank you for taking a look. That makes sense, so I updated the message to:
ERROR: enum label "a" used more than once
> I also wonder if we need to add tests for it, so that we make sure the
> new error is triggered prior to the generic one, e.g. in create_type.sql
>
> -- check for duplicate enum entries
> CREATE TYPE den AS ENUM ('foo','bar','foo');
> CREATE TYPE en AS ENUM ('foo','bar');
> ALTER TYPE en ADD VALUE 'foo';
> ALTER TYPE en RENAME VALUE 'foo' TO 'bar';
> DROP TYPE en;
I also added a test for duplicate enum entries to enum.sql,
since tests for existing entries are already there.
Regards,
Yugo Nagata
--
Yugo Nagata <[email protected]>
>From 9b7392f7e26af0c6464731c42adc5b8b91a011f7 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Thu, 3 Jul 2025 23:45:40 +0900
Subject: [PATCH v2] Improve error message for duplicate labels in enum types
Previously, duplicate labels in an enum type were caught by a unique
index on pg_enum, resulting in a generic error message. This adds an
explicit check beforehand to produce a more user-friendly and descriptive
error message.
---
src/backend/catalog/pg_enum.c | 19 +++++++++++++++++++
src/test/regress/expected/enum.out | 3 +++
src/test/regress/sql/enum.sql | 3 +++
3 files changed, 25 insertions(+)
diff --git a/src/backend/catalog/pg_enum.c b/src/backend/catalog/pg_enum.c
index a1634e58eec..daa11d34976 100644
--- a/src/backend/catalog/pg_enum.c
+++ b/src/backend/catalog/pg_enum.c
@@ -164,6 +164,25 @@ EnumValuesCreate(Oid enumTypeOid, List *vals)
{
char *lab = strVal(lfirst(lc));
Name enumlabel = palloc0(NAMEDATALEN);
+ ListCell *lc2;
+
+ /*
+ * Check for duplicate labels. The unique index on pg_enum would catch
+ * that anyway, but we prefer a friendlier error message.
+ */
+ foreach(lc2, vals)
+ {
+ char *lab2 = strVal(lfirst(lc2));
+
+ if (lc == lc2)
+ break;
+
+ if (strcmp(lab, lab2) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("enum label \"%s\" used more than once",
+ lab)));
+ }
/*
* labels are stored in a name field, for easier syscache lookup, so
diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.out
index 4d9f36d0d36..09201489080 100644
--- a/src/test/regress/expected/enum.out
+++ b/src/test/regress/expected/enum.out
@@ -52,6 +52,9 @@ hint |
sql_error_code | 22P02
\x
+-- check for duplicate enum entries
+CREATE TYPE den AS ENUM ('foo','bar','foo');
+ERROR: enum label "foo" used more than once
--
-- adding new values
--
diff --git a/src/test/regress/sql/enum.sql b/src/test/regress/sql/enum.sql
index ecc4878a678..8f744c71bea 100644
--- a/src/test/regress/sql/enum.sql
+++ b/src/test/regress/sql/enum.sql
@@ -23,6 +23,9 @@ SELECT * FROM pg_input_error_info('mauve', 'rainbow');
SELECT * FROM pg_input_error_info(repeat('too_long', 32), 'rainbow');
\x
+-- check for duplicate enum entries
+CREATE TYPE den AS ENUM ('foo','bar','foo');
+
--
-- adding new values
--
--
2.43.0
