Postgresql: Can the minus operator not be used with a parameter? Only hardcoded values?

The following query deletes an entry using index:

 const deleteGameQuery = ` 
                update users 
                set games = games - 1
                where username = $1
            `

If I pass the index as a parameter, nothing is deleted:

const gameIndex = rowsCopy[0].games.findIndex(obj => obj.game == gameID).toString();


            const deleteGameQuery = ` 
                update users 
                set games = games - $1
                where username = $2
            `
    
          const { rows } = await query(deleteGameQuery, [gameIndex, username]);

          ctx.body = rows;

The gameIndex parameter is just a string, the same as if I typed it. So why doesn't it seem to read the value? Is this not allowed?

The column games is a jsonb data type with the following data:

[
    {
        "game": "cyberpunk-2077",
        "status": "Backlog",
        "platform": "Any"
    },
    {
        "game": "new-pokemon-snap",
        "status": "Backlog",
        "platform": "Any"
    }
]

Solution

The problem is you're passing text instead of an integer. You need to pass an integer. I'm not sure exactly how your database interface works to pass integers, try removing toString() and ensure gameIndex is a Number.

const gameIndex = rowsCopy[0].games.findIndex(obj => obj.game == gameID).

array - integer and array - text mean two different things.

array - 1 removes the second element from the array.

select '[1,2,3]'::jsonb - 1;

 [1, 3]

array - '1' searches for the entry '1' and removes it.

select '["1","2","3"]'::jsonb - '1';

 ["2", "3"]

-- Here, nothing is removed because 1 != '1'.
select '[1,2,3]'::jsonb - '1';

 [1, 2, 3]

When you pass in a parameter, it is translated by query according to its type. If you pass a Number it will be translated as 1. If you pass a String it will be translated as '1'. (Or at least that's how it should work, I'm not totally familiar with Javascript database libraries.)

As a side note, this sort of data is better handled as a join table.

create table games (
  id bigserial primary key,
  name text not null,
  status text not null,
  platform text not null
);

create table users (
  id bigserial primary key,
  username text not null
);

create table game_users (
  game_id bigint not null references games,
  user_id bigint not null references users,

  -- If a user can only have each game once.
  unique(game_id, user_id)
);

-- User 1 has games 1 and 2. User 2 has game 2.
insert into game_users (game_id, user_id) values (1, 1), (2, 1), (2,2);

-- User 1 no longer has game 1.
delete from game_users where game_id = 1 and user_id = 1;

You would also have a platforms table and a game_platforms join table.

Join tables are a little mind bending, but they're how SQL stores relationships. JSONB is very useful, but it is not a substitute for relationships.