My query follows this structure:
WITH CTE AS (
SELECT t1.x, COUNT(t1.y) AS count
FROM table1 t1
GROUP BY t1.x
)
SELECT CTE.x, CTE.count AS newCount, t2.count AS oldCount
FROM table2 t2 JOIN CTE ON t2.x = CTE.x
WHERE t2.count != CTE.count;
I get the following error: [42803] ERROR: aggregate functions are not allowed in WHERE
It looks like the CTE.count is the aggregate that triggers this error. Aren't CTEs supposed to be calculated before the main query? How to rewrite the query to avoid this?
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
Looks like it is the "t2.count" that causes the issue.
On dbfiddle I can reproduce the issue ONLY when there is no column named "count" in the table2.
In other words, the error occurs only when table 2 defined like that:
create table table2 (x int, y int);
However if I added the "count" column, the error is gone
create table table2 (x int, y int, count int);
I believe when there is no such column, the postgres handles "count" as an aggregate function and throws the error.
So, my solution would be to check if such column is present and to never use preserved keywords as column names
The t2.count is being interpreted as an aggregate COUNT() function, and your t2 table does not have a column called count.
Make sure that your table does actually have a count column, or make sure to compute it's aggregate count on another CTE before joining, and then comparing the the results. Also avoid using the alias "count", like the following:
WITH CTE AS (
SELECT t1.x, COUNT(t1.y) AS total
FROM table1 t1
GROUP BY t1.x
),
CTE2 AS (
SELECT t2.x, COUNT(t2.y) AS total
FROM table2 t2
GROUP BY t2.x
)
SELECT
CTE1.x,
CTE1.total AS newCount,
CTE2.total AS oldCount
FROM
CTE2
JOIN CTE1 ON CTE2.x = CTE1.x
WHERE
CTE2.total != CTE1.total;