Drizzle Team•2y ago

PSQL SQL query does not work

Unable to achieve it with Drizzle helpers - I had to write my query in plain SQL. I'm trying to build a query that looks like this;

const existingIdentities: Identity[] = await client.execute(
        sql`
          SELECT * FROM identities
          WHERE
            ip_addresses && ARRAY[${batchIPs}]::varchar[] OR
            anonymous_ids && ARRAY[${batchAnonymousIds}]::varchar[] OR
            email IN (${batchEmails.join(", ")})
        `,
      );

const existingIdentities: Identity[] = await client.execute(
        sql`
          SELECT * FROM identities
          WHERE
            ip_addresses && ARRAY[${batchIPs}]::varchar[] OR
            anonymous_ids && ARRAY[${batchAnonymousIds}]::varchar[] OR
            email IN (${batchEmails.join(", ")})
        `,
      );

But I keep getting the following error;

PostgresError: syntax error at or near "$1"

PostgresError: syntax error at or near "$1"

I do not understand the error and can't manage to find a solution... To give you context, ip_addresses is a column of identities that is a VARCHAR[] anonymous_ids is a column of identities that is a VARCHAR[] email is a column of identities that is a VARCHAR Then, all the interpolated values are arrays of strings. Any idea on how to make it work ??? Much appreciated 🙏

1 Reply

Dan•2y ago

first of all, you can enable query logging to see which query is generated secondly, I suspect the problem is in ${batchEmails.join(", ")} part. You can use the inArray operator from Drizzle instead.

Gaming

Programming

PSQL SQL query does not work