How to apply HAVING to a alias

Question

I have query that creates a column on the fly like so:

SELECT 
v_list_client.id AS "id",
EXISTS(
  SELECT id FROM message 
  WHERE to_phone_number = v_list_client."phoneNumber" 
    AND  created_by_agent_id != 
  (SELECT id FROM agent WHERE agent_role_id = (
    SELECT id FROM agent_role WHERE label = 'Bot'))
) AS "hasReceivedMessageFromAgent",
*
FROM v_list_client
LEFT JOIN v_form
ON (v_form."client" ->> 'id')::integer = id
WHERE (v_form."formTypeId")::integer = 1
AND (v_form."isReceived")::boolean = true
HAVING "hasReceivedMessageFromAgent" = true

I'd like for the HAVING clause to only return results if the client has received a message from an agent, i.e if the hasReceivedMessageFromAgent column is true.

There is no aggregation happening in your query, so `HAVING` as a clause is inappropriate. Instead just add that condition to your `WHERE` clause. — JNevill, Apr 26 '23 at 18:12

Erwin Brandstetter · Accepted Answer · 2023-04-27T01:16:54.170

HAVING is only applicable after GROUP BY. But you also cannot refer to output columns in the WHERE clause. (That's what made you bark up the wrong tree.) See:

PostgreSQL Where count condition

Drop the column "hasReceivedMessageFromAgent" from the result. By definition, it would always be true and hence noise. You really want to use the expression as filter in another WHERE clause.

I suspect more problems in your query. In particular the subquery on agent looks like it could return multiple rows, which would raise an exception. See:

Postgres Error: More than one row returned by a subquery used as an expression

Consider this rewrite:

SELECT *
FROM   v_list_client l
JOIN   v_form        f ON (f.client ->> 'id')::int = l.id  -- was a fake LEFT JOIN
WHERE  f."formTypeId"::int = 1  -- why the need to cast?
AND    f."isReceived"::boolean  -- why the need to cast?
AND    EXISTS (
   SELECT FROM message m
   LEFT   JOIN (agent a JOIN agent_role ar ON ar.label = 'Bot' AND ar.id = a.agent_role_id) ON a.id = m.created_by_agent_id
   WHERE  m.to_phone_number = l."phoneNumber" 
   AND    a.id IS NULL
   );

About the fake LEFT JOIN, See:

Query with LEFT JOIN not returning rows for count of 0

"formTypeId" should already be integer.
"isReceived" should already be boolean.
The cast for either is expensive nonsense. If the values are always valid, the columns should have the proper data type already. If not, the cast would fail.

Aside: If at all possible, use legal, lower-case, unquoted identifiers in Postgres. See:

Are PostgreSQL column names case-sensitive?

How to apply HAVING to a alias

1 Answers1