psycopg2.ProgrammingError on Enum type in Flask (database) database migration

Question

When running a migration on my Flask app's postgres database I get the following psycopg2 error on an Enum type:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 8753d3c9dbd1, empty message
Traceback (most recent call last):
  File "/home/jul/.miniconda3/envs/audiolabeling/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/jul/.miniconda3/envs/audiolabeling/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: type "feedbacktype" already exists

I tried to delete the alembic_version table and the migrations folder and rerun flask db init/migrate/upgrade, with no success.

I had to drop the database entirely to solve the issue.

Is there any way to not recreate types when they already exist ?

models.py

from audiolabeling import db
import enum


class FeedBackType(enum.Enum):
    NONE = "none"
    HIDDENIMAGE = "hiddenImage"

class Task(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    feedback = db.Column(db.Enum(FeedBackType))

    def __repr__(self):
        return '<id {}>'.format(self.id)

Mustansir Zia · Accepted Answer · 2021-06-24 07:45:27Z

2

Probably pretty late for an answer but will still try to post it for someone else looking for this in the future. (As I was)

There's also a create_type kwarg for postgresql.ENUM. which is used to set the schema for an enum type column in the alembic migration script.

Here's what my new column definition looks like. (Which uses existing an existing enum)


sa.Column('ActionType', postgresql.ENUM('Primary', 'Secondary', name='actiontype', create_type=False), nullable=True),

This will now use the existing enum for the new column without creating a new one.

I'm using SQLAlchemy==1.1.1 and alembic==0.8.8 in my requirements file.

edited Jun 24, 2021 at 7:45

answered Jun 24, 2021 at 7:35

Mustansir Zia

1,00411 silver badges18 bronze badges

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

Comments

stamaimer · Accepted Answer · 2017-09-26 08:06:33Z

0

You can use feedback = db.Column(db.Enum(["NONE", "HIDDENIMAGE"])) to replace your code.

answered Sep 26, 2017 at 8:06

stamaimer

6,5456 gold badges39 silver badges57 bronze badges

2 Comments

jul Over a year ago

What if I want to use this Enum in several tables ?

stamaimer Over a year ago

You can define a list which consist of the values to construct the enum type. And unpack it in where you want to define a enum type field.

Matt-Heun Hong · Accepted Answer · 2020-02-05 03:26:53Z

0

EDIT: The below does NOT work with Alembic. Same issue is here: https://github.com/sqlalchemy/alembic/issues/566

Use sqlalchemy.dialects.postgresql.ENUM instead of db.Enum when creating the column.

ENUM has the optional parameter create_type, which should be set to False.

Like so:

from sqlalchemy.dialects.postgresql import ENUM

...

class Task(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    feedback = db.Column(ENUM(FeedBackType, create_type=False))

def __repr__(self):
    return '<id {}>'.format(self.id)

edited Feb 5, 2020 at 3:26

answered Feb 5, 2020 at 2:30

Matt-Heun Hong

3963 silver badges13 bronze badges

Collectives™ on Stack Overflow

psycopg2.ProgrammingError on Enum type in Flask (database) database migration

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related