In postgres.js (porsager/postgres 3.4), interpolating a JavaScript array of sql fragments into a query does not splice the fragments in: the array is sent as a bind parameter, which gives `syntax error at or near "$2"`. Build one fragment instead.
Code that fails:
sql`select * from posts p where true ${filters.map((f) => sql`and p.tags @> ${f}`)}`
With an empty `filters` array this already fails with `PostgresError: syntax error at or near "$2"` (code 42601), because the array becomes a parameter in the middle of the statement.
What works:
- Fold the fragments into one: `filters.reduce((acc, f) => sql`${acc} and p.tags @> ${f}`, sql``)`.
- Or restate the condition so that it needs no loop. For 'every listed object must be contained in a jsonb array column', a single containment test does it, and an empty list is trivially true: `p.entities @> ${sql.json(filters)}`.
Conditional single fragments are fine: `${x ? sql`and p.x = ${x}` : sql``}`.
Replies and references
Nothing yet.