enum already exists

[chirayushukla28]

Describe the bug

Whenever I am adding Enum field in a new model it's giving me this error "sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) type "tickettype" already exists" for resolving this I have to delete DB and create a new one.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

https://docs.sqlalchemy.org/en/14/errors.html#error-f405

SQLAlchemy Version in Use

1.4.46

DBAPI (i.e. the database driver)

2.9.5

Database Vendor and Major Version

2.9.5

Python Version

3.8

Operating system

Linux

To Reproduce

alembic revision --autogenerate -m "New Migration"
alemic upgrade head

Error

# Copy the complete stack trace and error message here, including SQL log output if applicable.

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) type "tickettype" already exists

[SQL: CREATE TYPE tickettype AS ENUM ('PAID', 'FREE', 'GROUP', 'DONATION', 'REQUEST', 'GUESTLIST')]

Additional context

No response

[CaselIT]

Hi,

Moved to alembic. What version are you using?

[zzzeek]

this isn't a bug as we dont support adding elements to PG enums, you would have to do this manually using op.execute("ALTER ENUM myenum ADD newitem")

[CaselIT]

I think they are adding a new column of type enum and alembic is generating the migration to add the enum.
IIRC enum have check_exists that defaults to True, so it should not issue the create if it exist. But I may miss-remember

[zzzeek]

alembic doesnt generate migrations to add enums. The enum gets created only if you run CREATE TABLE via op.create_table(). I dont even see any code that would do this for add_column().

[chirayushukla28]

Hi,

Moved to alembic. What version are you using?

alembic==1.8.1

[chirayushukla28]

this isn't a bug as we dont support adding elements to PG enums, you would have to do this manually using op.execute("ALTER ENUM myenum ADD newitem")

Where i have to add this?

[mazen-isstech]

this isn't a bug as we dont support adding elements to PG enums, you would have to do this manually using op.execute("ALTER ENUM myenum ADD newitem")

Where i have to add this?

In the revision file that is generated by Alembic, where you have upgrade() and downgrade() defined. You write it inside upgrade(). op should be defined in the file already.

[m-alhajji]

Here's how you can get around it:
1- Create an enum and two sqlalchemy models.
2- Set a column in both as an enum.
3- Generate alembic migration file for both table.
4- Run alembic upgrade.

OR you can manually change the name the enum.

Here's how to generate this error, replace those steps
3- generate migration for only one table. Then, generate another migration for the other table

[sanchitram1]

So autogenerate doesn't generate the creation of enum. You manually add it in, here is my example

project_status_enum = sa.Enum(
    "active",
    "deprecated",
    "duplicate",
    "spam",
    "changed_url",
    "no_url",
    name="enum_project_status",
    schema="chai_processed",
)

def upgrade() -> None:
    project_status_enum.create(op.get_bind(), checkfirst=True)

   # other stuff

I kept running into the DuplicateViolation error.

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) type "project_status_enum" already exists

[SQL: CREATE TYPE chai_processed.project_status_enum AS ENUM ('active', 'deprecated', 'duplicate', 'spam', 'changed_url', 'no_url')]
(Background on this error at: https://sqlalche.me/e/20/f405)

I switched the logging level to INFO to see the actual SQLs that alembic was running, and saw it was creating the enum type twice:

INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_type.typname 
FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_type.typnamespace 
WHERE pg_catalog.pg_type.typname = %(typname_1)s AND pg_catalog.pg_namespace.nspname = %(nspname_1)s
INFO  [sqlalchemy.engine.Engine] [generated in 0.00006s] {'typname_1': 'enum_project_status', 'nspname_1': 'chai_processed'}

INFO  [sqlalchemy.engine.Engine] CREATE TYPE chai_processed.enum_project_status AS ENUM ('active', 'deprecated', 'duplicate', 'spam', 'changed_url', 'no_url')
INFO  [sqlalchemy.engine.Engine] [no key 0.00006s] {}

INFO  [sqlalchemy.engine.Engine] CREATE TYPE chai_processed.enum_project_status AS ENUM ('active', 'deprecated', 'duplicate', 'spam', 'changed_url', 'no_url')
INFO  [sqlalchemy.engine.Engine] [no key 0.00007s] {}
INFO  [sqlalchemy.engine.Engine] ROLLBACK

That first query returned nothing (since no enum existed). The second query created the enum. The third query...why was it doing it again?

My guess is the first project_status_enum.create(op.get_bind(), checkfirst=True) is redundant in the upgrade function, but I wanted to confirm that. My workaround to remove that line did work, and I am not running into the Duplicate Error any more.

So, can anyone confirm that creating the Enum field outside of the actual upgrade step genuinely creates the enum?

[CaselIT]

Other stuff here is important

[sanchitram1]

    op.create_table(
        "agg_projects_processed",
        sa.Column(
            "tid",
            sa.Integer(),
            sa.Identity(
                always=False,
                start=1,
                increment=1,
                nomaxvalue=True,
                cycle=False,
                minvalue=1,
            ),
            nullable=False,
        ),
        sa.Column("project_name", sa.String(), nullable=False),
        sa.Column(
            "status", project_status_enum, nullable=False, server_default="active"
        ),
        sa.PrimaryKeyConstraint("tid", name=op.f("pk_agg_projects_processed")),
        schema="chai_processed",
    )

creating 4 other tables, none of which use project_status_enum

[sanchitram1]

Also @CaselIT, to your earlier point, did you mean checkfirst or check_exists?

[CaselIT]

you are using the enum right here

sa.Column(
            "status", project_status_enum, nullable=False, server_default="active"
        ),

that's why it gets created. please see also #1347 if you want to use create_type and similar PG only options

[VadimKulyba]

@CaselIT @zzzeek

I have similar issue

I trying to reuse enum (which created in one of previous migrations), but getting error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) type "studyenum" already exists
[SQL: CREATE TYPE studyenum AS ENUM .....

I want to help to fix this issue, it is this same, as described?
Can i help to contiribute?
Can you add some hints where i can start research?

[CaselIT]

Using the postgresql specific type you can indicate in the migration not to create the type:
https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ENUM.params.create_type

[VadimKulyba]

@CaselIT @zzzeek

Yes, i already found this solution, but if this issue still open, i think you planning to automate this functionality, right?

[fievelk]

I encountered this error after creating a new model with an enum column. I could create and execute the migration (upgrade() operation), but the downgrade() operation didn't seem to remove the enum type automatically. To fix it, I just added the statement manually:

def upgrade():
    op.create_table(
        "my_table",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("source", sa.Enum("SOURCE_ENUM", name="source_enum"), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )


def downgrade():
    op.drop_table("my_table")
    op.execute("DROP TYPE source_enum")  # <-- This is what I added manually

I'm using SQLAlchemy 1.4.
Hope this can help someone else.

[eddy-di]

I had the same issue for the first migration when the enum is in the table and it is necessary to upgrade.

when I had this code:

sa.Column('role', postgresql.ENUM('USER', 'STAFF', name='role_enum'), nullable=True),

I was getting:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) type "role_enum" already exists

But I read this explanation:

I understood that when creating the table with already available enum it is better to change create_type=False so that it won't double check.
And I was able to overcome it with this addition:

sa.Column('role', postgresql.ENUM('USER', 'STAFF', name='role_enum', create_type=False), nullable=True),

I hope it will be helpful

[chirayushukla28]

I guess it will work, what if we create new enum fields and choices ? Thanks and Regards, Chirayu Shukla Software Engineer Python | Thoughtwin IT Solutions [image: mobilePhone] 9039901501

…

On Sun, 31 Mar 2024 at 1:53 PM, Eldiiar ***@***.***> wrote: I had the same issue for the first migration when the enum is in the table and it is necessary to upgrade. when I had this code: sa.Column('role', postgresql.ENUM('USER', 'STAFF', name='role_enum'), nullable=True), I was getting: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) type "role_enum" already exists But I read this explanation: Screenshot.from.2024-03-31.14-18-04.png (view on web) <https://github.com/sqlalchemy/alembic/assets/110094562/897a9787-5704-408c-8d07-f000e8f43fed> I understood that when creating the table with already available enum it is better to change create_type=False so that it won't double check. And I was able to overcome it with this addition: sa.Column('role', postgresql.ENUM('USER', 'STAFF', name='role_enum', create_type=False), nullable=True), I hope it will be helpful — Reply to this email directly, view it on GitHub <#1254 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A6GOACNDJHMLX2U2UWCWICLY27BXTAVCNFSM6AAAAAAYSVANLCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAMRYGU4TQNRUHA> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[HassanElwi]

I had same issue. I have pgAdmin you should remove "tickettype" from your database-name -> shemas -> public -> Types section of pgAdmin and the run alembic command to migrate.

[Et7f3]

I know we can specify create_type=False in a postgresql.ENUM to hide the error.
As a user I really like the feature when it is True:

Defaults to True. Indicates that CREATE TYPE should be emitted, after optionally checking for the presence of the type, when the parent table is being created

So it seems the detection feature is at fault since it try to create it.

I also like alembic generate migration file and doesn't need to touch them (here the workaround require to modify generate file, know that a previous migration did add the type, if enum have correct values, ...) so I tried something like:

class WarningEnum(enum.Enum):
  critical = "CRITIQUE"
  warning = "Avertissement"
  info = "Information"


class SomeAddedClass(Base):
    __tablename__ = "some_added_class"

    level: Mapped[WarningEnum] = mapped_column(type_=postgresql.ENUM(WarningEnum, create_type=False))
    id: Mapped[int] = mapped_column(primary_key=True)

In the hope that create_type=False get copied in the migration file. (spoiler it didn't)

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('some_added_class',
    sa.Column('level', postgresql.ENUM('critical', 'warning', 'info', name='warningenum'), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###

Also the solution of deleting types is not suitable because some data depends on it.

[zzzeek]

alembic doesnt detect changes in ENUM right now or really do anything with them at all. the kinds of changes that would be emitted when the contents change are very complicated to guess automatically due to the many individual operations supported or not by PostgreSQL, so instead of making hasty decisions and getting it wrong, we've intentionally stayed away from alteration of enums.

for this reason we've held off on getting into ENUM however there is an alembic extension that has taken on this task which you can see in https://pypi.org/project/alembic-postgresql-enum/ . I would take a look at this project and see if these extensions suit your needs.

[Et7f3]

Thanks, I just tested this plugin today and it make alembic works as excepted. I can add column that mention a previously created enum without having to manipulate db nor migration file (I think like OP did). downgrade/upgrade now apply cleany (even with data that use the enum: drop enum cascade).

I guess now I am locked with postgresql for my schema (it is acceptable), and I should be aware that renaming enum should be done with caution (I think I will just add new variant without removing old one).

Detecting enum renaming is a problem that protobuf suggests just to add enum and reserve old one (so we can see which one is considered deleted and which one are really new). This plugin offer a way to specify a renaming so I can use it as last resort (if I really wanted to rename: I can just change the value in python code instead of renaming the field).
https://protobuf.dev/programming-guides/dos-donts/#reserve-deleted-numbers

Just to come back to the issue: in postgresql we can introspect enum but not in other dialect so the default alembic behavior is a bit silly to always emit a create statement ?

[zzzeek]

you can introspect ENUM types in MySQL/MariaDB as well. for all other backends ENUM just uses VARCHAR.