Support IN with arrays

[kocv59]

WHERE (string1, string2) IN (('string, 'string), ('string2', 'string2')) is supported in postgres so this should support WHERE (string1, string2) IN (${sql([ ["string", "string"], ["string1", "string1"] ])}).

How can I make this kind of query until it's supported?

[croconut]

super strange that this doesnt correctly support in. guess i need to switch to pg + pg-format, since i dont see a decent work around. im not even using a complicated one, its literally just an array of dynamically created input strings - it only works for the first string if i have a one sized array o.o -> select * from t1 where id in (${strArray}) and i run the literal select * from t1 where id in ('thing1', 'thing2') which ofc works

[croconut]

i had to switch to =ANY(${strArray}), fortunately i didnt need to switch to pg. im still confused tho

[porsager]

@croconut The original issue is about composite types.

For your case you just need to leave out the surrounding parenthesis - Postgres.js will add them.

await sql`
  select * from t1 where id in ${ sql([1, 2, 3]) }
`

Now remember that you might bump into the postgres limit of 65534 max parameters :P In that case the = any() is actually better.

[croconut]

no they're strings, sql() tries to turn them into identifiers

[porsager]

No not in this case, it will format them for in (...)

[croconut]

i am super new to this library, is there something wrong with this query? i tried with / without the parentheses around the parameter, and it only worked with =ANY(${tableChunk})

im using: "node_modules/postgres": { "version": "3.4.7",

with postgres:18-trixie

relevant snippet:

    let table2d = [['t1', 't2']];
    for (const tables of table2d) {
        // we use a local obj in case the cache would have dropped an item for some reason
        /** @type {{col3: number; col2: string; col1: string; }[]} */
        console.debug('searching for', tables, Array.isArray(tables), typeof tables[0]);
        let records = await sql`
    select 
            col3, 
            col2, 
            col1
        from t1
        where col1 in (${tableChunk})
        group by col1, col2, col3 
    ;
        `;

     ...

[porsager]

Do like this ;)

let table2d = [['t1', 't2']];
    for (const tables of table2d) {
        // we use a local obj in case the cache would have dropped an item for some reason
        /** @type {{col3: number; col2: string; col1: string; }[]} */
        console.debug('searching for', tables, Array.isArray(tables), typeof tables[0]);
        let records = await sql`
    select 
            col3, 
            col2, 
            col1
        from t1
        where col1 in ${ sql(tableChunk) }
        group by col1, col2, col3 
    ;
        `;

I added sql() and removed the parenthesis around the ${}

[croconut]

oooh okay, i only tried with parentheses with the sql() version, thanks :)