asyncpg DatatypeMismatchError when setting a custom postgres schema

Question

I'm using SQLAlchemy with asyncpg and I have several models defined using Pydantic.

I have one pydantic model named PanAcquisition:

class PanAcquisition(Base):
    __tablename__ = "pan_acquisition"

    acquisition_id = Column(Integer, primary_key=True)
    time_started = Column(
        DateTime(timezone=True), server_default=func.now(), nullable=False, index=True
    )
    last_access = Column(
        DateTime(timezone=True), server_default=func.now(), nullable=False, index=True
    )

    acquisition_source = Column(
        ENUM(ApplicationSource), nullable=False, index=True
    )

When running test, I'm using a different schema "test" but I got this error:

sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DatatypeMismatchError'>: column "acquisition_source" is of type test.applicationsource but expression is of type applicationsource
E                   HINT:  You will need to rewrite or cast the expression.

I don't know how to specify the db_schema for the enum.

Answer 1

The solution is to specify the schema in the enum

class PanAcquisition(Base):
    __tablename__ = "pan_acquisition"
    ...

    acquisition_source = Column(
        ENUM(ApplicationSource, schema=db_config.db_schema), nullable=False, index=True
    )