CODEWITHSUNDEEP
postgresql
Davor
Davor

Reputation: 1396

Postgres fails when creating enum

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.

Upvotes: 1

Views: 1532

Answers (1)

Thom Brown
Thom Brown

Reputation: 2029

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');

Upvotes: 6

Related Questions