Another option is to use a filtered window aggregate COUNT()
to get the number of rows matching the required condition, then use the result in WHERE
to decide whether the rows should be returned, like this:
SELECT
email_text
FROM
(
SELECT
email_text,
COUNT(*) FILTER (WHERE is_activated) OVER () AS activated_count
-- or: (WHERE is_activated IS TRUE)
FROM
your_table
) AS counted
WHERE
activated_count = 0
;
You can also try using a common table expression (CTE) instead of the derived table:
WITH
counted AS
(
SELECT
email_text,
COUNT(*) FILTER (WHERE is_activated) OVER () AS activated_count
FROM
your_table
)
SELECT
email_text
FROM
counted
WHERE
activated_count = 0
;
There is no logical difference between the two queries, but due to the fact that CTEs in PostgreSQL are always materialised during query execution, there may be difference in performance depending on your setup. Therefore it would make sense to try both variations.