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 · Answer 1 · 2021-06-24T07:45:27.743

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.

score 0 · Answer 2 · answered Sep 26 '17 at 08:06

0

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

answered Sep 26 '17 at 08:06

stamaimer

6,227
5
34
55

What if I want to use this Enum in several tables ? – jul Sep 26 '17 at 08:51
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. – stamaimer Sep 26 '17 at 08:57

Matt-Heun Hong · Answer 3 · 2020-02-05T03:26:53.610

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)

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

3 Answers3

Linked