BUG: Interpolation issue while query building

[waimun]

Consider the following code:

const ids = ['ab11cd', 'ef22gh']

await sql`
  select student_id, max(version) as version
  from students
  where student_id in (${sql(ids)})
  group by student_id
`

This would result in the following error in the Postgres database (using 16.3-bookworm currently).

2024-05-30 15:08:50.622 UTC [632] ERROR:  column "ab11cd" does not exist at character 117
2024-05-30 15:08:50.622 UTC [632] STATEMENT:  
	        select student_id, max(version) as version
	        from students
	        where student_id in ("ab11cd","ef22gh")
	        group by student_id

I tried various syntax usages from the guide, section "Building queries" (README) but I was not able to achieve a non-error from the database. As a temporarily workaround, I have to create a formatted string with a single quote (instead of double quote) for each value in the array (ids), and then use that to pass as sql.unsafe().

Note: Postgres interprets double quote as a column or table name.

Please investigate, and advise if there's something wrong with the usage above.

[henryzhang03]

bumping this issue

[ticup]

I had the same issue, but found it. If you leave out the parenthesis it works.

const ids = ['ab11cd', 'ef22gh']

await sql`
  select student_id, max(version) as version
  from students
  where student_id in ${sql(ids)}
  group by student_id`

[waimun]

If parenthesis is used, should the query be flagged as a syntax error or self correct itself?

[luanhenzo]

If parenthesis is used, should the query be flagged as a syntax error or self correct itself?

I'm having this exactly problem right now. I agree with you, @waimun.

[porsager]

https://github.com/porsager/postgres?tab=readme-ov-file#dynamic-values-and-where-in