Pogchamp-company/alembic-postgresql-enum

enum already exists when using array of enum

Closed this issue · 2 comments

elyassgh commented

Hello, i really appreciated your initiative to solve this problem that has been around for a while, i integrated your solution it solves it perfectly, however i would like to address to you an issue i faced using this library, its when you have an array of enum, in the migration upgrade i got duplicate enum error;

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

[SQL: CREATE TYPE user_roles AS ENUM ('role_1', 'role_2')]

here is an example of code :

class UserRoles(Enum):
    ROLE_1 = 'role_1'
    ROLE_1 = 'role_2'
from sqlalchemy import ARRAY, Enum

class User(BaseModel):
    __tablename__ = 'users'
    roles = Column(ARRAY(Enum(UserRoles, name='user_roles')))

on the alembic migration file :

def upgrade() -> None:
    sa.Enum('role_1', 'role_2', name='user_roles').create(op.get_bind())
    op.create_table('users',
      sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
      sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
      sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
      sa.Column('roles', sa.ARRAY(sa.Enum('role_1', 'role_2', name='user_roles')), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )

it does work fine if i commented the enum first creation :

def upgrade() -> None:
    # sa.Enum('role_1', 'role_2', name='user_roles').create(op.get_bind())
    op.create_table('users',
      sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
      sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
      sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
      sa.Column('roles', sa.ARRAY(sa.Enum('role_1', 'role_2', name='user_roles')), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )

that is my observation on this issue.

NB. I tested also the enum update it works fine 👍

Cordially

RustyGuard commented

It seems really strange. sa.Enum in create_table should have been replaced with postgres ENUM with create_type=false. I'll investigate this behavior

elyassgh commented

Thanks for you responsiveness, it does change it to postgresql.ENUM for other enum that are single value but not for the ones wrapped in ARRAY

  • 👀1