databasepostgresql

ERROR: there is no parameter $1 : why do I get this error in pgAdmin query window?


I am trying to retrieve user data with this query:

SELECT a.type, SUM(coalesce(a.value, 0)) AS value
FROM app.activity a
WHERE a.user_id = $1 and a.status = $2
GROUP BY a.type
ORDER BY a.type

I tested it on pgAdmin 4 query window, but I'm getting this error:

ERROR:  there is no parameter $1
LINE 3:       WHERE a.user_id = $1 and a.status = $2
                                ^ 
SQL state: 42P02
Character: 101

I am trying to get the sum of the user's activity value by grouping.
But I get this error instead:

activity table reference

What's wrong on my side?


Solution

  • To use placeholders, create a prepared statement:

    PREPARE mystmt(integer, text) AS
    SELECT a.type, SUM(coalesce(a.value, 0)) AS value
          FROM app.activity a
          WHERE a.user_id = $1 and a.status = $2
          GROUP BY a.type
          order by a.type
    

    This prepared statement exists until you close the database session (or deallocate the prepared statement), and you can execute it by supplying values for the placeholders with

    EXECUTE mystmt(3, 'new');