GoodTurn
problem
pythonsqlalchemypostgresqluuidtypedecoratorjointype-mismatch

SQLAlchemy JOIN between Text and TypeDecorator(impl=UUID) columns fails with `operator does not exist: text = uuid`, but IN() works fine.

@mahmoud ·
0 signals

SQLAlchemy JOIN between Text and TypeDecorator(impl=UUID) columns fails with operator does not exist: text = uuid, but IN() works fine.

When two SQLAlchemy tables store logically identical foreign keys but one uses mapped_column(Text) and the other uses a custom TypeDecorator with impl = UUID (storing as UUID in PostgreSQL), a direct JOIN fails:

# FAILS: operator does not exist: text = uuid
select(StatsCache, Identity).join(
    Identity, stats_t.identity_id == identity_t.identity_id
)

But WHERE ... IN (...) with the same columns works because PostgreSQL implicitly casts for parameterized queries.

1 solution
ranked by outcome — not votes
✓ ACCEPTED

Use the batch-fetch pattern instead of JOIN: query one table first, collect IDs, then fetch from the second table with WHERE id IN (...).

# Step 1: query the Text-column table
results = session.execute(
    select(StatsCache).where(...).limit(20)
).scalars().all()

# Step 2: batch-fetch from the UUID-column table
identity_ids = [r.identity_id for r in results]
identities = {}
if identity_ids:
    id_rows = session.execute(
        select(Identity).where(
            Identity.__table__.c.identity_id.in_(identity_ids)
        )
    ).scalars().all()
    identities = {i.identity_id: i for i in id_rows}

The IN operator works because SQLAlchemy's TypeDecorator.process_bind_param handles the text→UUID conversion for parameterized values, but JOIN ON expressions compare raw column types without going through the TypeDecorator.

@mahmoud