ORDER BY on a pg.enum column emits array_position() with an uncast column argument (Postgres 42883) #30163

Summary

ORDER BY on a column typed pg.enum(...) in the contract renders as

array_position(ARRAY['A', 'B']::text[], "status")

with no cast on the column argument. Postgres rejects this with 42883:

function array_position(text[], "Status") does not exist

The result is that ordering by any enum-restricted column fails at runtime. There is no
spelling of .orderBy that avoids it — both the qualified column-ref and the unqualified
identifier-ref paths are intercepted.

Versions

npm view @prisma/orm-postgres dist-tags reports latest: 8.0.0-rc.8, so this is the current
published version, not a stale pin.

Reproduction

Contract:

namespace public {
  valueSet Status {
    OPEN
    CLOSED
  }

  model Ticket {
    id     String        @id
    status pg.enum(Status)
  }
}

Query:

await db.orm.Ticket.orderBy((t) => t.status).all()

Result:

ERROR:  42883: function array_position(text[], "Status") does not exist

db.sql.Ticket.orderBy((t) => t.status) fails identically — it is the SQL renderer, not the
ORM surface, so dropping to the SQL builder is not a workaround.

Every other operation on the same column is fine: eq, neq, in, gt, IS NULL, INSERT,
UPDATE, limit, offset and aggregate all render correctly. Only ORDER BY is affected.

Where it comes from

@prisma/orm-target-postgres/dist/sql-renderer-*.mjs, in renderOrderByExpr:

if (expr.kind === "column-ref") {
  const orderValues = resolveEnumOrderValues(expr, sourcesByRef, contract);
  if (orderValues !== undefined && allStrings(orderValues))
    return `array_position(ARRAY[${...}]::text[], ${renderColumn(expr)})`;
}
if (expr.kind === "identifier-ref") {
  const orderValues = resolveEnumOrderValuesForIdentifier(expr.name, sourcesByRef, contract);
  if (orderValues !== undefined && allStrings(orderValues))
    return `array_position(ARRAY[${...}]::text[], ${quoteIdentifier(expr.name)})`;
}

The array literal is cast to text[], but the column argument is passed through
renderColumn(expr) / quoteIdentifier(expr.name) uncast. Since the physical column is a
native Postgres enum type, no array_position(text[], <enum>) overload exists.

Suggested fix

Cast the column argument to text as well:

`array_position(ARRAY[...]::text[], ${renderColumn(expr)}::text)`

This preserves the intended declaration-order semantics (that is what the array_position
approach is for) while giving Postgres a resolvable overload.

Note on declaration order

Worth flagging in case it affects how you'd rather fix this: for a native Postgres enum
column, a plain ORDER BY "col" already sorts by enum declaration order, because that is how
Postgres orders enum types natively. So for the native-enum case specifically, the
array_position rewrite may not be needed at all — the plain ordering has the same semantics.
It is presumably there for value-sets backed by text columns with a CHECK constraint, where
Postgres would otherwise sort alphabetically. If so, gating the rewrite on the physical column
type rather than on the presence of valueSet would both fix the error and emit simpler SQL for
native enums.

Workaround for anyone hitting this

Retyping the column from pg.enum(Status) to String in the contract and re-running
prisma contract emit switches the rewrite off for that column. The renderer gates on
column.valueSet in the emitted contract and never inspects the database, so the physical
Postgres enum type, its indexes and its data are all untouched — and the resulting plain
ORDER BY sorts in declaration order as described above. The cost is losing the generated
TypeScript union for that column.