alembic: enum already exists
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
About this issue
- Original URL
- State: open
- Created 3 years ago
- Reactions: 7
- Comments: 25 (9 by maintainers)
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:
I was getting:
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:
I hope it will be helpful
you are using the enum right here
that’s why it gets created. please see also https://github.com/sqlalchemy/alembic/issues/1347 if you want to use
create_typeand similar PG only optionsSo autogenerate doesn’t generate the creation of enum. You manually add it in, here is my example
I kept running into the DuplicateViolation error.
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:
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 theupgradefunction, 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
upgradestep genuinely creates the enum?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.
I encountered this error after creating a new model with an enum column. I could create and execute the migration (
upgrade()operation), but thedowngrade()operation didn’t seem to remove the enum type automatically. To fix it, I just added the statement manually:I’m using SQLAlchemy 1.4. Hope this can help someone else.
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().
In the revision file that is generated by Alembic, where you have
upgrade()anddowngrade()defined. You write it insideupgrade().opshould be defined in the file already.