When using extension types in PostgreSQL, i cant use value inserting

[divinitas-art]

await prisma.$queryRawUnsafe(
      `
      SELECT
        sv."songId",
        AVG(pdb.score(sv.id))::numeric as "avgScore"
      FROM song_variants sv
      WHERE
        (sv."searchTitle" &&& $1::pdb.boost(5)
         OR sv."lyrics" &&& $1::pdb.boost(5)
         OR sv."searchTitle" &&& $1::pdb.fuzzy(1)::pdb.boost(3)
         OR sv."lyrics" &&& $1::pdb.fuzzy(1)::pdb.boost(3)
         OR sv."searchTitle" &&& $1::pdb.fuzzy(2)
         OR sv."lyrics" &&& $1::pdb.fuzzy(2))
        AND sv."deletedAt" IS NULL
      GROUP BY sv."songId"
      ORDER BY AVG(pdb.score(sv.id)) DESC
      LIMIT 10
    `,
      query
    )

I am using ParadeDB (which is basically an extension for PostgreSQL) it uses type casting to do stuff... When i use it with raw queries, I get this error when I try to use the values inserted using $1 and passing the value as parameter for that function... Raw query failed. Code: XX000 Message: ERROR: The right-hand side of the &&&(field, TEXT) operator must be a text value

I found no solution for this problem other than writing my own escaping function and hope for the best...

Is there any way of using it with prisma without doing that? I think prisma tries to be smart in some way and recognizing the types, but it completely bodges it for this.

[asmit990]

Why this fails

Prisma parameter binding uses prepared statements, so $1 gets bound as a regular PostgreSQL parameter (typically text). But ParadeDB’s operators like:

&&&
pdb.boost()
pdb.fuzzy()

do not accept parameter placeholders when the right-hand side requires a literal typed expression (e.g. pdb.boost('foo'), not $1::pdb.boost).

Postgres won’t treat $1 as a typed literal — it treats it as a plain parameter → ParadeDB rejects it:

ERROR: The right-hand side of the &&&(field, TEXT) operator must be a text value

So it’s not that Prisma is doing something wrong — ParadeDB’s operator is not compatible with PostgreSQL parameter binding.

This is why $queryRawUnsafe still fails: the placeholder is still prepared → type casting is still handled as parameter → operator still rejects it.

What actually works

ParadeDB requires a literal text at query compile time, not a prepared parameter. Meaning the RHS must look like:

'something'::pdb.boost(5)

not:

$1::pdb.boost(5)

So you can’t pass $1 into the pdb.boost() or pdb.fuzzy() operator.

The only correct workaround

Insert the literal inside the SQL string, not as a bound parameter.

Example that works:

await prisma.$queryRawUnsafe(SELECT sv."songId", AVG(pdb.score(sv.id))::numeric AS "avgScore" FROM song_variants sv WHERE ( sv."searchTitle" &&& '${query}'::pdb.boost(5) OR sv."lyrics" &&& '${query}'::pdb.boost(5) OR sv."searchTitle" &&& '${query}'::pdb.fuzzy(1)::pdb.boost(3) OR sv."lyrics" &&& '${query}'::pdb.fuzzy(1)::pdb.boost(3) OR sv."searchTitle" &&& '${query}'::pdb.fuzzy(2) OR sv."lyrics" &&& '${query}'::pdb.fuzzy(2) ) AND sv."deletedAt" IS NULL GROUP BY sv."songId" ORDER BY AVG(pdb.score(sv.id)) DESC LIMIT 10);

But you must escape the string yourself to avoid SQL injection.

This is not Prisma’s fault — ParadeDB requires the right-hand literal in query text → parameter binding cannot satisfy that.

Correct technical conclusion for OSS discussion

If you want a precise comment to put on the GitHub issue:

ParadeDB’s operators don't support PostgreSQL prepared-statement parameters on the RHS because they require typed literal expressions. Prisma always binds $1 as a prepared parameter, so $1::pdb.boost() is not accepted by ParadeDB. This is a ParadeDB limitation rather than a Prisma type inference problem. The only working pattern is embedding the literal into the SQL text (with manual escaping), which is why $queryRawUnsafe works only if the parameter is injected manually.

Optional suggestion:

If ParadeDB eventually supports binding parameters inside typed operators (i.e. accepting $1::pdb.boost), Prisma will work automatically without changes.

[jacek-prisma]

Hi @divinitas-art Closing this, since the problem is not in Prisma as explained above