I am trying to retrieve user data with below 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 getting below 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 don’t get any, but Error statement.
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');