First, I have this query, which works just fine:
x
DELETE FROM notifications
WHERE data @> '{"postID": 321}'
RETURNING user_id, read
But then, I use it as a sub-query:
SELECT d.user_id, count(d.user_id)
FROM (
DELETE FROM notifications
WHERE data @> '{"postID": 321}'
RETURNING user_id, read
) as d
WHERE d.read = false
GROUP BY d.user_id
And get this error:
ERROR: syntax error at or near "FROM"
LINE 16: DELETE FROM notifications
^
SQL state: 42601
Character: 245
- How do I fix this and achieve the intended result?
- How, in general, can a correct query become an incorrect sub-query?
Advertisement
Answer
Use a CTE:
WITH d as (
DELETE FROM notifications
WHERE data @> '{"postID": 321}'
RETURNING user_id, read
)
SELECT d.user_id, count(d.user_id)
FROM d
WHERE d.read = false
GROUP BY d.user_id