I've been enjoying using this excellent library, but I ran into a problem when trying to build a query with a row comparison like the following:
SELECT *
FROM table
WHERE ("name", "id") > ($1, $2)
ORDER BY "name" ASC , "id" ASC
The documentation in the section "Dynamic values and where in" states:
Value lists can also be created dynamically, making where in queries simple too.
const users = await sql`
select
*
from users
where age in ${ sql([68, 75, 23]) }
`
Based on this, I thought that the following code would construct my query:
const results = await sql`
SELECT *
FROM table
WHERE ${sql(['name', 'id'])} < ${sql(['hello', 'id1'])}
`
However, that actually produced this query (syntax error at or near ","):
SELECT *
FROM table
WHERE "name","id" > "hello","id1"
ORDER BY "name" ASC , "id" ASC
Note that the parentheses are missing, and the string values on the right side of the comparison are passed as quoted column identifiers instead of parameterized values.
Furthermore, if I try to build a query similar to the one in the example, by passing an array of numbers like sql([68, 75, 23]), postgres.js throws an error while trying to construct the query:
"stack": "TypeError: str.replace is not a function
at escape (file:///.../node_modules/postgres/src/types.js:217:20)
at file:///.../node_modules/postgres/src/types.js:213:22
at Array.map (<anonymous>)
at escapeIdentifiers (file:///.../node_modules/postgres/src/types.js:213:13)
at Builder.build (file:///.../node_modules/postgres/src/types.js:70:9)
at stringifyValue (file:///.../node_modules/postgres/src/types.js:109:38)
at stringify (file:///.../node_modules/postgres/src/types.js:100:16)
at fragment (file:///.../node_modules/postgres/src/types.js:119:10)
at stringifyValue (file:///.../node_modules/postgres/src/types.js:110:30)
at stringify (file:///.../node_modules/postgres/src/types.js:100:16)
at new Query (file:///.../node_modules/postgres/src/query.js:35:9)
at Object.sql (file:///.../node_modules/postgres/src/index.js:112:11)
...
It makes sense to me that this wouldn't work, since I never tell postgres.js if I'm passing it column identifiers or string values. The fact that it throws a str.replace is not a function error when given an array of numbers makes it seem like it was never intended to be used to construct value lists in the first place, and is only meant for identifier lists.
My use case is different from the one in the documentation, so I don't know if the WHERE IN query in the documentation works, but the wording "Value lists can also be created dynamically" implies that this would work in any situation where a value list could be used. It also feels like it might be a mistake that the identifier list isn't wrapped in parentheses automatically. It's easy enough to manually construct the value lists I need with a bit more templating code, but the documentation probably should be updated to clarify/correct this statement.
Version: 3.4.4
I've been enjoying using this excellent library, but I ran into a problem when trying to build a query with a row comparison like the following:
The documentation in the section "Dynamic values and where in" states:
Based on this, I thought that the following code would construct my query:
However, that actually produced this query (syntax error at or near ","):
Note that the parentheses are missing, and the string values on the right side of the comparison are passed as quoted column identifiers instead of parameterized values.
Furthermore, if I try to build a query similar to the one in the example, by passing an array of numbers like
sql([68, 75, 23]), postgres.js throws an error while trying to construct the query:It makes sense to me that this wouldn't work, since I never tell postgres.js if I'm passing it column identifiers or string values. The fact that it throws a
str.replace is not a functionerror when given an array of numbers makes it seem like it was never intended to be used to construct value lists in the first place, and is only meant for identifier lists.My use case is different from the one in the documentation, so I don't know if the
WHERE INquery in the documentation works, but the wording "Value lists can also be created dynamically" implies that this would work in any situation where a value list could be used. It also feels like it might be a mistake that the identifier list isn't wrapped in parentheses automatically. It's easy enough to manually construct the value lists I need with a bit more templating code, but the documentation probably should be updated to clarify/correct this statement.