Error when adding column enum[]

[adriantre]

I am trying to add a column of type array of an existing enum. The table exists, and have rows. The column does not exist.

I have added the server_default like this in sqlalchemy:

server_default=sa.text(
    "ARRAY['my_enum_val1', 'my_enum_val2']::my_enum[]"
)

when trying to migrate I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) default for column "my_column" cannot be cast automatically to type my_enum[]
[SQL:
  ALTER TABLE public."my_table" 
  ALTER COLUMN my_column TYPE public.my_enum[]
  USING my_column::text[]::public.my_enum[];
]

I printed the raw sql that alembic generates from the migration script and ran all commands up until the (above) failing command.

alembic upgrade <down_revision>:<revision> --sql

Then I ran

SELECT my_column::text[]::public.my_enum[];

and it yielded the default values without problems. But manually running the breaking command (above) failed.

Any ideas?

Here is the generated alembic migration script

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        "my_table",
        sa.Column(
            "my_column",
            sa.ARRAY(
                postgresql.ENUM(
                    'my_enum_val1', 
                    'my_enum_val2',
                    ... # etc.
                    name="my_enum",
                    create_type=False,
                )
            ),
            server_default=sa.text(
                "ARRAY['my_enum_val1', 'my_enum_val2']::my_enum[]"
            ),
            nullable=True,
        ),
    )
    op.create_index(
        "ix_my_table_my_column",
        "my_table",
        ["my_column"],
        unique=False,
        postgresql_using="gin",
    )
    op.sync_enum_values(
        "public",
        "my_enum",
        [
            'my_enum_val1', 
            'my_enum_val2',
            ... # etc.
        ],
        [
            TableReference(
                table_schema="public",
                table_name="my_table",
                column_name="my_column",
                column_type=ColumnType.ARRAY,
            ),
            ... # etc for other columns using this already
        ],
        enum_values_to_rename=[],
    )
    # ### end Alembic commands ###

Versions:
PostgreSQL = 14.10
python = 3.10.12
sqlalchemy = 1.4.51
alembic = 1.13.1
alembic-postgresql-enum = 1.2.0

[RustyGuard]

Plese write back whether this issue still happens on your side