Question 88915 in PostgreSQL Question posted in
The official documentation can be found here.

operator does not exist: integer = bigint[] – Postgresql

When using a custom SQL query with Prisma, I keep getting the following error when trying to pass an array of numbers to the IN parameter. Converting it to a string of comma seperate values also doesn’t work.

The home_tryscorer_ids = [1,2,7]

Here is my query. Does anyone know how to correctly pass in the home_tryscorer_ds as 1,2,7.

const numberOfTryscorers: number = home_tryscorer_ids.length;

const games: GameType[] = await this.db
  .$queryRaw`
    SELECT g.*, COUNT(DISTINCT p."player_id")
    FROM "public"."NrlPlayerToGame" p
    JOIN "public"."NrlGame" g ON p."game_id" = g."id"
    WHERE p."player_id" IN (${home_tryscorer_ids}) AND "tries" >= 1
    GROUP BY g."id", g."date", g."home_team_id", g."away_team_id"
    HAVING COUNT(DISTINCT p."player_id") = ${numberOfTryscorers}
  `;
Tags:

2

Answers


  1. 0

    The IN operator in SQL expects a comma-separated list of values, but home_tryscorer_ids is an array of numbers (esp. no commas!). A way to get around this is to convert the array to a string of comma-separated values using the join() method before passing it to the query.

    This should work:

    const home_tryscorer_ids = [1, 2, 7];
const numberOfTryscorers = home_tryscorer_ids.length;
const home_tryscorer_ids_string = home_tryscorer_ids.join(',');

const games: GameType[] = await this.db.$queryRaw`
  SELECT g.*, COUNT(DISTINCT p."player_id")
  FROM "public"."NrlPlayerToGame" p
  JOIN "public"."NrlGame" g ON p."game_id" = g."id"
  WHERE p."player_id" IN (${home_tryscorer_ids_string})
    AND "tries" >= 1
  GROUP BY g."id", g."date", g."home_team_id", g."away_team_id"
  HAVING COUNT(DISTINCT p."player_id") = ${numberOfTryscorers}
`;

    Note that this assumes that the values in home_tryscorer_ids are safe to use directly in the SQL query. If the values come from user input, it’s important to sanitize that to prevent SQL injection attacks.

    Login or Signup to reply.
  2. 0

    If home_tryscorer_ids is an array: IN () operator doesn’t support them but =ANY() does: demo

    const numberOfTryscorers: number = home_tryscorer_ids.length;

const games: GameType[] = await this.db
  .$queryRaw`
    SELECT g.*, COUNT(DISTINCT p."player_id")
    FROM "public"."NrlPlayerToGame" p
    JOIN "public"."NrlGame" g ON p."game_id" = g."id"
    WHERE p."player_id"=ANY(${home_tryscorer_ids}) AND "tries" >= 1
    GROUP BY g."id", g."date", g."home_team_id", g."away_team_id"
    HAVING COUNT(DISTINCT p."player_id") = ${numberOfTryscorers}
  `;

    Same can be done with array_position() that returns a NULL if the element is not in the array:

    WHERE ARRAY_POSITION(${home_tryscorer_ids}, p."player_id") IS NOT NULL
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search