CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionscommon-table-expression
Andrew Vershinin
Andrew Vershinin

Reputation: 1968

Aggregate column from CTE cannot be used in WHERE clause in the query in PostgreSQL

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

Upvotes: 5

Views: 872

Answers (2)

EzLo
EzLo

Reputation: 14189

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;

Upvotes: 4

ekochergin
ekochergin

Reputation: 4129

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

Upvotes: 3

Related Questions