1

So I'm working in Node.js and using the 'pg' npm module. I'm trying to check to see if the contents of an array are contained within an array that's stored in a Postgres table (the order doesn't matter to me--it should return true if there is a 1:1 element ration between arrays).

The Postgres query looks like this:

let getComFromMembers = `SELECT * FROM ComTable WHERE (members @> ($1) AND members <@ ($1))`

In my javascript, I'm calling it like this:

let results = await client.query(getComFromMembers, [numberMembers]);

numberMembers is an array that was originally pulled from Postgres, and then mapped to a number array:

let postgresArray = []
// query tables and populate postgresArray with .push()
let numberArray = postgresArray.map(Number)

For some reason, I'm not getting anything back from 'results'. As an example, in the case where numberArray would be an array with elements 1, 2, and 3, look below.

To get it to work I need to query directly into my database:

SELECT * FROM ComTable WHERE (members @> '{1,2,3}' AND members <@ '{1,2,3}')
nickcoding2
  • 142
  • 1
  • 8
  • 34
  • please see this: https://stackoverflow.com/questions/10720420/node-postgres-how-to-execute-where-col-in-dynamic-value-list-query/10771206#10771206 – GrafiCode Jun 05 '22 at 17:44
  • @GrafiCode I tried using ANY($1::int[]) and also ANY ($1) but neither worked correctly. The first threw 'operator does not exist: integer[] @> integer' and the second threw 'could not find array type for data type integer[]' – nickcoding2 Jun 05 '22 at 18:47
  • 1
    I see, but this particular answer (the accepted one) tells you to generate a `list` where each number is preceeded by the dollar sign `$` (whatever that means XD) https://stackoverflow.com/a/11691651/5334486 «you get the postgres parameterized escaping» – GrafiCode Jun 05 '22 at 19:00
  • there's a comment to that answer (the one with 43 upvotes), I think that's the correct way to do it – GrafiCode Jun 05 '22 at 19:05
  • @GrafiCode I mean I tried ANY ($1), would I have to add another set of brackets around the numberArray I'm passing in and then do ANY ($1) ? This is super weird – nickcoding2 Jun 05 '22 at 19:16
  • I understand the confusion, I honestly don't know much about the way postgres handles arrays coming from javascript. – GrafiCode Jun 05 '22 at 19:18
  • @GrafiCode I'm thinking when you wrap the passed in array in ANY(), it doesn't handle the usage of the contains operations @> and <@. It can only deal with equality. Not sure how to proceed. – nickcoding2 Jun 06 '22 at 11:48
  • Don't know but It work on my PC (`pg` version 8.7.3, postgresql server 14.3) – binhgreat Jun 14 '22 at 11:24

1 Answers1

1

I believe you need to execute the query like so:

let getComFromMembers = `SELECT * FROM ComTable WHERE members @> $1 AND members <@ $1`;
let numberMembers = [101, 212, 333];
let results = await client.query(getComFromMembers, ["{" + numberMembers.join(",") + "}"]);

But the proper solution would be to "generate" and execute the query as follows:

let numberMembers = [101, 212, 333];

let placeHolder = numberMembers.map((_, i) => `$${i+1}`).join(", ");
// the result would be "$1, $2, $3"

let getComFromMembers = `SELECT * FROM ComTable WHERE members @> ARRAY[${placeHolder}]::integer[] AND members <@ ARRAY[${placeHolder}]::integer[]`;
// the result would be "SELECT * FROM ComTable WHERE members @> ARRAY[$1, $2, $3]::integer[] AND members <@ ARRAY[$1, $2, $3]::integer[]"
getComFromMembers;

let results = await client.query(getComFromMembers, numberMembers);
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • The second solution threw this error, where numberMembers was [ 359, 69, 420 ]: 2022-06-14T01:58:13.017485+00:00 app[web.1]: error: operator does not exist: integer[] @> text[] – nickcoding2 Jun 14 '22 at 02:01
  • That should not happen in theory unless your JavaScript array contains numbers as strings. That being said, add explicit cast `members @> ARRAY[${placeHolder}]::integer[] AND members <@ ARRAY[${placeHolder}]::integer[]` – Salman A Jun 14 '22 at 08:00
  • Nevermind. I just looked at [the documentation](https://node-postgres.com/features/queries) and it states that _everything else_ is converted to string so explicit typecast is needed. I'll revise the answer. – Salman A Jun 14 '22 at 08:31