Postgres fails when creating enum

Question

I have a following enum that I want to create:

CREATE TYPE "test" AS ENUM('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '25', '11', '12', '14', '16', '20', '18', '17', '22', '13', '23', '24', '28', '27', '26', '29', '32', '19', '31', '30', '33', '35', '34', '1', '38', '36', '37', '40', '39', '41', '43', '42', '1', '45', '46', '15', '44', '10', '1', '47', '48', '49', '21', '1', '50', '51', '253', '254', '255');

When I try to execute that code, I get:

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "pg_enum_typid_label_index"
  Detail: Key (enumtypid, enumlabel)=(33404, 1) already exists.

I honestly don't understand what the issue is. The number of items should be well below any limit for enums, but if I reduce it to certain number of items, it actually works. What is the problem here?

Version of Postgres used is 9.6.20.

You have the value '1' in your list twice. There may be other duplicates, as well, but I confirmed '1' after both '0' and '42'. — Mike Organek
– Mike Organek, Commented Jan 19, 2021 at 16:49
@a_horse_with_no_name - there are only some values in there. These are country codes for digital tachographs as specified by EU legislation, not really a bunch of random numbers. — Davor
– Davor, Commented Jan 19, 2021 at 16:52
@MikeOrganek - whelp, I was given poor data, it seems. Post that as an answer please so that I can give you credit. Still, what an unhelpful error message... — Davor
– Davor, Commented Jan 19, 2021 at 16:53
@Davor: the error message seems fine (though not perfect) too me. It does point out there's a duplicate value somewhere, with the corresponding label (though the value 1 does not really stand out as much as SOME_ACTUAL_ENUM_VALUE would). — Marth
– Marth, Commented Jan 19, 2021 at 17:12

Thom Brown · Accepted Answer · 2021-01-19 16:54:46Z

6

Your enum definition contains duplicate values.

If you remove all those duplicate '1' values, it will work:

CREATE TYPE "test" AS ENUM('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '25', '11', '12', '14', '16', '20', '18', '17', '22', '13', '23', '24', '28', '27', '26', '29', '32', '19', '31', '30', '33', '35', '34', '38', '36', '37', '40', '39', '41', '43', '42', '45', '46', '15', '44', '10', '47', '48', '49', '21', '50', '51', '253', '254', '255');

answered Jan 19, 2021 at 16:54

Thom Brown

2,0698 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres fails when creating enum

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related